ZetCode

Python psycopg 3

last modified November 17, 2026 Python 3.15

In this article we show how to work with PostgreSQL databases in Python using psycopg 3.

psycopg 3 is the current PostgreSQL database adapter from the authors of psycopg2. It is a complete rewrite: it is installed and imported as psycopg, it supports asyncio natively, it sends query parameters separately from the query text (server-side binding), and it can use PostgreSQL's binary protocol, pipeline mode, and automatic prepared statements. Its API stays close to psycopg2 and to the Python Database API Specification v2.0 (PEP 249), so most psycopg2 code needs only small changes.

The older psycopg2 adapter is covered in the Python PostgreSQL article; the last section of this article lists the differences between the two. The examples were tested with Python 3.15, psycopg 3.3.9, psycopg-pool 3.3.3, and PostgreSQL 18 on Linux.

PostgreSQL

PostgreSQL is a powerful, open source object-relational database system. It runs on Linux, BSD systems, macOS, Windows, and other platforms and is developed by the PostgreSQL Global Development Group.

For the examples, we need a running PostgreSQL server with a testdb database. The quickest way to get one is a Docker container. The official postgres image creates the database given in POSTGRES_DB and sets the password of the postgres superuser.

$ docker run -d --name pg -e POSTGRES_PASSWORD='s$cret' \
    -e POSTGRES_DB=testdb -p 5432:5432 postgres:18

Installing psycopg 3

The package on PyPI is called psycopg. It comes in three implementations: a pure Python one, which needs the libpq library installed on the system; a C extension built locally (psycopg[c]); and precompiled wheels that bundle libpq (psycopg[binary]). The binary package is the easiest way to start. Connection pools live in the separate psycopg-pool package.

$ pip install "psycopg[binary]" psycopg-pool

Checking the implementation

Whichever implementation is installed, the code imports psycopg. The following program shows which one is in use.

implementation.py
import psycopg

print('psycopg', psycopg.__version__)
print('implementation:', psycopg.pq.__impl__)
print('libpq:', psycopg.pq.version())

The psycopg.pq.__impl__ attribute is python, c, or binary. The pq.version function returns the version of the libpq library; 180006 stands for version 18.6.

$ python implementation.py
psycopg 3.3.9
implementation: binary
libpq: 180006

PostgreSQL version

In the first example, we get the version of the PostgreSQL server.

version.py
import psycopg

with psycopg.connect(dbname='testdb', user='postgres',
                     password='s$cret', host='localhost') as conn:

    row = conn.execute('SELECT version()').fetchone()
    print(row[0])

We connect to the testdb database, execute a query, and print the result.

with psycopg.connect(dbname='testdb', user='postgres',
                     password='s$cret', host='localhost') as conn:

The connect function creates a new database session and returns a Connection object. The keyword arguments are passed to libpq; any parameter we omit gets its default value, for example port 5432. Used in a with statement, the connection is closed when the block ends.

row = conn.execute('SELECT version()').fetchone()
print(row[0])

The execute method of the connection is a shortcut: it creates a cursor, executes the statement on it, and returns the cursor. The fetchone method returns the next row of the result as a tuple.

