Basic module usage - Psycopg 2.9.9 documentation

Passing parameters to SQL queries

Psycopg converts Python variables to SQL values using their types: the Python type determines the function used to convert the object into a string representation suitable for PostgreSQL. Many standard Python types are already adapted to the correct SQL representation .

Passing parameters to an SQL statement happens in functions such as cursor.execute() by using %s placeholders in the SQL statement, and passing a sequence of values as the second argument of the function. For example the Python function call:

>>> cur.execute("""
...     INSERT INTO some_table (an_int, a_date, a_string)
...     VALUES (%s, %s, %s);
...     """,
...     (10, datetime.date(2005, 11, 18), "O'Reilly"))

is converted into a SQL command similar to:

INSERT INTO some_table (an_int, a_date, a_string)
VALUES (10, '2005-11-18', 'O''Reilly');

Named arguments are supported too using %( name )s placeholders in the query and specifying the values into a mapping. Using named arguments allows to specify the values in any order and to repeat the same value in several places in the query:

>>> cur.execute("""
...     INSERT INTO some_table (an_int, a_date, another_date, a_string)
...     VALUES (%(int)s, %(date)s, %(date)s, %(str)s);
...     """,
...     {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})

Using characters % , ( , ) in the argument names is not supported.

When parameters are used, in order to include a literal % in the query you can use the %% string:

>>> cur.execute("SELECT (%s % 2) = 0 AS even", (10,))       # WRONG
>>> cur.execute("SELECT (%s %% 2) = 0 AS even", (10,))      # correct

While the mechanism resembles regular Python strings manipulation, there are a few subtle differences you should care about when passing parameters to a query.

• The Python string operator % must not be used : the execute() method accepts a tuple or dictionary of values as second parameter. Never use % or + to merge values into queries :

  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))  # correct
  

• For positional variables binding, the second argument must always be a sequence , even if it contains a single variable (remember that Python requires a comma to create a single element tuple):

  >>> cur.execute("INSERT INTO foo VALUES (%s)", "bar")    # WRONG
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar"))  # WRONG
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ("bar",)) # correct
  >>> cur.execute("INSERT INTO foo VALUES (%s)", ["bar"])  # correct
  

• The placeholder must not be quoted . Psycopg will add quotes where needed:

  >>> cur.execute("INSERT INTO numbers VALUES ('%s')", (10,)) # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # correct
  

• The variables placeholder must always be a %s , even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate:

  >>> cur.execute("INSERT INTO numbers VALUES (%d)", (10,))   # WRONG
  >>> cur.execute("INSERT INTO numbers VALUES (%s)", (10,))   # correct
  

• Only query values should be bound via this method: it shouldn’t be used to merge table or field names to the query (Psycopg will try quoting the table name as a string value, generating invalid SQL). If you need to generate dynamically SQL queries (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module:

  >>> cur.execute("INSERT INTO %s VALUES (%s)", ('numbers', 10))  # WRONG
  >>> cur.execute(                                                # correct
  ...     SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),
  ...     (10,))
  

