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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
| psycopg2 | psycopg 3 |
|---|---|
import psycopg2 | import psycopg |
with conn: ends the transaction, connection stays open | with conn: ends the transaction and closes the connection |
with conn: for a transaction | with conn.transaction():, nestable with savepoints |
| parameters merged on the client | server-side binding; ClientCursor for the old behaviour |
IN %s with a tuple | = ANY(%s) with a list |
execute_values, execute_batch | executemany, or COPY with write_row |
DictCursor, RealDictCursor | row_factory=dict_row |
NamedTupleCursor | row_factory=namedtuple_row |
Json(...) from psycopg2.extras | Json, Jsonb from psycopg.types.json |
bytea returned as memoryview | bytea returned as bytes |
copy_from, copy_to, copy_expert | cursor.copy context manager |
e.pgcode | e.sqlstate |
conn.closed is 0 or 1 | conn.closed is False or True |
psycopg2.pool | the psycopg-pool package |
select + poll for notifications | conn.notifies() generator |
| no asyncio support | AsyncConnection, AsyncConnectionPool |
Source
psycopg 3 – Differences from psycopg2
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
List all Python tutorials.