Articles tagged recipe

Building a Django driver for Psycopg 3

One of the goals of the Psycopg 3 project is to make easy to port code developed from Psycopg 2. For this reason the creation of a Django backend (the module you specify in the settings as your database ENGINE) was a project with a double goal:

A Django driver is a way to make Psycopg 3 useful from the start, with the possibility of dropping it in a project transparently and have available, when needed the new features offered (for instance the superior COPY support).
The difficulty of introducing Psycopg 3 in the Django codebase and the type of changes required are indicative of the type of problems that could be found porting other projects.

...and it's done! A few days ago, the new Psycopg 3 Django backend could pass the entire Django test suite!

Cancelling PostgreSQL statements from Python

Cancelling a long running query from Python is not something that happens automatically: the libpq doesn't react to Python signals so the only way to stop a query is to run a pg_cancel_backend from another process. Killing the Python process won't cancel the query: it will run until completion and then rolled back. This makes working with long-running query from the Python interpreter somewhat frustrating.

Prepared statements in Psycopg

Although the libpq library supports prepared statements, psycopg2 doesn't offer yet a direct way to access the relevant functions. This will probably change in the future, but in the meantime it is possible to use prepared statements in PostgreSQL using the PREPARE SQL command.

Building Psycopg on Windows using MinGW

My goal was to install Psycopg on Windows using MinGW and the PostgreSQL binary package.

I have used the MinGW GCC binaries packaged by Giovanni Bajo. The package takes care of a lot of details, for instance registering MinGW as default compiler for Python, plus some magic I don't even want to know, and makes the entire process simple enough.

PostgreSQL notifications with Psycopg2 and Eventlet

PostgreSQL supports asynchronous notifications, a simple messaging system allowing clients to be notified about events occurred in the database. Notifications can be sent by any session using a "NOTIFY channel" command and will be received by any session which has subscribed with a LISTEN channel to receive updates. The system has been greatly improved in PostgreSQL 9.0 with the addition of a message payload, making the feature even more useful. Previously a typical use case would have been to notify interested sessions that a certain table was updated: now it is possible to signal for instance which record was changed. You can put the NOTIFY command in a database trigger for automatic notifications on insert or update... the possibilities are actually quite interesting.

Links about building Psycopg on Mac OS X

Looks like building Psycopg on OS X is tricky: the code needs no tweak, but linking against the right library seems problematic.

Passing connections to functions using a decorator

In many script I write, there are functions requiring database operations. Every time I need them, I try to write such code in functions like:

@with_connection
def do_some_job(cnn, arg1, arg2=None):
    cur = cnn.cursor()
    cur.execute(SQL, (arg1, arg2)) # or something else

do_some_job(42, arg2='hi')