>>> SQL = "INSERT INTO authors (name) VALUES ('%s');" # NEVER DO THIS
>>> data = ("O'Reilly", )
>>> cur.execute(SQL % data) # THIS WILL FAIL MISERABLY
ProgrammingError: syntax error at or near "Reilly"
LINE 1: INSERT INTO authors (name) VALUES ('O'Reilly')
                                              ^

>>> SQL = "INSERT INTO authors (name) VALUES (%s);" # Note: no quotes
>>> data = ("O'Reilly", )
>>> cur.execute(SQL, data) # Note: no % operator

>>> path = r'C:\Users\Bobby.Tables'
>>> cur.execute('INSERT INTO mytable(path) VALUES (%s)', (path,))
>>> cur.execute('SELECT * FROM mytable WHERE path LIKE %s', (path,))
>>> cur.fetchall()
[]

>>> cur.execute("SELECT * FROM mytable WHERE path LIKE %s ESCAPE ''", (path,))

Adaptation of Python values to SQL types

Many standard Python types are adapted into SQL and returned as Python objects when a query is executed.

The following table shows the default mapping between Python and PostgreSQL types:

The mapping is fairly customizable: see Adapting new Python types to SQL syntax and Type casting of SQL types into Python objects . You can also find a few other specialized adapters in the psycopg2.extras module.

>>> cur.mogrify("SELECT %s, %s, %s;", (None, True, False))
'SELECT NULL, true, false;'

>>> cur.mogrify("SELECT %s, %s, %s, %s;", (10, 10L, 10.0, Decimal("10.00")))
'SELECT 10, 10, 10.0, 10.00;'

>>> print(u, type(u))
àèìòù€ 

>>> cur.execute("INSERT INTO test (num, data) VALUES (%s,%s);", (74, u))

>>> print(conn.encoding)
UTF8

>>> cur.execute("SELECT data FROM test WHERE num = 74")
>>> x = cur.fetchone()[0]
>>> print(x, type(x), repr(x))
àèìòù€  '\xc3\xa0\xc3\xa8\xc3\xac\xc3\xb2\xc3\xb9\xe2\x82\xac'

>>> conn.set_client_encoding('LATIN9')

>>> cur.execute("SELECT data FROM test WHERE num = 74")
>>> x = cur.fetchone()[0]
>>> print(type(x), repr(x))
 '\xe0\xe8\xec\xf2\xf9\xa4'

>>> psycopg2.extensions.register_type(psycopg2.extensions.UNICODE, cur)

>>> cur.execute("SELECT data FROM test WHERE num = 74")
>>> x = cur.fetchone()[0]
>>> print(x, type(x), repr(x))
àèìòù€  u'\xe0\xe8\xec\xf2\xf9\u20ac'

import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)

import psycopg2.extensions
psycopg2.extensions.register_type(psycopg2.extensions.BYTES, conn)
psycopg2.extensions.register_type(psycopg2.extensions.BYTESARRAY, conn)
cur = conn.cursor()
cur.execute("select %s::text", (u"€",))
cur.fetchone()[0]
b'\xe2\x82\xac'

mypic = open('picture.png', 'rb').read()
curs.execute("insert into blobs (file) values (%s)",
    (psycopg2.Binary(mypic),))

>>> dt = datetime.datetime.now()
>>> dt
datetime.datetime(2010, 2, 8, 1, 40, 27, 425337)

>>> cur.mogrify("SELECT %s, %s, %s;", (dt, dt.date(), dt.time()))
"SELECT '2010-02-08T01:40:27.425337', '2010-02-08', '01:40:27.425337';"

>>> cur.mogrify("SELECT %s;", (dt - datetime.datetime(2010,1,1),))
"SELECT '38 days 6027.425337 seconds';"

>>> cur.execute("SET TIME ZONE 'Europe/Rome'")  # UTC + 1 hour
>>> cur.execute("SELECT '2010-01-01 10:30:45'::timestamptz")
>>> cur.fetchone()[0]
datetime.datetime(2010, 1, 1, 10, 30, 45,
    tzinfo=datetime.timezone(datetime.timedelta(seconds=3600)))

>>> cur.execute("SET TIME ZONE 'Asia/Calcutta'")  # offset was +5:21:10
>>> cur.execute("SELECT '1900-01-01 10:30:45'::timestamptz")
>>> cur.fetchone()[0].tzinfo
# On Python 3.6: 5h, 21m
datetime.timezone(datetime.timedelta(0, 19260))
# On Python 3.7 and following: 5h, 21m, 10s
datetime.timezone(datetime.timedelta(seconds=19270))

class InfDateAdapter:
    def __init__(self, wrapped):
        self.wrapped = wrapped
    def getquoted(self):
        if self.wrapped == datetime.date.max:
            return b"'infinity'::date"
        elif self.wrapped == datetime.date.min:
            return b"'-infinity'::date"
        else:
            return psycopg2.extensions.DateFromPy(self.wrapped).getquoted()

