Connection and cursor factories

psycopg 2 exposes two new-style classes that can be sub-classed and expanded to adapt them to the needs of the programmer: cursor and connection. The connection class is usually sub-classed only to provide a . cursor is much more interesting, because it is the class where query building, execution and result type-casting into Python variables happens.

Setting transaction isolation levels

psycopg2 connection objects hold informations about the PostgreSQL transaction isolation level. The current transaction level can be read from the .isolation_level attribute. The default isolation level is READ COMMITTED. A different isolation level con be set through the .set_isolation_level() method. The level can be set to one of the following constants, defined in psycopg2.extensions:

ISOLATION_LEVEL_AUTOCOMMIT

No transaction is started when command are issued and no .commit()/.rollback() is required. Some PostgreSQL command such as CREATE DATABASE can't run into a transaction: to run such command use .set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT).

`ISOLATION_LEVEL_READ_COMMITTED`

System Message: ERROR/3 (../doc/extensions.rst, line 54); backlink

Can't find 'ISOLATION_LEVEL_READ_COMMITTED' in any provided module.

This is the default value. A new transaction is started at the first .execute() command on a cursor and at each new .execute() after a .commit() or a .rollback(). The transaction runs in the PostgreSQL READ COMMITTED isolation level.

ISOLATION_LEVEL_SERIALIZABLE

Transactions are run at a SERIALIZABLE isolation level.

Adaptation of Python values to SQL types

psycopg2 casts Python variables to SQL literals by type. Standard Python types are already adapted to the proper SQL literal.

Example: the Python function:

curs.execute("""INSERT INTO atable (anint, adate, astring)
                 VALUES (%s, %s, %s)""",
             (10, datetime.date(2005, 11, 18), "O'Reilly"))

is converted into the SQL command:

INSERT INTO atable (anint, adate, astring)
 VALUES (10, '2005-11-18', 'O''Reilly');

Named arguments are supported too with %(name)s placeholders. Notice that:

• The Python string operator % is not used: the .execute() function accepts the values tuple or dictionary as second parameter.
• The variables placeholder must always be a %s, even if a different placeholder (such as a %d for an integer) may look more appropriate.
• For positional variables binding, the second argument must always be a tuple, even if it contains a single variable.
• Only variable values should be bound via this method: it shouldn't be used to set table or field names. For these elements, ordinary string formatting should be used before running .execute().

from psycopg2.extensions import adapt, register_adapter, AsIs

class Point(object):
    def __init__(self, x=0.0, y=0.0):
        self.x = x
        self.y = y

def adapt_point(point):
    return AsIs("'(%s,%s)'" % (adapt(point.x), adapt(point.y)))
    
register_adapter(Point, adapt_point)

curs.execute("INSERT INTO atable (apoint) VALUES (%s)", 
             (Point(1.23, 4.56),))

INSERT INTO atable (apoint) VALUES ((1.23, 4.56));

Type casting of SQL types into Python values

PostgreSQL objects read from the database can be adapted to Python objects through an user-defined adapting function. An adapter function takes two argments: the object string representation as returned by PostgreSQL and the cursor currently being read, and should return a new Python object. For example, the following function parses a PostgreSQL point into the previously defined Point class:

def cast_point(value, curs):
    if value is not None:
            # Convert from (f1, f2) syntax using a regular expression.
        m = re.match("\((.*),(.*)\)", value) 
        if m:
            return Point(float(m.group(1)), float(m.group(2)))

To create a mapping from the PostgreSQL type (either standard or user-defined), its oid must be known. It can be retrieved either by the second column of the cursor description:

curs.execute("SELECT NULL::point")
point_oid = curs.description[0][1]   # usually returns 600

or by querying the system catalogs for the type name and namespace (the namespace for system objects is pg_catalog):

curs.execute("""
    SELECT pg_type.oid
      FROM pg_type JOIN pg_namespace
             ON typnamespace = pg_namespace.oid
     WHERE typname = %(typename)s
       AND nspname = %(namespace)s""",
            {'typename': 'point', 'namespace': 'pg_catalog'})
    
point_oid = curs.fetchone()[0]

After you know the object oid, you must can and register the new type:

POINT = psycopg2.extensions.new_type((point_oid,), "POINT", cast_point)
psycopg2.extensions.register_type(POINT)

The new_type() function binds the object oids (more than one can be specified) to the adapter function. register_type() completes the spell. Conversion is automatically performed when a column whose type is a registered oid is read:

curs.execute("SELECT '(10.2,20.3)'::point")
point = curs.fetchone()[0]
print type(point), point.x, point.y
# Prints: "<class '__main__.Point'> 10.2 20.3"

Working with times and dates

Receiving NOTIFYs

Using COPY TO and COPY FROM

psycopg2 cursor object provides an interface to the efficient PostgreSQL COPY command to move data from files to tables and back.

The .copy_to(file, table) method writes the content of the table named table to the file-like object file. file must have a write() method.

The .copy_from(file, table) reads data from the file-like object file appending them to the table named table. file must have both read() and readline() method.

Both methods accept two optional arguments: sep (defaulting to a tab) is the columns separator and null (defaulting to \N) represents NULL values in the file.

PostgreSQL status message and executed query

cursor objects have two special fields related to the last executed query:

• .query is the textual representation (str or unicode, depending on what was passed to .execute() as first argument) of the query after argument binding and mogrification has been applied. To put it another way, .query is the exact query that was sent to the PostgreSQL backend.
• .statusmessage is the status message that the backend sent upon query execution. It usually contains the basic type of the query (SELECT, INSERT, UPDATE, ...) and some additional information like the number of rows updated and so on. Refer to the PostgreSQL manual for more information.