Python/PostgreSQL Interface Architecture

If you are writing a Python application that interfaces with PostgreSQL you can choose from a number of different APIs, most of which are compatible with the Python DB-API specification. DB-API is a three-layer interface that lets you build Python applications that can, at least in theory, interact with many different databases. The top layer (the layer that your application deals with) is a database-neutral interface that hides all of the nasty details of writing database-dependent code. The middle layer is an interface that converts requests and responses between DB-API form and the form required by the database. The bottom layer is the database itself (in this case, PostgreSQL). The three most commonly found Python/PostgreSQL interfaces are PyGreSQL, PsycoPg (great name don't you think?), and PoPy. All three libraries can be used as the middle layer in a DB-API stack. Each library offers its own set of extensions to the DB-API library, but I won't cover those here—see the documentation for each library for full details.

The standard PostgreSQL distribution included the PyGreSQL library until a few releases ago. Starting with PostgreSQL version 7.4, you'll have to download a Python/PostgreSQL interface from a separate website. You can find PyGreSQL at http://www.pygresql.org, PsycoPg at http://initd.org/projects/psycopg1, and PoPy at http://popy.sourceforge.net.

The alternate implementations (PyscoPg and PoPy) have been designed to maximize performance in some configurations. The interface distributed with PostgreSQL (PyGreSQL) was implemented as a wrapper, so it probably won't be quite as fast as the other two; but with PyGreSQL, you can pick and choose between the two interface layers.

In this chapter, I'll describe the DB-API interface between Python and PostgreSQL, but not the PyGreSQL extensions. Applications written to the DB-API specification can connect to different databases; applications written using PyGreSQL cannot. See the PyGreSQL Web site (http://www.pygresql.org) for more information about the extensions offered by PyGreSQL.