psycopg2.extensions.register_adapter(datetime.date, InfDateAdapter)

>>> cur.execute("SELECT '24:00:00'::time - '00:00:00'::time")
>>> cur.fetchone()[0]
datetime.timedelta(days=1)

>>> cur.execute("SELECT '24:00:00'::time, '00:00:00'::time")
>>> cur.fetchone()
(datetime.time(0, 0), datetime.time(0, 0))

>>> cur.mogrify("SELECT %s;", ([10, 20, 30], ))
'SELECT ARRAY[10,20,30];'

ids = [10, 20, 30]
cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (ids,))

>>> cur.mogrify("SELECT %s IN %s;", (10, (10, 20, 30)))
'SELECT 10 IN (10, 20, 30);'

Transactions control

In Psycopg transactions are handled by the connection class. By default, the first time a command is sent to the database (using one of the cursor s created by the connection), a new transaction is created. The following database commands will be executed in the context of the same transaction - not only the commands issued by the first cursor, but the ones issued by all the cursors created by the same connection. Should any command fail, the transaction will be aborted and no further command will be executed until a call to the rollback() method.

The connection is responsible for terminating its transaction, calling either the commit() or rollback() method. Committed changes are immediately made persistent in the database. If the connection is closed (using the close() method) or destroyed (using del or by letting it fall out of scope) while a transaction is in progress, the server will discard the transaction. However doing so is not advisable: middleware such as PgBouncer may see the connection closed uncleanly and dispose of it.

It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible. A few commands (e.g. CREATE DATABASE , VACUUM , CALL on stored procedures using transaction control…) require to be run outside any transaction: in order to be able to run these commands from Psycopg, the connection must be in autocommit mode: you can use the autocommit property.

A few other transaction properties can be set session-wide by the connection : for instance it is possible to have read-only transactions or change the isolation level. See the set_session() method for all the details.

with psycopg2.connect(DSN) as conn:
    with conn.cursor() as curs:
        curs.execute(SQL)

conn = psycopg2.connect(DSN)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL1)

with conn:
    with conn.cursor() as curs:
        curs.execute(SQL2)

conn.close()

conn = psycopg2.connect(DSN)
try:
    # connection usage
finally:
    conn.close()

Server side cursors

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returns a huge amount of data, a proportionally large amount of memory will be allocated by the client.

If the dataset is too large to be practically handled on the client side, it is possible to create a server side cursor. Using this kind of cursor it is possible to transfer to the client only a controlled amount of data, so that a large dataset can be examined without keeping it entirely in memory.

Server side cursor are created in PostgreSQL using the DECLARE command and subsequently handled using MOVE , FETCH and CLOSE commands.

Psycopg wraps the database server side cursor in named cursors . A named cursor is created using the cursor() method specifying the name parameter. Such cursor will behave mostly like a regular cursor, allowing the user to move in the dataset using the scroll() method and to read the data using fetchone() and fetchmany() methods. Normally you can only scroll forward in a cursor: if you need to scroll backwards you should declare your cursor scrollable .

Named cursors are also iterable like regular cursors. Note however that before Psycopg 2.4 iteration was performed fetching one record at time from the backend, resulting in a large overhead. The attribute itersize now controls how many records are fetched at time during the iteration: the default value of 2000 allows to fetch about 100KB per roundtrip assuming records of 10-20 columns of mixed number and strings; you may decrease this value if you are dealing with huge records.

Named cursors are usually created WITHOUT HOLD , meaning they live only as long as the current transaction. Trying to fetch from a named cursor after a commit() or to create a named cursor when the connection is in autocommit mode will result in an exception. It is possible to create a WITH HOLD cursor by specifying a True value for the withhold parameter to cursor() or by setting the withhold attribute to True before calling execute() on the cursor. It is extremely important to always close() such cursors, otherwise they will continue to hold server-side resources until the connection will be eventually closed. Also note that while WITH HOLD cursors lifetime extends well after commit() , calling rollback() will automatically close the cursor.

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