$ python version.py
PostgreSQL 18.6 (Debian 18.6-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit

Connection parameters

Instead of keyword arguments, we can pass a connection string to connect. It is either a list of key=value pairs or a URI such as postgresql://postgres:s%24cret@localhost/testdb. In a URI, special characters in the password must be percent-encoded; the $ sign becomes %24.

connection_info.py
import psycopg

conninfo = 'postgresql://postgres:s%24cret@localhost/testdb'

with psycopg.connect(conninfo) as conn:

    print(conn.info.dsn)
    print(conn.info.server_version)
    print(conn.info.dbname, conn.info.user, conn.info.host)
    print(conn.info.parameter_status('server_encoding'))
    print(conn.info.parameter_status('TimeZone'))

The info attribute of the connection is a ConnectionInfo object with details about the session.

print(conn.info.dsn)

The dsn attribute contains the effective connection parameters, including defaults filled in by libpq. The password is left out.

print(conn.info.parameter_status('server_encoding'))
print(conn.info.parameter_status('TimeZone'))

The server reports some of its settings to the client when the connection starts; parameter_status reads them without a query.

$ python connection_info.py
user=postgres dbname=testdb host=localhost hostaddr=127.0.0.1 port=5432 sslcertmode=allow
180006
testdb postgres localhost
UTF8
Etc/UTC

Building connection strings

The psycopg.conninfo module parses and builds connection strings. This is useful when part of the connection comes from configuration and part from code.

conninfo.py
from psycopg.conninfo import conninfo_to_dict, make_conninfo

base = 'postgresql://postgres@localhost/testdb?connect_timeout=10'

print(conninfo_to_dict(base))

dsn = make_conninfo(base, password='s$cret', application_name='report')
print(dsn)

The conninfo_to_dict function turns a connection string or URI into a dictionary. The make_conninfo function merges a connection string with keyword arguments and returns a new string, quoting values when necessary.

$ python conninfo.py
{'user': 'postgres', 'connect_timeout': '10', 'dbname': 'testdb', 'host': 'localhost'}
user=postgres connect_timeout=10 dbname=testdb host=localhost password=s$cret application_name=report

Environment variables

Hard-coding passwords is a bad practice. psycopg uses libpq, so it honours the standard PostgreSQL environment variables PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD, and it reads passwords from the ~/.pgpass file.

connect_env.py
import psycopg

with psycopg.connect() as conn:

    row = conn.execute('SELECT current_user, current_database()').fetchone()
    print(row)

We call connect without arguments; all the parameters come from the environment. The current_user and current_database functions tell us where we are connected.

$ export PGHOST=localhost PGUSER=postgres PGDATABASE=testdb
$ export PGPASSWORD='s$cret'
$ python connect_env.py
('postgres', 'testdb')

Context managers

Connections and cursors can be used in a with statement. A cursor is closed when its with block ends. For a connection, the block does two things: when it ends, the transaction is committed (or rolled back if an exception was raised) and then the connection is closed. This differs from psycopg2, where with conn: only ends the transaction and leaves the connection open.

context_managers.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with conn.cursor() as cur:
        cur.execute('SELECT 1')

    print('cursor closed:', cur.closed)
    print('connection closed:', conn.closed)

print('connection closed:', conn.closed)

We check the closed attribute of the cursor and of the connection inside and after the blocks. Unlike in psycopg2, where closed is an integer, it is a boolean here.

$ python context_managers.py
cursor closed: True
connection closed: False
connection closed: True

Commit and rollback in a with block

By default, psycopg starts a transaction with the first statement. The connection's with block commits it when the block ends normally. If an exception leaves the block, the changes are rolled back.

context_rollback.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:
    conn.execute('CREATE TABLE IF NOT EXISTS notes(id SERIAL, body TEXT)')
    conn.execute("INSERT INTO notes(body) VALUES('first')")

try:
    with psycopg.connect(conninfo) as conn:
        conn.execute("INSERT INTO notes(body) VALUES('second')")
        raise ValueError('something went wrong')

except ValueError as e:
    print('error:', e)

with psycopg.connect(conninfo) as conn:
    rows = conn.execute('SELECT body FROM notes').fetchall()
    print(rows)
    conn.execute('DROP TABLE notes')

The first block inserts a row and ends normally, so the row is committed. The second block inserts a row and then raises an exception. The transaction is rolled back, the connection is closed, and the exception propagates to our except clause. The third connection sees only the first row.

$ python context_rollback.py
error: something went wrong
[('first',)]

Creating a table

The execute method runs any SQL statement, including DDL statements like CREATE TABLE. Here we call it directly on the connection.

create_table.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS cars')
    conn.execute('''CREATE TABLE cars(id SERIAL PRIMARY KEY,
                    name VARCHAR(255) NOT NULL, price INT)''')

    conn.execute("INSERT INTO cars(name, price) VALUES('Audi', 52642)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Mercedes', 57127)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Skoda', 9000)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Volvo', 29000)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Bentley', 350000)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Citroen', 21000)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Hummer', 41400)")
    conn.execute("INSERT INTO cars(name, price) VALUES('Volkswagen', 21600)")

The program creates the cars table and inserts eight rows into it. Thanks to the with block, the transaction is committed at the end. We check the table with psql inside the container.

$ python create_table.py
$ docker exec pg psql -U postgres -d testdb -c 'SELECT * FROM cars'
 id |    name    | price
----+------------+--------
  1 | Audi       |  52642
  2 | Mercedes   |  57127
  3 | Skoda      |   9000
  4 | Volvo      |  29000
  5 | Bentley    | 350000
  6 | Citroen    |  21000
  7 | Hummer     |  41400
  8 | Volkswagen |  21600
(8 rows)

The executemany method

The executemany method executes a statement for each item of a sequence of parameters. In psycopg2 it ran one statement at a time; in psycopg 3 it uses pipeline mode, which sends all the statements without waiting for each reply, so it is fast without any helper functions.

execute_many.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

cars = [
    ('Audi', 52642),
    ('Mercedes', 57127),
    ('Skoda', 9000),
    ('Volvo', 29000),
    ('Bentley', 350000),
    ('Citroen', 21000),
    ('Hummer', 41400),
    ('Volkswagen', 21600),
]

with psycopg.connect(conninfo) as conn:

    with conn.cursor() as cur:

        cur.execute('DROP TABLE IF EXISTS cars')
        cur.execute('''CREATE TABLE cars(id SERIAL PRIMARY KEY,
                       name VARCHAR(255) NOT NULL, price INT)''')

        cur.executemany('INSERT INTO cars(name, price) VALUES(%s, %s)', cars)
        print('rows inserted:', cur.rowcount)

The program recreates the cars table and fills it from a list of tuples. The %s placeholders are replaced by the values of each tuple. After executemany, the rowcount attribute holds the total number of affected rows.

$ python execute_many.py
rows inserted: 8

The executemany method with RETURNING

With the returning=True option, executemany keeps the result of every statement. It is useful with INSERT ... RETURNING, for example to get the ids generated for the new rows.

executemany_returning.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

colours = ['red', 'green', 'blue', 'yellow']

with psycopg.connect(conninfo) as conn:

    with conn.cursor() as cur:

        cur.execute('DROP TABLE IF EXISTS colours')
        cur.execute('CREATE TABLE colours(id SERIAL PRIMARY KEY, name TEXT)')

        cur.executemany('INSERT INTO colours(name) VALUES(%s) RETURNING id, name',
                        [(c,) for c in colours], returning=True)

        for result in cur.results():
            print(result.fetchone())

The cursor holds one result set per executed statement. The results method iterates over them; at each step the cursor is positioned on the next result, and we fetch its row.

[(c,) for c in colours]

Each parameter set must be a sequence or a mapping, so a single value is wrapped in a one-element tuple.

$ python executemany_returning.py
(1, 'red')
(2, 'green')
(3, 'blue')
(4, 'yellow')

Last inserted row id

psycopg 3 cursors have no lastrowid attribute. PostgreSQL has no notion of a last row id; to get the value generated for a SERIAL or identity column, we add a RETURNING clause to the INSERT statement.

returning_id.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with conn.cursor() as cur:

        cur.execute('DROP TABLE IF EXISTS words')
        cur.execute('CREATE TABLE words(id SERIAL PRIMARY KEY, word VARCHAR(255))')

        cur.execute('INSERT INTO words(word) VALUES(%s) RETURNING id', ('forest',))
        print('id:', cur.fetchone()[0])

        cur.execute('INSERT INTO words(word) VALUES(%s) RETURNING id', ('cloud',))
        print('id:', cur.fetchone()[0])

The RETURNING id clause makes the INSERT statement return the new id as a one-row result, which we read with fetchone.

$ python returning_id.py
id: 1
id: 2

Fetching all rows

The fetchall method fetches all the remaining rows of a query result and returns them as a list of tuples.

fetch_all.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with conn.cursor() as cur:

        cur.execute('SELECT * FROM cars')
        rows = cur.fetchall()

        for row in rows:
            print(f'{row[0]} {row[1]} {row[2]}')

We select all rows of the cars table and print them one per line.

$ python fetch_all.py
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600

Fetching one row

The fetchone method returns the next row of a query result, or None when no more rows are available.

fetch_one.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    cur = conn.execute('SELECT * FROM cars ORDER BY price DESC')

    print(cur.fetchone())
    print(cur.fetchone())

    print('rows in result:', cur.rowcount)
    print('position:', cur.rownumber)

We fetch the two most expensive cars. With a client-side cursor, the whole result is already in the client after execute, so rowcount tells us its size. The rownumber attribute is the index of the next row to fetch.

$ python fetch_one.py
(5, 'Bentley', 350000)
(2, 'Mercedes', 57127)
rows in result: 8
position: 2

Fetching rows in batches

The fetchmany method returns the next batch of rows. Its argument is the batch size; without one, it uses the cursor's arraysize attribute, which is 1 by default.

fetch_many.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    cur = conn.execute('SELECT id, name FROM cars ORDER BY id')

    while rows := cur.fetchmany(3):
        print(rows)

We fetch the cars three at a time. The walrus operator assigns the batch and ends the loop when fetchmany returns an empty list.

$ python fetch_many.py
[(1, 'Audi'), (2, 'Mercedes'), (3, 'Skoda')]
[(4, 'Volvo'), (5, 'Bentley'), (6, 'Citroen')]
[(7, 'Hummer'), (8, 'Volkswagen')]

Iterating over a cursor

A cursor is iterable. Since conn.execute returns the cursor, we can loop over a query result directly.

iterate_cursor.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    for name, price in conn.execute('SELECT name, price FROM cars WHERE price < 30000'):
        print(f'{name}: {price}')

Each row is a tuple, so we unpack it in the for statement.

$ python iterate_cursor.py
Skoda: 9000
Volvo: 29000
Citroen: 21000
Volkswagen: 21600

Dictionary rows

The form of the rows is decided by a row factory. The default factory, tuple_row, returns tuples. The psycopg.rows module has others; dict_row returns plain dictionaries keyed by column name. A row factory can be set on the connection, on a cursor, or changed later through the row_factory attribute.

dict_row.py
import psycopg
from psycopg.rows import dict_row

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo, row_factory=dict_row) as conn:

    rows = conn.execute('SELECT * FROM cars WHERE price > 50000').fetchall()

    for row in rows:
        print(row)

    print(row['name'], type(row))

We pass dict_row to connect, so every cursor of the connection returns dictionaries. Unlike psycopg2's DictRow, the rows are ordinary dict objects that can be passed directly to json.dumps and similar functions.

$ python dict_row.py
{'id': 1, 'name': 'Audi', 'price': 52642}
{'id': 2, 'name': 'Mercedes', 'price': 57127}
{'id': 5, 'name': 'Bentley', 'price': 350000}
Bentley <class 'dict'>

Named tuple rows

The namedtuple_row factory returns named tuples. Their fields can be accessed by name or by index, and they use less memory than dictionaries.