cur1 = conn.cursor()
cur1.callproc('reffunc', ['curname'])

cur2 = conn.cursor('curname')
for record in cur2:     # or cur2.fetchone, fetchmany...
    # do something with record
    pass

Thread and process safety

The Psycopg module and the connection objects are thread-safe : many threads can access the same database either using separate sessions and creating a connection per thread or using the same connection and creating separate cursor s. In DB API 2.0 parlance, Psycopg is level 2 thread safe .

The difference between the above two approaches is that, using different connections, the commands will be executed in different sessions and will be served by different server processes. On the other hand, using many cursors on the same connection, all the commands will be executed in the same session (and in the same transaction if the connection is not in autocommit mode), but they will be serialized.

The above observations are only valid for regular threads: they don’t apply to forked processes nor to green threads. libpq connections shouldn’t be used by a forked processes , so when using a module such as multiprocessing or a forking web deploy method such as FastCGI make sure to create the connections after the fork.

Connections shouldn’t be shared either by different green threads: see Support for coroutine libraries for further details.

Using COPY TO and COPY FROM

Psycopg cursor objects provide an interface to the efficient PostgreSQL COPY command to move data from files to tables and back.

Currently no adaptation is provided between Python and PostgreSQL types on COPY : the file can be any Python file-like object but its format must be in the format accepted by PostgreSQL COPY command (data format, escaped characters, etc).

The methods exposed are:

copy_from()

Reads data from a file-like object appending them to a database table ( COPY table FROM file syntax). The source file must provide both read() and readline() method.

copy_to()

Writes the content of a table to a file-like object ( COPY table TO file syntax). The target file must have a write() method.

copy_expert()

Allows to handle more specific cases and to use all the COPY features available in PostgreSQL.

Please refer to the documentation of the single methods for details and examples.

Access to PostgreSQL large objects

PostgreSQL offers support for large objects , which provide stream-style access to user data that is stored in a special large-object structure. They are useful with data values too large to be manipulated conveniently as a whole.

Psycopg allows access to the large object using the lobject class. Objects are generated using the connection.lobject() factory method. Data can be retrieved either as bytes or as Unicode strings.

Psycopg large object support efficient import/export with file system files using the lo_import() and lo_export() libpq functions.

Two-Phase Commit protocol support

Psycopg exposes the two-phase commit features available since PostgreSQL 8.1 implementing the two-phase commit extensions proposed by the DB API 2.0.

The DB API 2.0 model of two-phase commit is inspired by the XA specification , according to which transaction IDs are formed from three components:

• a format ID (non-negative 32 bit integer)

• a global transaction ID (string not longer than 64 bytes)

• a branch qualifier (string not longer than 64 bytes)

For a particular global transaction, the first two components will be the same for all the resources. Every resource will be assigned a different branch qualifier.

According to the DB API 2.0 specification, a transaction ID is created using the connection.xid() method. Once you have a transaction id, a distributed transaction can be started with connection.tpc_begin() , prepared using tpc_prepare() and completed using tpc_commit() or tpc_rollback() . Transaction IDs can also be retrieved from the database using tpc_recover() and completed using the above tpc_commit() and tpc_rollback() .

PostgreSQL doesn’t follow the XA standard though, and the ID for a PostgreSQL prepared transaction can be any string up to 200 characters long. Psycopg’s Xid objects can represent both XA-style transactions IDs (such as the ones created by the xid() method) and PostgreSQL transaction IDs identified by an unparsed string.

The format in which the Xids are converted into strings passed to the database is the same employed by the PostgreSQL JDBC driver : this should allow interoperation between tools written in Python and in Java. For example a recovery tool written in Python would be able to recognize the components of transactions produced by a Java program.

For further details see the documentation for the above methods.