namedtuple_row.py
import psycopg
from psycopg.rows import namedtuple_row

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with conn.cursor(row_factory=namedtuple_row) as cur:

        cur.execute('SELECT name, price FROM cars ORDER BY price LIMIT 3')

        for car in cur:
            print(car)

        print(car.name, car.price)

This time the row factory is set on a cursor. The named tuple type is created from the column names of the result; it is called Row.

$ python namedtuple_row.py
Row(name='Skoda', price=9000)
Row(name='Citroen', price=21000)
Row(name='Volkswagen', price=21600)
Volkswagen 21600

Rows as objects

The class_row factory creates an object of a given class for each row, passing the columns as keyword arguments. It works well with dataclasses, Pydantic models, and similar classes.

class_row.py
from dataclasses import dataclass

import psycopg
from psycopg.rows import class_row

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

@dataclass
class Car:
    id: int
    name: str
    price: int

    def with_tax(self, rate=0.2):
        return round(self.price * (1 + rate))

with psycopg.connect(conninfo) as conn:

    with conn.cursor(row_factory=class_row(Car)) as cur:

        cur.execute('SELECT id, name, price FROM cars WHERE id <= %s', (3,))

        for car in cur:
            print(car, car.with_tax())

The Car dataclass has a field for each selected column and an extra method. The query returns Car objects, on which we can call with_tax. The names of the columns must match the parameters of the class; we can use AS aliases in the query to rename columns.

$ python class_row.py
Car(id=1, name='Audi', price=52642) 63170
Car(id=2, name='Mercedes', price=57127) 68552
Car(id=3, name='Skoda', price=9000) 10800

Single values

When a query returns a single column, the scalar_row factory returns the value itself instead of a one-element tuple.

scalar_row.py
import psycopg
from psycopg.rows import scalar_row

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    cur = conn.cursor(row_factory=scalar_row)

    names = cur.execute('SELECT name FROM cars ORDER BY name').fetchall()
    print(names)

    total = cur.execute('SELECT sum(price) FROM cars').fetchone()
    print(total)

The first query returns a list of names instead of a list of tuples. The second returns the sum directly, without [0].

$ python scalar_row.py
['Audi', 'Bentley', 'Citroen', 'Hummer', 'Mercedes', 'Skoda', 'Volkswagen', 'Volvo']
581769

Custom row factory

A row factory is a callable that receives the cursor and returns a function that turns a sequence of values into a row. The outer callable is invoked once per result, so it can prepare everything that depends on the columns.

custom_row.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

def upper_keys(cursor):

    names = [col.name.upper() for col in cursor.description]

    def make_row(values):
        return dict(zip(names, values))

    return make_row

with psycopg.connect(conninfo, row_factory=upper_keys) as conn:

    row = conn.execute('SELECT name, price FROM cars WHERE id = 1').fetchone()
    print(row)

The upper_keys factory reads the column names from cursor.description once, converts them to upper case, and returns a function that builds a dictionary for each row.

$ python custom_row.py
{'NAME': 'Audi', 'PRICE': 52642}

Parameterized queries

Values should never be put into an SQL string with string formatting. Instead, we use placeholders and pass the values separately. psycopg uses %s for positional parameters and %(name)s for named parameters, whatever the type of the value.

parameterized_query.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    row = conn.execute('SELECT * FROM cars WHERE id = %s', (5,)).fetchone()
    print(row)

    row = conn.execute('SELECT * FROM cars WHERE name = %(name)s',
                       {'name': 'Volvo'}).fetchone()
    print(row)

    rows = conn.execute('SELECT name FROM cars WHERE price BETWEEN %(lo)s AND %(hi)s',
                        {'lo': 20000, 'hi': 30000}).fetchall()
    print(rows)

The positional parameters are passed in a tuple, the named ones in a dictionary. A named parameter can appear several times in a query.

conn.execute('SELECT * FROM cars WHERE id = %s', (5,))

The parameters must be a sequence or a mapping, even for one value. Note the trailing comma: (5) is just the number 5.

$ python parameterized_query.py
(5, 'Bentley', 350000)
(4, 'Volvo', 29000)
[('Volvo',), ('Citroen',), ('Volkswagen',)]

Lists, ANY and LIKE

A Python list is adapted to a PostgreSQL array. To match a column against a list of values, we use the = ANY(%s) condition. The IN %s form, which works with tuples in psycopg2, does not work in psycopg 3.

query_any.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    ids = [1, 3, 5]
    rows = conn.execute('SELECT id, name FROM cars WHERE id = ANY(%s)',
                        (ids,)).fetchall()
    print(rows)

    rows = conn.execute('SELECT name FROM cars WHERE name LIKE %s',
                        ('V%',)).fetchall()
    print(rows)

    rows = conn.execute("SELECT name FROM cars WHERE name LIKE 'Me%%' AND price > %s",
                        (50000,)).fetchall()
    print(rows)

    try:
        conn.execute('SELECT name FROM cars WHERE id IN %s', ((1, 3, 5),))
    except psycopg.errors.SyntaxError as e:
        print('IN %s failed:', e.diag.message_primary)

The program selects cars by a list of ids, and by name patterns.

rows = conn.execute("SELECT name FROM cars WHERE name LIKE 'Me%%' AND price > %s",
                    (50000,)).fetchall()

When a query has parameters, a literal % sign must be written as %%. In the second query, the pattern is passed as a parameter, so no escaping is needed.

conn.execute('SELECT name FROM cars WHERE id IN %s', ((1, 3, 5),))

The error message shows what the server received: the placeholder became $1, and IN $1 is not valid SQL. The next section explains why.

$ python query_any.py
[(1, 'Audi'), (3, 'Skoda'), (5, 'Bentley')]
[('Volvo',), ('Volkswagen',)]
[('Mercedes',)]
IN %s failed: syntax error at or near "$1"

Server-side binding

psycopg2 merged the parameters into the query text on the client and sent a complete SQL string. psycopg 3 sends the query with $1, $2, ... placeholders and the values separately; the server combines them. This is called server-side binding. It is safer and allows prepared statements and binary data, but the server accepts parameters only where a value is expected in a SELECT, INSERT, UPDATE, or DELETE statement. Commands like SET, NOTIFY, or CREATE TABLE do not take parameters.

server_binding.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    try:
        conn.execute('SET TimeZone TO %s', ('Europe/Bratislava',))
    except psycopg.errors.SyntaxError as e:
        print('SET failed:', e.diag.message_primary)
        conn.rollback()

    conn.execute("SELECT set_config('TimeZone', %s, false)", ('Europe/Bratislava',))
    print(conn.execute('SHOW TimeZone').fetchone()[0])

The SET command with a parameter fails with a syntax error. After the error, the transaction is in a failed state, so we roll it back. The set_config function does the same as SET but is an ordinary function, so its arguments can be parameters.

$ python server_binding.py
SET failed: syntax error at or near "$1"
Europe/Bratislava

Client-side binding

When server-side binding is not possible, the ClientCursor class merges the parameters into the query on the client, as psycopg2 did. Its mogrify method returns the query that would be sent to the server.

client_cursor.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with psycopg.ClientCursor(conn) as cur:

        query = 'SELECT * FROM cars WHERE name = %s AND price > %s'
        print(cur.mogrify(query, ("O'Brien", 1000)))

        cur.execute('SET TimeZone TO %s', ('Europe/Bratislava',))
        cur.execute('SHOW TimeZone')
        print(cur.fetchone()[0])

        print(cur.mogrify('SELECT name FROM cars WHERE id = ANY(%s)', ([1, 3, 5],)))

The mogrify method shows how the values are quoted: the single quote in the name is doubled. With a client cursor, the SET command with a parameter works.

print(cur.mogrify('SELECT name FROM cars WHERE id = ANY(%s)', ([1, 3, 5],)))

The list becomes an array literal with a type cast. psycopg chooses the smallest integer type that fits the values; PostgreSQL converts it when it compares the array with the column.

$ python client_cursor.py
SELECT * FROM cars WHERE name = 'O''Brien' AND price > 1000
Europe/Bratislava
SELECT name FROM cars WHERE id = ANY('{1,3,5}'::int2[])

SQL injection

SQL injection happens when user input is put into SQL text. A crafted input can change the meaning of the statement.

sql_injection.py
import sys
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

name = sys.argv[1]

with psycopg.connect(conninfo) as conn:

    unsafe = f"SELECT id, name FROM cars WHERE name = '{name}'"
    print('unsafe:', conn.execute(unsafe).fetchall())

    safe = 'SELECT id, name FROM cars WHERE name = %s'
    print('safe:  ', conn.execute(safe, (name,)).fetchall())

We run the same query with an f-string and with a query parameter. With an ordinary name, both give the same result. The second input closes the string literal and appends an OR '1'='1' condition, which is always true, so the unsafe query returns all rows. With a parameter, the input is sent separately and compared as one string; no car has such a name.

$ python sql_injection.py Skoda
unsafe: [(3, 'Skoda')]
safe:   [(3, 'Skoda')]
$ python sql_injection.py "x' OR '1'='1"
unsafe: [(1, 'Audi'), (2, 'Mercedes'), (3, 'Skoda'), (4, 'Volvo'), (5, 'Bentley'), (6, 'Citroen'), (7, 'Hummer'), (8, 'Volkswagen')]
safe:   []

Dynamic SQL with the sql module

Parameters can only replace values. Table and column names must be part of the SQL text. The psycopg.sql module builds such queries safely: sql.Identifier quotes names and sql.SQL holds trusted SQL fragments.

sql_compose.py
import psycopg
from psycopg import sql

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

def fetch(conn, table, columns, order_by):

    query = sql.SQL('SELECT {cols} FROM {table} ORDER BY {key} LIMIT %s').format(
        cols=sql.SQL(', ').join(map(sql.Identifier, columns)),
        table=sql.Identifier(table),
        key=sql.Identifier(order_by),
    )

    print(query.as_string(conn))
    return conn.execute(query, (3,)).fetchall()

with psycopg.connect(conninfo) as conn:

    print(fetch(conn, 'cars', ['name', 'price'], 'price'))
    print(fetch(conn, 'colours', ['id', 'name'], 'name'))

The fetch function selects columns from any table. The format method fills the {} fields with composed objects; the %s placeholder stays for the parameter. The as_string method shows the final query, with double-quoted identifiers.

$ python sql_compose.py
SELECT "name", "price" FROM "cars" ORDER BY "price" LIMIT %s
[('Skoda', 9000), ('Citroen', 21000), ('Volkswagen', 21600)]
SELECT "id", "name" FROM "colours" ORDER BY "name" LIMIT %s
[(3, 'blue'), (2, 'green'), (1, 'red')]

Template strings

Python 3.14 added template strings, or t-strings (PEP 750). They look like f-strings with a t prefix, but they produce a Template object that keeps the literal parts and the interpolated values apart. Since version 3.3, psycopg accepts t-strings in execute and turns each interpolation into a query parameter.

tstrings.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    price = 30000
    name = 'Skoda'

    rows = conn.execute(t'SELECT name, price FROM cars WHERE price < {price}').fetchall()
    print(rows)

    row = conn.execute(t'SELECT * FROM cars WHERE name = {name}').fetchone()
    print(row)

    table = 'colours'
    column = 'name'
    rows = conn.execute(t'SELECT {column:i} FROM {table:i} ORDER BY {column:i}').fetchall()
    print(rows)

The first two queries look like f-strings, but they are not vulnerable to SQL injection: price and name are sent as parameters.

rows = conn.execute(t'SELECT {column:i} FROM {table:i} ORDER BY {column:i}').fetchall()

A format specifier changes how a value is inserted. The :i specifier makes it a quoted identifier, the same as sql.Identifier. There is also :l for a literal merged on the client and :q for a nested template or sql object.

$ python tstrings.py
[('Skoda', 9000), ('Volvo', 29000), ('Citroen', 21000), ('Volkswagen', 21600)]
(3, 'Skoda', 9000)
[('blue',), ('green',), ('red',), ('yellow',)]

Type adaptation

psycopg converts Python objects to PostgreSQL values and back. Numbers, strings, booleans, dates and times, Decimal, timedelta, lists, and None work out of the box.

data_types.py
import datetime
from decimal import Decimal

import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    row = conn.execute('''SELECT 42, 3.5::float8, 12.30::numeric, true, 'text',
        DATE '2026-11-17', TIME '14:30:00',
        TIMESTAMP '2026-11-17 14:30:00',
        TIMESTAMPTZ '2026-11-17 14:30:00+01',
        INTERVAL '2 days 3 hours', ARRAY[1, 2, 3], NULL''').fetchone()

    for value in row:
        print(f'{type(value).__name__:10} {value!r}')

    print()

    params = (Decimal('19.99'), datetime.date(2026, 1, 1), [1.5, 2.5], None)
    row = conn.execute('SELECT %s, %s + 30, %s, %s::int IS NULL', params).fetchone()
    print(row)

The first query returns one value of each common type, and we print the Python type of each.

A timestamptz value is returned in the time zone of the session, as a datetime with a ZoneInfo object. The server in the container uses UTC, so 14:30 at +01 becomes 13:30 UTC. The numeric type is returned as Decimal, which keeps the exact value.

row = conn.execute('SELECT %s, %s + 30, %s, %s::int IS NULL', params).fetchone()

In the second query, Python values are sent as parameters. Adding 30 to a date gives a date 30 days later. A None parameter is sent as NULL with no type; in an expression where the server cannot infer the type, we have to add a cast, otherwise the query fails with IndeterminateDatatype.

$ python data_types.py
int        42
float      3.5
Decimal    Decimal('12.30')
bool       True
str        'text'
date       datetime.date(2026, 11, 17)
time       datetime.time(14, 30)
datetime   datetime.datetime(2026, 11, 17, 14, 30)
datetime   datetime.datetime(2026, 11, 17, 13, 30, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC'))
timedelta  datetime.timedelta(days=2, seconds=10800)
list       [1, 2, 3]
NoneType   None

(Decimal('19.99'), datetime.date(2026, 1, 31), [1.5, 2.5], True)

JSON, UUID, arrays and network addresses

PostgreSQL has many types that have counterparts in the Python standard library. psycopg adapts uuid.UUID and the ipaddress objects without any registration. For JSON, we wrap the value in Json or Jsonb, because a dictionary or a list on its own is ambiguous.

json_uuid.py
import ipaddress
import uuid

import psycopg
from psycopg.types.json import Jsonb

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS events')
    conn.execute('''CREATE TABLE events(id UUID PRIMARY KEY,
                    addr INET, tags TEXT[], data JSONB)''')

    conn.execute('INSERT INTO events VALUES(%s, %s, %s, %s)',
                 (uuid.UUID('2f1c6e1e-7c39-4a6b-9a1e-5c1d2b3a4f50'),
                  ipaddress.ip_address('192.168.0.10'),
                  ['login', 'web'],
                  Jsonb({'user': 'jane', 'attempts': 3, 'ok': True})))

    row = conn.execute('SELECT * FROM events').fetchone()

    for value in row:
        print(f'{type(value).__name__:15} {value}')

    row = conn.execute("SELECT data->>'user', (data->>'attempts')::int FROM events"
                       ' WHERE data @> %s', (Jsonb({'ok': True}),)).fetchone()
    print(row)

The table has columns of type UUID, INET, TEXT[], and JSONB. The values come back as UUID, IPv4Address, list, and dict objects. A JSONB value is stored in a binary form with sorted keys, so the key order may change.

' WHERE data @> %s', (Jsonb({'ok': True}),)

The @> operator tests whether the JSON document contains another one. The ->> operator extracts a field as text.

$ python json_uuid.py
UUID            2f1c6e1e-7c39-4a6b-9a1e-5c1d2b3a4f50
IPv4Address     192.168.0.10
list            ['login', 'web']
dict            {'ok': True, 'user': 'jane', 'attempts': 3}
('jane', 3)

Ranges

PostgreSQL range types, such as daterange or int4range, are adapted to Range objects from the psycopg.types.range module. By default a range includes its lower bound and excludes the upper one.

ranges.py
import datetime

import psycopg
from psycopg.types.range import Range

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS bookings')
    conn.execute('''CREATE TABLE bookings(guest TEXT, during DATERANGE,
                    EXCLUDE USING gist (during WITH &&))''')

    d = datetime.date
    conn.execute('INSERT INTO bookings VALUES(%s, %s)',
                 ('Jane', Range(d(2026, 12, 1), d(2026, 12, 5))))

    period = conn.execute('SELECT during FROM bookings').fetchone()[0]
    print(period, period.lower, period.upper, period.bounds)
    print(conn.execute('SELECT %s::date <@ during FROM bookings',
                       (d(2026, 12, 5),)).fetchone()[0])

    try:
        conn.execute('INSERT INTO bookings VALUES(%s, %s)',
                     ('Paul', Range(d(2026, 12, 4), d(2026, 12, 8))))
    except psycopg.errors.ExclusionViolation as e:
        print('booking rejected:', e.diag.message_primary)

The bookings table has an exclusion constraint: no two rows can have overlapping date ranges (&& is the overlap operator). The returned Range has lower, upper, and bounds attributes.

The <@ operator tests whether a value lies in a range; December 5 is not in the range because the upper bound is excluded. The second booking overlaps with the first, so PostgreSQL rejects it.

$ python ranges.py
[2026-12-01, 2026-12-05) 2026-12-01 2026-12-05 [)
False
booking rejected: conflicting key value violates exclusion constraint "bookings_during_excl"

Inserting an image

Binary data is stored in a BYTEA column. In psycopg 3, a bytes object is adapted to bytea directly; no wrapper is needed.

insert_image.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with open('mountains.jpg', 'rb') as f:
    data = f.read()

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS images')
    conn.execute('CREATE TABLE images(id SERIAL PRIMARY KEY, name TEXT, data BYTEA)')

    conn.execute('INSERT INTO images(name, data) VALUES(%s, %b)',
                 ('mountains.jpg', data))

    size = conn.execute('SELECT length(data) FROM images').fetchone()[0]
    print(f'stored {size} bytes')

We read an image file and insert its contents into the images table.

conn.execute('INSERT INTO images(name, data) VALUES(%s, %b)',
             ('mountains.jpg', data))

The %b placeholder asks for the parameter to be sent in binary format, so the bytes are transferred as they are, without encoding them as text. The %t placeholder requests text format, and %s lets psycopg choose.

$ python insert_image.py
stored 23917 bytes

Reading an image

A bytea value comes back as a bytes object. In psycopg2 it was a memoryview.

read_image.py
import hashlib

import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    name, data = conn.execute('SELECT name, data FROM images WHERE id = 1').fetchone()

print(type(data).__name__, len(data))

with open('copy.jpg', 'wb') as f:
    f.write(data)

for fname in (name, 'copy.jpg'):
    with open(fname, 'rb') as f:
        print(fname, hashlib.sha256(f.read()).hexdigest()[:16])

We read the image back and write it to copy.jpg. The SHA-256 hashes of the two files are equal, so the data survived the round trip.

$ python read_image.py
bytes 23917
mountains.jpg dcdcb94c540268ad
copy.jpg dcdcb94c540268ad

Binary format

PostgreSQL can return results in text or binary format. Text is the default. The binary format avoids parsing text and is often smaller. We ask for it with binary=True on a cursor or in execute.

binary_format.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    for binary in (False, True):

        cur = conn.cursor(binary=binary)
        cur.execute('SELECT 1234567::int4, 2.5::float8, now()::date')

        result = cur.pgresult
        formats = [result.fformat(i) for i in range(result.nfields)]
        sizes = [len(result.get_value(0, i)) for i in range(result.nfields)]

        print('binary' if binary else 'text', formats, sizes, cur.fetchone()[:2])

We run the same query with a text and a binary cursor. The pgresult attribute exposes the low-level libpq result. The fformat method returns 0 for a text column and 1 for a binary one, and get_value returns the raw data of a field.

The integer takes 7 bytes as text and 4 in binary, the float 3 and 8 bytes. For the Python code, the format makes no difference: the fetched values are the same.

$ python binary_format.py
text [0, 0, 0] [7, 3, 10] (1234567, 2.5)
binary [1, 1, 1] [4, 8, 4] (1234567, 2.5)

Column metadata

The description attribute of a cursor is a list of Column objects, one for each column of the result.

column_names.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    cur = conn.execute('SELECT * FROM cars LIMIT 3')

    for col in cur.description:
        print(col.name, col.type_code, col.type_display)

    names = [col.name for col in cur.description]
    print(f'{names[0]:3} {names[1]:10} {names[2]:>7}')

    for row in cur:
        print(f'{row[0]:<3} {row[1]:10} {row[2]:7}')

Each Column has a name and a type_code, which is the OID of the PostgreSQL type. The type_display attribute shows the type in a readable form, including the length modifier. We use the names as a header for the rows.

$ python column_names.py
id 23 int4
name 1043 varchar(255)
price 23 int4
id  name         price
1   Audi         52642
2   Mercedes     57127
3   Skoda         9000

Listing tables and columns

The information_schema views describe the objects in the database. We query them like any other table.

list_tables.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    tables = conn.execute('''SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'public' ORDER BY table_name''').fetchall()
    print([t[0] for t in tables])

    columns = conn.execute('''SELECT column_name, data_type, is_nullable
        FROM information_schema.columns
        WHERE table_name = %s ORDER BY ordinal_position''', ('cars',))

    for column in columns:
        print(column)

The program lists the tables in the public schema and the columns of the cars table with their types and nullability.

$ python list_tables.py
['cars', 'colours', 'events', 'images', 'words']
('id', 'integer', 'NO')
('name', 'character varying', 'NO')
('price', 'integer', 'YES')

Exporting data with COPY

The COPY command moves data between a table and a file much faster than INSERT and SELECT. In psycopg 3, cursor.copy runs a COPY ... TO STDOUT or COPY ... FROM STDIN statement and returns a Copy object that is used in a with block.

copy_to.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn, open('cars.csv', 'wb') as f:

    with conn.cursor() as cur:

        with cur.copy('COPY cars TO STDOUT (FORMAT csv, HEADER)') as copy:
            for data in copy:
                f.write(data)

        print(f'{cur.rowcount} rows exported')

Iterating over the Copy object returns the data in blocks of bytes, which we write to cars.csv. The FORMAT csv, HEADER options produce CSV with a header line.

$ python copy_to.py
8 rows exported
$ cat cars.csv
id,name,price
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600

Importing data with COPY

To load data, we use COPY ... FROM STDIN and pass the data to the write method of the Copy object.

copy_from.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn, open('cars.csv', 'rb') as f:

    conn.execute('DROP TABLE IF EXISTS cars_copy')
    conn.execute('CREATE TABLE cars_copy (LIKE cars)')

    with conn.cursor() as cur:

        with cur.copy('COPY cars_copy FROM STDIN (FORMAT csv, HEADER)') as copy:
            while data := f.read(8192):
                copy.write(data)

        print(f'{cur.rowcount} rows imported')

    rows = conn.execute('SELECT * FROM cars_copy WHERE price > 50000').fetchall()
    print(rows)

The program creates the cars_copy table with the structure of cars and fills it from the CSV file. The file is read in blocks of 8 KB, so even a large file does not have to fit in memory.

$ python copy_from.py
8 rows imported
[(1, 'Audi', 52642), (2, 'Mercedes', 57127), (5, 'Bentley', 350000)]

COPY with Python objects

With write_row, we can feed Python objects to COPY directly; psycopg converts them as it does query parameters. In the other direction, the rows method returns the copied data as tuples of Python objects.

copy_rows.py
import datetime

import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

start = datetime.datetime(2026, 11, 17, 8, 0, tzinfo=datetime.UTC)
readings = [(start + datetime.timedelta(minutes=15 * i), 'sensor-1', 20.5 + i / 4)
            for i in range(1000)]

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS readings')
    conn.execute('''CREATE TABLE readings(taken TIMESTAMPTZ, sensor TEXT,
                    temp NUMERIC(5, 2))''')

    with conn.cursor() as cur:

        with cur.copy('COPY readings (taken, sensor, temp) FROM STDIN') as copy:
            for row in readings:
                copy.write_row(row)

        print(f'{cur.rowcount} rows written')

        with cur.copy('COPY (SELECT * FROM readings ORDER BY taken LIMIT 3) TO STDOUT') as copy:
            copy.set_types(['timestamptz', 'text', 'numeric'])
            for row in copy.rows():
                print(row)

We generate a thousand sensor readings and load them with write_row. This is the fastest way to insert many rows from Python.

copy.set_types(['timestamptz', 'text', 'numeric'])
for row in copy.rows():
    print(row)

The COPY protocol does not tell the client the column types, so we declare them with set_types before reading rows. Without it, all values would come back as strings.

$ python copy_rows.py
1000 rows written
(datetime.datetime(2026, 11, 17, 8, 0, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC')), 'sensor-1', Decimal('20.50'))
(datetime.datetime(2026, 11, 17, 8, 15, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC')), 'sensor-1', Decimal('20.75'))
(datetime.datetime(2026, 11, 17, 8, 30, tzinfo=zoneinfo.ZoneInfo(key='Etc/UTC')), 'sensor-1', Decimal('21.00'))

Transactions

A transaction is an atomic unit of database operations: either all of them are applied or none. The transaction method of a connection returns a context manager that starts a transaction and commits it when the block ends, or rolls it back if an exception is raised. Unlike the connection's own with block, it does not close the connection.

create_accounts.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS accounts')
    conn.execute('''CREATE TABLE accounts(id INT PRIMARY KEY, owner TEXT,
                    balance NUMERIC(10, 2) CHECK (balance >= 0))''')
    conn.execute('''INSERT INTO accounts VALUES
                    (1, 'Alice', 100.00), (2, 'Bob', 50.00)''')

This program creates an accounts table. A CHECK constraint forbids negative balances.

transfer.py
import sys
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

def transfer(conn, src, dst, amount):

    try:
        with conn.transaction():
            conn.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s',
                         (amount, dst))
            conn.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s',
                         (amount, src))

        print(f'transferred {amount}')

    except psycopg.errors.CheckViolation as e:
        print(f'transfer of {amount} failed: {e.diag.message_primary}')

with psycopg.connect(conninfo, autocommit=True) as conn:

    transfer(conn, 1, 2, int(sys.argv[1]))
    print(conn.execute('SELECT owner, balance FROM accounts ORDER BY id').fetchall())

The transfer function moves money between two accounts. The first UPDATE adds the amount to the target account, the second subtracts it from the source. If the second statement violates the CHECK constraint, the transaction block rolls back the first one as well.

with psycopg.connect(conninfo, autocommit=True) as conn:

The transaction block is the recommended way to manage transactions; it works best with an autocommit connection, where every statement outside a block is committed immediately.

The first transfer of 30 succeeds. The second transfer of 120 would leave Alice with a negative balance; it fails and neither account changes.

$ python create_accounts.py
$ python transfer.py 30
transferred 30
[('Alice', Decimal('70.00')), ('Bob', Decimal('80.00'))]
$ python transfer.py 120
transfer of 120 failed: new row for relation "accounts" violates check constraint "accounts_balance_check"
[('Alice', Decimal('70.00')), ('Bob', Decimal('80.00'))]

Nested transactions

Transaction blocks can be nested. An inner block creates a savepoint; if it fails, only the work done since the savepoint is undone, and the outer transaction continues. Raising psycopg.Rollback rolls back a block on purpose without propagating an error.

savepoints.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS tasks')
    conn.execute('CREATE TABLE tasks(id INT PRIMARY KEY, title TEXT)')

    with conn.transaction():

        conn.execute("INSERT INTO tasks VALUES(1, 'write report')")

        try:
            with conn.transaction():
                conn.execute("INSERT INTO tasks VALUES(2, 'call Peter')")
                conn.execute("INSERT INTO tasks VALUES(1, 'duplicate')")
        except psycopg.errors.UniqueViolation as e:
            print('inner block rolled back:', e.diag.message_primary)

        with conn.transaction():
            conn.execute("INSERT INTO tasks VALUES(3, 'draft')")
            raise psycopg.Rollback()

        conn.execute("INSERT INTO tasks VALUES(4, 'send invoice')")

    print(conn.execute('SELECT * FROM tasks ORDER BY id').fetchall())

The outer block inserts task 1. The first inner block inserts task 2 and then a duplicate key, so it is rolled back to its savepoint: task 2 disappears, task 1 stays. The second inner block inserts task 3 and raises Rollback, which is caught by its own block. Finally, task 4 is inserted and the outer block commits.

$ python savepoints.py
inner block rolled back: duplicate key value violates unique constraint "tasks_pkey"
[(1, 'write report'), (4, 'send invoice')]

Uncommitted changes

Without a with block or a commit call, changes are not saved. The first statement starts a transaction, and closing the connection with close discards it.

no_commit.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

conn = psycopg.connect(conninfo)

conn.execute('CREATE TABLE friends(id SERIAL PRIMARY KEY, name TEXT)')
conn.execute("INSERT INTO friends(name) VALUES('Jane'), ('Tom')")

print(conn.execute('SELECT * FROM friends').fetchall())
print(conn.info.transaction_status.name)

conn.close()

The program creates the friends table and inserts two rows. Inside the session, the table and the rows exist. The transaction_status attribute is INTRANS: a transaction is open. After close, the table is gone.

$ python no_commit.py
[(1, 'Jane'), (2, 'Tom')]
INTRANS
$ docker exec pg psql -U postgres testdb -c "\dt friends"
Did not find any tables named "friends".

Autocommit

In autocommit mode, every statement is committed as soon as it executes. Some statements, like CREATE DATABASE or VACUUM, cannot run inside a transaction and need autocommit.

autocommit.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo, autocommit=True) as conn:

    print(conn.autocommit)

    conn.execute('DROP DATABASE IF EXISTS shop')
    conn.execute('CREATE DATABASE shop')

    conn.execute('DROP TABLE IF EXISTS friends')
    conn.execute('CREATE TABLE friends(id SERIAL PRIMARY KEY, name TEXT)')
    conn.execute("INSERT INTO friends(name) VALUES('Jane'), ('Tom')")
    print(conn.info.transaction_status.name)

    dbs = conn.execute('''SELECT datname FROM pg_database
                          WHERE NOT datistemplate ORDER BY datname''').fetchall()
    print([db[0] for db in dbs])

We pass autocommit=True to connect; it can also be set later through the autocommit attribute when no transaction is open. The program creates the shop database and the friends table. The transaction status stays IDLE.

$ python autocommit.py
True
IDLE
['postgres', 'shop', 'testdb']
$ docker exec pg psql -U postgres testdb -c "SELECT * FROM friends"
 id | name
----+------
  1 | Jane
  2 | Tom
(2 rows)

Handling errors

Database errors are raised as exceptions. The psycopg.errors module has a class for each PostgreSQL error code, such as UniqueViolation or UndefinedTable, so we can catch specific errors.

errors.py
import psycopg
from psycopg import errors

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo, autocommit=True) as conn:

    statements = [
        "INSERT INTO cars(id, name, price) VALUES(1, 'Tesla', 45000)",
        "INSERT INTO cars(name, price) VALUES(NULL, 45000)",
        'SELECT * FROM trucks',
        'SELECT 1 / 0',
    ]

    for stmt in statements:
        try:
            conn.execute(stmt)
        except errors.UniqueViolation as e:
            print('duplicate:', e.diag.constraint_name, e.sqlstate)
        except errors.NotNullViolation as e:
            print('missing value:', e.diag.column_name, e.sqlstate)
        except psycopg.Error as e:
            print(type(e).__name__, e.sqlstate, e.diag.message_primary)

try:
    psycopg.connect('dbname=testdb user=postgres password=wrong host=localhost')
except psycopg.OperationalError as e:
    print(e)

The program runs four statements that fail. A duplicate id raises UniqueViolation and a missing name NotNullViolation; the other errors are caught by the psycopg.Error base class.

print('duplicate:', e.diag.constraint_name, e.sqlstate)

The sqlstate attribute holds the five-character error code (called pgcode in psycopg2). The diag attribute gives details reported by the server, such as the violated constraint, the column, or the primary message.

except psycopg.OperationalError as e:
    print(e)

A failed connection raises OperationalError. Here the password is wrong.

$ python errors.py
duplicate: cars_pkey 23505
missing value: name 23502
UndefinedTable 42P01 relation "trucks" does not exist
DivisionByZero 22012 division by zero
connection failed: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "postgres"

Error classes

The error classes form a hierarchy defined by PEP 249. The lookup function finds the class for an error code or its name.

error_lookup.py
import psycopg
from psycopg import errors

print(errors.lookup('23505'))
print(errors.lookup('UNIQUE_VIOLATION'))

print(errors.UniqueViolation.__mro__[1:4])
print(issubclass(errors.CheckViolation, psycopg.IntegrityError))

Both the code 23505 and the name UNIQUE_VIOLATION map to UniqueViolation. It is a subclass of IntegrityError, so a handler for IntegrityError catches all constraint violations.

$ python error_lookup.py
<class 'psycopg.errors.UniqueViolation'>
<class 'psycopg.errors.UniqueViolation'>
(<class 'psycopg.IntegrityError'>, <class 'psycopg.DatabaseError'>, <class 'psycopg.Error'>)
True

Server-side cursors

A normal cursor receives the whole result of a query at once. For very large results, a server-side cursor keeps the result on the server and transfers it in chunks. We create one by giving the cursor a name.

server_cursor.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    with conn.cursor(name='squares') as cur:

        cur.itersize = 5000
        cur.execute('SELECT n, n::bigint * n FROM generate_series(1, 1000000) AS n')

        total = 0
        count = 0

        for n, square in cur:
            total += square
            count += 1

        print(f'{count} rows, sum of squares: {total}')

The query produces a million rows. While we iterate over the cursor, psycopg fetches itersize rows at a time (100 by default), so the client holds at most 5000 rows in memory. A named cursor exists only inside a transaction; the connection's with block provides one.

$ python server_cursor.py
1000000 rows, sum of squares: 333333833333500000

Streaming results

The stream method of a normal cursor is another way to process a large result without loading it all. It uses the libpq single-row or chunked mode: the rows of one query are received as they arrive, without declaring a named cursor.

stream.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    cur = conn.cursor()
    largest = ''
    count = 0

    for (word,) in cur.stream('SELECT md5(n::text) FROM generate_series(1, 200000) AS n',
                              size=1000):
        largest = max(largest, word)
        count += 1

    print(f'{count} rows, largest hash: {largest}')

We stream 200,000 hashes and keep the largest one. The size argument receives the rows in chunks of 1000, which is faster than one row at a time. The cursor cannot run other queries until the stream is exhausted or closed.

$ python stream.py
200000 rows, largest hash: fffffe98d0963d27015c198262d97221

Prepared statements

A prepared statement is parsed and planned once by the server and then executed many times with different parameters. psycopg prepares queries automatically: after a query has been executed prepare_threshold times (5 by default) on a connection, the next execution prepares it.

prepared.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

def prepared(conn):
    return conn.execute('SELECT count(*) FROM pg_prepared_statements',
                        prepare=False).fetchone()[0]

with psycopg.connect(conninfo) as conn:

    print('prepare_threshold:', conn.prepare_threshold)

    for i in range(1, 8):
        conn.execute('SELECT name FROM cars WHERE id = %s', (i,))
        print(f'run {i}: {prepared(conn)} prepared')

    conn.execute('SELECT price FROM cars WHERE id = %s', (1,), prepare=True)
    print('after prepare=True:', prepared(conn))

    rows = conn.execute('SELECT name, statement FROM pg_prepared_statements',
                        prepare=False)
    for row in rows:
        print(row)

We run the same query seven times with different ids and check the pg_prepared_statements view after each run. The query is prepared on the sixth run. The prepare=True argument prepares a query immediately, and prepare=False never prepares it; we use it for the monitoring query so it does not count itself.

Behind a connection pooler such as PgBouncer in transaction mode, a prepared statement may end up on a different server connection. Unless the pooler supports prepared statements, automatic preparation must be turned off with prepare_threshold=None.

$ python prepared.py
prepare_threshold: 5
run 1: 0 prepared
run 2: 0 prepared
run 3: 0 prepared
run 4: 0 prepared
run 5: 0 prepared
run 6: 1 prepared
run 7: 1 prepared
after prepare=True: 2
('_pg3_0', 'SELECT name FROM cars WHERE id = $1')
('_pg3_1', 'SELECT price FROM cars WHERE id = $1')

Pipeline mode

Normally the client sends a statement and waits for its result before sending the next one. In pipeline mode, the client sends many statements without waiting, and the results arrive later. This saves a network round trip per statement, which matters when the server is far away.

pipeline.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with psycopg.connect(conninfo) as conn:

    conn.execute('DROP TABLE IF EXISTS cities')
    conn.execute('CREATE TABLE cities(id SERIAL PRIMARY KEY, name TEXT, population INT)')

    cities = [('Bratislava', 475000), ('Kosice', 229000), ('Presov', 85000)]

    with conn.pipeline():

        cursors = [conn.execute('INSERT INTO cities(name, population) VALUES(%s, %s)'
                                ' RETURNING id', city) for city in cities]
        count = conn.execute('SELECT count(*) FROM cities')

    print([cur.fetchone()[0] for cur in cursors])
    print(count.fetchone()[0])

Inside the pipeline block, execute sends a statement and returns at once, without waiting for its result. When the block ends, psycopg waits for all the results and stores them in the cursors. After the block, we fetch the generated ids and the count. The statements are processed in order, so the count sees the three new rows.

$ python pipeline.py
[1, 2, 3]
3

Asynchronous queries

psycopg 3 supports asyncio. The AsyncConnection and AsyncCursor classes have the same methods as their synchronous counterparts, but the methods that talk to the server are coroutines.

async_query.py
import asyncio

import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

async def main():

    async with await psycopg.AsyncConnection.connect(conninfo) as conn:

        async with conn.cursor() as cur:

            await cur.execute('SELECT name, price FROM cars WHERE price > %s', (40000,))

            async for name, price in cur:
                print(name, price)

asyncio.run(main())

AsyncConnection.connect is a coroutine, so we await it before entering the async with block. We iterate over the cursor with async for.

$ python async_query.py
Audi 52642
Mercedes 57127
Bentley 350000
Hummer 41400

Running queries concurrently

With asyncio, we can wait for several queries at the same time. A connection runs one query at a time, so each concurrent query needs its own connection.

async_gather.py
import asyncio
import time

import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

async def slow_query(n):

    async with await psycopg.AsyncConnection.connect(conninfo) as conn:
        cur = await conn.execute('SELECT pg_sleep(1), %s::int * 10', (n,))
        row = await cur.fetchone()
        return row[1]

async def main():

    start = time.perf_counter()
    results = await asyncio.gather(*(slow_query(n) for n in range(1, 6)))
    elapsed = time.perf_counter() - start

    print(results)
    print(f'5 queries of 1 s took {elapsed:.1f} s')

asyncio.run(main())

Each call of slow_query opens a connection and runs a query that sleeps for one second. The asyncio.gather function runs five of them concurrently; together they take about one second instead of five. The results come back in the order of the calls.

$ python async_gather.py
[10, 20, 30, 40, 50]
5 queries of 1 s took 1.0 s

Connection pool

Opening a connection is slow. A connection pool keeps connections open and lends them to the parts of a program that need them. The psycopg_pool package provides ConnectionPool and AsyncConnectionPool.

pool.py
from concurrent.futures import ThreadPoolExecutor

from psycopg_pool import ConnectionPool

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

def price_of(pool, car_id):

    with pool.connection() as conn:
        row = conn.execute('SELECT name, price FROM cars WHERE id = %s',
                           (car_id,)).fetchone()
        return row

with ConnectionPool(conninfo, min_size=2, max_size=4) as pool:

    with ThreadPoolExecutor(max_workers=8) as executor:
        rows = list(executor.map(lambda i: price_of(pool, i), range(1, 9)))

    for row in rows:
        print(row)

    stats = pool.get_stats()
    print({k: stats[k] for k in ('pool_min', 'pool_max', 'requests_num')})

print('pool closed:', pool.closed)

The pool keeps at least two and at most four connections. Eight threads look up cars at the same time; each borrows a connection with pool.connection. When the with block ends, the transaction is committed and the connection goes back to the pool instead of being closed.

The get_stats method returns counters of the pool. When the pool's own with block ends, the pool closes all its connections.

$ python pool.py
('Audi', 52642)
('Mercedes', 57127)
('Skoda', 9000)
('Volvo', 29000)
('Bentley', 350000)
('Citroen', 21000)
('Hummer', 41400)
('Volkswagen', 21600)
{'pool_min': 2, 'pool_max': 4, 'requests_num': 8}
pool closed: True

Pool timeout

When all connections are busy and the pool is at its maximum size, a request waits for a connection to be returned. If none is returned within the timeout, PoolTimeout is raised. The default timeout is 30 seconds.

pool_timeout.py
from psycopg_pool import ConnectionPool, PoolTimeout

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with ConnectionPool(conninfo, min_size=1, max_size=1) as pool:

    with pool.connection() as conn:

        print('first connection:', conn.info.backend_pid > 0)

        try:
            with pool.connection(timeout=0.5) as conn2:
                pass
        except PoolTimeout as e:
            print('second request:', e)

    with pool.connection(timeout=0.5) as conn:
        print('connection returned and reused')

The pool has a single connection. While the first block holds it, the second request waits half a second and fails. After the first block returns the connection, the next request gets it.

$ python pool_timeout.py
first connection: True
second request: couldn't get a connection after 0.50 sec
connection returned and reused

LISTEN and NOTIFY

PostgreSQL can send notifications between sessions. A session subscribes to a channel with LISTEN, and another session sends a message with NOTIFY or the pg_notify function. The notifies method of a connection is a generator that waits for notifications.

listen_notify.py
import psycopg

conninfo = 'dbname=testdb user=postgres password=s$cret host=localhost'

with (psycopg.connect(conninfo, autocommit=True) as listener,
      psycopg.connect(conninfo, autocommit=True) as sender):

    listener.execute('LISTEN orders')

    sender.execute("SELECT pg_notify('orders', 'order 1001 created')")
    sender.execute('SELECT pg_notify(%s, %s)', ('orders', 'order 1002 created'))

    for note in listener.notifies(timeout=5, stop_after=2):
        print(note.channel, note.payload, note.pid == sender.info.backend_pid)

We open two autocommit connections: a notification is delivered only when the sending transaction commits. The sender uses pg_notify, which, being a function, accepts parameters.

for note in listener.notifies(timeout=5, stop_after=2):

The generator stops after two notifications or after five seconds. Each Notify object has the channel, the payload, and the process id of the sending backend.

$ python listen_notify.py
orders order 1001 created True
orders order 1002 created True

Migrating from psycopg2

Most psycopg2 code runs on psycopg 3 after changing the import. The differences shown in this article are summarized in the following table.

psycopg2psycopg 3
import psycopg2import psycopg
with conn: ends the transaction, connection stays openwith conn: ends the transaction and closes the connection
with conn: for a transactionwith conn.transaction():, nestable with savepoints
parameters merged on the clientserver-side binding; ClientCursor for the old behaviour
IN %s with a tuple= ANY(%s) with a list
execute_values, execute_batchexecutemany, or COPY with write_row
DictCursor, RealDictCursorrow_factory=dict_row
NamedTupleCursorrow_factory=namedtuple_row
Json(...) from psycopg2.extrasJson, Jsonb from psycopg.types.json
bytea returned as memoryviewbytea returned as bytes
copy_from, copy_to, copy_expertcursor.copy context manager
e.pgcodee.sqlstate
conn.closed is 0 or 1conn.closed is False or True
psycopg2.poolthe psycopg-pool package
select + poll for notificationsconn.notifies() generator
no asyncio supportAsyncConnection, AsyncConnectionPool

Source

psycopg 3 documentation

psycopg 3 – Differences from psycopg2

psycopg 3 – Row factories

psycopg 3 – sql module

psycopg 3 – Connection pools

PostgreSQL documentation

In this article we have worked with PostgreSQL databases in Python using psycopg 3. See also Python PostgreSQL with psycopg2, Python SQLite, Python PyMySQL, and Python Peewee.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all Python tutorials.