Python PostgreSQL
last modified November 17, 2026 Python 3.15
In this article we show how to work with PostgreSQL databases in Python using the psycopg2 module.
psycopg2 is the most widely used PostgreSQL database adapter for
Python. It implements the Python Database API Specification v2.0 (PEP 249)
and adds many PostgreSQL-specific extensions, such as server-side cursors,
the COPY command, asynchronous notifications, and conversion of
arrays, JSON, and other PostgreSQL types. The module is mostly written in C
as a wrapper around libpq, the official PostgreSQL client
library.
psycopg2 is mature and stable; new features are added to its successor,
psycopg 3 (the psycopg package), which has a very
similar API and adds native asyncio support. Most of what we learn here
applies to both; the differences, such as the meaning of
with conn:, are covered in the
Python psycopg 3 article.
The examples were tested with Python 3.15, psycopg2 2.9.13, and PostgreSQL 18 on Linux.
PostgreSQL
PostgreSQL is a powerful, open source object-relational database system. It is a multi-user database management system that runs on Linux, BSD systems, macOS, Windows, and other platforms. PostgreSQL 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 psycopg2
The psycopg2-binary package ships with precompiled wheels that
include libpq, so no compiler or PostgreSQL development files
are needed. The psycopg2 package is the same module built from
source; it needs pg_config and the libpq headers,
and the psycopg authors recommend it for production deployments. Both are
imported as psycopg2.
$ pip install psycopg2-binary
PostgreSQL version
In the first example, we get the version of the PostgreSQL server.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
try:
cur = con.cursor()
cur.execute('SELECT version()')
version = cur.fetchone()[0]
print(version)
finally:
con.close()
We connect to the testdb database, execute a query and print
the result.
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
The connect function creates a new database session and
returns a connection object. The parameters are passed to
libpq; any parameter we omit gets its default value, for
example port 5432.
cur = con.cursor()
cur.execute('SELECT version()')
From the connection, we get a cursor object. The cursor sends SQL
statements to the database and gives access to their results. The
execute method runs the statement.
version = cur.fetchone()[0]
The query returns one row with one column. The fetchone
method returns the row as a tuple; we take its first element.
finally:
con.close()
The connection is closed in the finally clause, so it is
released even if the query fails.
$ 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.
import psycopg2
con = psycopg2.connect('dbname=testdb user=postgres password=s$cret host=localhost')
with con:
print(con.dsn)
print(con.server_version)
print(con.info.dbname, con.info.user, con.info.host)
print(con.encoding)
with con.cursor() as cur:
cur.execute('SHOW server_version')
print(cur.fetchone()[0])
con.close()
The program connects with a connection string and prints some information about the connection.
print(con.dsn)
The dsn attribute contains the parameters of the connection.
The password is masked.
print(con.server_version) print(con.info.dbname, con.info.user, con.info.host)
The server_version attribute is an integer; 180006 stands for
version 18.6. The info attribute is a
ConnectionInfo object with details about the connection.
with con.cursor() as cur:
cur.execute('SHOW server_version')
The SHOW command returns the value of a server setting.
$ python connection_info.py user=postgres password=xxx dbname=testdb host=localhost 180006 testdb postgres localhost UTF8 18.6 (Debian 18.6-1.pgdg13+2)
Environment variables
Hard-coding passwords is a bad practice. Because psycopg2 uses
libpq, it honours the standard PostgreSQL environment variables
PGHOST, PGPORT, PGDATABASE,
PGUSER, and PGPASSWORD, and it reads passwords
from the ~/.pgpass file.
import psycopg2
con = psycopg2.connect('')
with con, con.cursor() as cur:
cur.execute('SELECT current_user, current_database()')
user, db = cur.fetchone()
print(f'connected to {db} as {user}')
con.close()
We call connect with an empty connection string; 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 connected to testdb as postgres
Context managers
Connections and cursors can be used in a with statement. A
cursor is closed when its with block ends. A connection is
different: its with block wraps a transaction. When the block
ends normally, the transaction is committed; when an exception is raised,
the transaction is rolled back. The connection stays open.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con:
with con.cursor() as cur:
cur.execute('SELECT 1 + 1')
print(cur.fetchone())
print('cursor closed:', cur.closed)
print('connection closed:', con.closed)
con.close()
print('connection closed:', con.closed)
The example shows the difference between the two context managers.
print('connection closed:', con.closed)
The closed attribute of a connection is 0 while the connection
is open. After the with con: block it is still 0; we have to
call close ourselves.
$ python context_managers.py (2,) cursor closed: True connection closed: 0 connection closed: 1
Creating a table
The execute method runs any SQL statement, including DDL
statements like CREATE TABLE.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.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.execute("INSERT INTO cars(name, price) VALUES('Audi', 52642)")
cur.execute("INSERT INTO cars(name, price) VALUES('Mercedes', 57127)")
cur.execute("INSERT INTO cars(name, price) VALUES('Skoda', 9000)")
cur.execute("INSERT INTO cars(name, price) VALUES('Volvo', 29000)")
cur.execute("INSERT INTO cars(name, price) VALUES('Bentley', 350000)")
cur.execute("INSERT INTO cars(name, price) VALUES('Citroen', 21000)")
cur.execute("INSERT INTO cars(name, price) VALUES('Hummer', 41400)")
cur.execute("INSERT INTO cars(name, price) VALUES('Volkswagen', 21600)")
con.close()
The program creates the cars table and inserts eight rows into
it. Thanks to the with con block, the transaction is committed
at the end.
with con, con.cursor() as cur:
Both context managers can be combined in one with statement.
cur.execute('''CREATE TABLE cars(id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL, price INT)''')
The table has three columns. The SERIAL type creates an
auto-incremented integer column.
We verify the data with the psql tool running inside the
container.
$ python create_table.py $ docker exec pg psql -U postgres 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 against all
parameter tuples of a sequence. It is useful for commands that modify the
database; result sets are discarded.
import psycopg2
cars = [
('Audi', 52642),
('Mercedes', 57127),
('Skoda', 9000),
('Volvo', 29000),
('Bentley', 350000),
('Citroen', 21000),
('Hummer', 41400),
('Volkswagen', 21600),
]
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('TRUNCATE cars RESTART IDENTITY')
query = 'INSERT INTO cars(name, price) VALUES(%s, %s)'
cur.executemany(query, cars)
print(f'{cur.rowcount} rows inserted')
con.close()
We empty the cars table and insert the rows again with a
single executemany call.
cur.execute('TRUNCATE cars RESTART IDENTITY')
The TRUNCATE statement removes all rows;
RESTART IDENTITY resets the sequence of the id
column, so the new rows get ids from 1 again.
query = 'INSERT INTO cars(name, price) VALUES(%s, %s)' cur.executemany(query, cars)
The first argument is a parameterized SQL statement; the second is a list of
tuples with the values. The %s placeholders are filled with
the values of each tuple.
$ python execute_many.py 8 rows inserted
The execute_values function
In psycopg2, executemany runs the statement once per row, which
means one network round trip per row. The execute_values
function from the psycopg2.extras module builds a single
INSERT with a multi-row VALUES list, which is much
faster for large data sets.
import psycopg2
from psycopg2.extras import execute_values
words = ['sky', 'forest', 'cloud', 'valley', 'rock', 'storm', 'falcon']
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS words')
cur.execute('CREATE TABLE words(id SERIAL PRIMARY KEY, word TEXT)')
query = 'INSERT INTO words(word) VALUES %s RETURNING id, word'
rows = execute_values(cur, query, [(w,) for w in words], fetch=True)
print(cur.query.decode()[:72], '...')
for row in rows:
print(row)
con.close()
We insert seven words into a new words table and get the
generated ids back.
query = 'INSERT INTO words(word) VALUES %s RETURNING id, word' rows = execute_values(cur, query, [(w,) for w in words], fetch=True)
The query has a single %s placeholder, which is replaced with
the list of rows. With fetch=True, the function returns the
rows produced by the RETURNING clause. Big inputs are split
into pages of page_size rows (100 by default).
print(cur.query.decode()[:72], '...')
The query attribute holds the last statement sent to the
server, as bytes.
$ python execute_values.py
INSERT INTO words(word) VALUES ('sky'),('forest'),('cloud'),('valley'),( ...
(1, 'sky')
(2, 'forest')
(3, 'cloud')
(4, 'valley')
(5, 'rock')
(6, 'storm')
(7, 'falcon')
Last inserted row id
The lastrowid attribute of a cursor is not useful with
PostgreSQL. To get the id of a newly inserted row, we use the
RETURNING clause.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('INSERT INTO cars(name, price) VALUES(%s, %s) RETURNING id',
('Toyota', 32500))
car_id = cur.fetchone()[0]
print(f'new car id: {car_id}')
print(f'lastrowid: {cur.lastrowid}')
cur.execute('DELETE FROM cars WHERE id = %s', (car_id,))
con.close()
We insert a new car, print its id, and delete it again.
cur.execute('INSERT INTO cars(name, price) VALUES(%s, %s) RETURNING id',
('Toyota', 32500))
car_id = cur.fetchone()[0]
The RETURNING id clause makes the INSERT statement
return a row with the new id, which we fetch like a query result.
print(f'lastrowid: {cur.lastrowid}')
The lastrowid attribute holds the OID of the inserted row.
Tables have no OIDs since PostgreSQL 12, so it is 0.
$ python returning_id.py new car id: 9 lastrowid: 0
The fetchall method
The fetchall method fetches all the remaining rows of a query
result and returns them as a list of tuples. An empty list is returned when
there are no rows.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT id, name, price FROM cars ORDER BY id')
rows = cur.fetchall()
print(type(rows), len(rows))
for car_id, name, price in rows:
print(f'{car_id} {name} {price}')
con.close()
We retrieve all rows from the cars table.
for car_id, name, price in rows:
print(f'{car_id} {name} {price}')
Each row is a tuple, so we can unpack it in the for loop.
$ python fetch_all.py <class 'list'> 8 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
The fetchone method
The fetchone method returns the next row of a result set, or
None when no more data is available.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT id, name, price FROM cars WHERE price > 40000 ORDER BY price')
while (row := cur.fetchone()) is not None:
print(row)
print(cur.fetchone())
con.close()
We fetch the cars that cost more than 40000 one by one.
while (row := cur.fetchone()) is not None:
print(row)
The walrus operator assigns the next row and the loop ends when
fetchone returns None.
print(cur.fetchone())
Fetching from an exhausted result set is not an error; we simply get
None.
$ python fetch_one.py (7, 'Hummer', 41400) (1, 'Audi', 52642) (2, 'Mercedes', 57127) (5, 'Bentley', 350000) None
The fetchmany method
The fetchmany method returns the next set of rows as a list.
The size of the set is given by its argument, or by the
arraysize attribute of the cursor (1 by default).
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT name FROM cars ORDER BY name')
while batch := cur.fetchmany(3):
print([name for (name,) in batch])
con.close()
We fetch the car names in batches of three. The last batch is shorter and the following call returns an empty list, which ends the loop.
print([name for (name,) in batch])
Each row is a one-element tuple; the (name,) target unpacks
it.
$ python fetch_many.py ['Audi', 'Bentley', 'Citroen'] ['Hummer', 'Mercedes', 'Skoda'] ['Volkswagen', 'Volvo']
Iterating over a cursor
A cursor is iterable. Looping over it is the most convenient way to process a result set row by row.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT name, price FROM cars ORDER BY price DESC LIMIT 4')
print(f'{cur.rowcount} rows')
for name, price in cur:
print(f'{name:<10} {price:>7,}')
con.close()
We print the four most expensive cars.
print(f'{cur.rowcount} rows')
After a SELECT, the rowcount attribute holds the
number of rows in the result. For INSERT,
UPDATE, and DELETE, it is the number of affected
rows.
$ python iterate_cursor.py 4 rows Bentley 350,000 Mercedes 57,127 Audi 52,642 Hummer 41,400
Dictionary cursors
The default cursor returns rows as tuples. The
psycopg2.extras module provides cursors that return rows which
can be accessed by column names. A DictCursor returns rows that
support both indexes and names; a RealDictCursor returns real
dictionaries.
import psycopg2
from psycopg2.extras import DictCursor, RealDictCursor
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con:
with con.cursor(cursor_factory=DictCursor) as cur:
cur.execute('SELECT id, name, price FROM cars WHERE id = 1')
row = cur.fetchone()
print(row)
print(row['name'], row[2])
with con.cursor(cursor_factory=RealDictCursor) as cur:
cur.execute('SELECT id, name, price FROM cars WHERE id <= 3 ORDER BY id')
for row in cur:
print(row)
con.close()
We use both cursor classes to print data of the cars table.
with con.cursor(cursor_factory=DictCursor) as cur:
The cursor_factory parameter selects the cursor class.
print(row['name'], row[2])
A DictRow is a list subclass, which also allows access by
column name.
for row in cur:
print(row)
A RealDictRow is a dictionary subclass. Column names are folded
to lowercase in PostgreSQL unless they are quoted, so the keys are
lowercase.
$ python dict_cursor.py
[1, 'Audi', 52642]
Audi 52642
RealDictRow({'id': 1, 'name': 'Audi', 'price': 52642})
RealDictRow({'id': 2, 'name': 'Mercedes', 'price': 57127})
RealDictRow({'id': 3, 'name': 'Skoda', 'price': 9000})
Named tuple cursor
The NamedTupleCursor returns rows as named tuples, so the
columns are available as attributes.
import psycopg2
from psycopg2.extras import NamedTupleCursor
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost',
cursor_factory=NamedTupleCursor)
with con, con.cursor() as cur:
cur.execute('SELECT name, price FROM cars WHERE price < 25000 ORDER BY price')
for car in cur:
print(car)
print(car.name, car.price)
con.close()
This time the cursor factory is set for the whole connection; every cursor
created by the connection is a NamedTupleCursor.
print(car.name, car.price)
We access the fields of the last row as attributes.
$ python named_tuple_cursor.py Record(name='Skoda', price=9000) Record(name='Citroen', price=21000) Record(name='Volkswagen', price=21600) Volkswagen 21600
Parameterized queries
When we use parameterized queries, we use placeholders instead of directly writing the values into the statements. psycopg2 then converts the Python values to SQL literals and quotes them properly. Parameterized queries protect against SQL injection attacks.
psycopg2 supports two placeholder styles: positional %s
placeholders with a sequence of values, and named
%(name)s placeholders with a mapping. The placeholder is always
%s, whatever the type of the value.
import psycopg2
car_id = 1
new_price = 62300
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('UPDATE cars SET price = %s WHERE id = %s', (new_price, car_id))
print(f'rows updated: {cur.rowcount}')
cur.execute('SELECT * FROM cars WHERE id = %(id)s', {'id': car_id})
print(cur.fetchone())
con.close()
We update the price of a car and select the changed row.
cur.execute('UPDATE cars SET price = %s WHERE id = %s', (new_price, car_id))
The values are passed in a tuple. The placeholders must not be quoted, even for strings; psycopg2 adds the quotes.
cur.execute('SELECT * FROM cars WHERE id = %(id)s', {'id': car_id})
Named placeholders take their values from a dictionary. A name can be used several times in a query.
$ python parameterized_query.py rows updated: 1 (1, 'Audi', 62300)
Lists, IN, and LIKE
A Python tuple is converted to a parenthesized list of values, suitable for
the IN operator. A Python list is converted to a PostgreSQL
array, which is used with ANY.
import psycopg2
names = ['Audi', 'Skoda', 'Volvo']
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT name, price FROM cars WHERE name IN %s ORDER BY id',
(tuple(names),))
print(cur.fetchall())
cur.execute('SELECT name, price FROM cars WHERE name = ANY(%s) ORDER BY id',
(names,))
print(cur.fetchall())
cur.execute("SELECT name FROM cars WHERE name LIKE %s", ('%en%',))
print(cur.fetchall())
cur.execute("SELECT '100%%', %s", ('ok',))
print(cur.fetchone())
con.close()
We select cars by a list of names with IN and with
ANY, and we search with a LIKE pattern.
cur.execute('SELECT name, price FROM cars WHERE name IN %s ORDER BY id',
(tuple(names),))
The tuple of names is a single parameter, so it is wrapped in another
tuple. An empty tuple would produce invalid SQL; the ANY
variant works with empty lists as well.
cur.execute("SELECT name FROM cars WHERE name LIKE %s", ('%en%',))
The % wildcards belong to the value, not to the query, so they
need no escaping.
cur.execute("SELECT '100%%', %s", ('ok',))
A literal % in a query with parameters must be doubled.
$ python query_in.py
[('Audi', 62300), ('Skoda', 9000), ('Volvo', 29000)]
[('Audi', 62300), ('Skoda', 9000), ('Volvo', 29000)]
[('Bentley',), ('Citroen',), ('Volkswagen',)]
('100%', 'ok')
SQL injection
Building queries with string formatting is dangerous: a crafted input changes the meaning of the statement.
import psycopg2
user_input = "Audi' OR '1'='1"
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute(f"SELECT count(*) FROM cars WHERE name = '{user_input}'")
print('string formatting:', cur.fetchone()[0])
cur.execute('SELECT count(*) FROM cars WHERE name = %s', (user_input,))
print('query parameter:', cur.fetchone()[0])
print(cur.query.decode())
con.close()
We run the same query with an f-string and with a query parameter.
cur.execute(f"SELECT count(*) FROM cars WHERE name = '{user_input}'")
The input closes the string literal and appends an
OR '1'='1' condition, which is always true. The query matches
all rows.
cur.execute('SELECT count(*) FROM cars WHERE name = %s', (user_input,))
With a parameter, psycopg2 doubles the single quotes inside the value, and the whole input is compared as one string. No car has such a name.
$ python sql_injection.py string formatting: 8 query parameter: 0 SELECT count(*) FROM cars WHERE name = 'Audi'' OR ''1''=''1'
The mogrify method
The mogrify method is a psycopg2 extension of the DB API. It
returns the query string after binding the arguments, exactly as it would
be sent to the server by execute. It is useful for debugging.
import datetime
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
print(cur.mogrify('SELECT name, price FROM cars WHERE id = %s', (2,)))
print(cur.mogrify('SELECT %s, %s, %s, %s',
("O'Neil", None, True, [1, 2, 3])))
print(cur.mogrify('SELECT %s', (datetime.date(2026, 11, 17),)).decode())
con.close()
We show how various Python values are converted to SQL literals.
print(cur.mogrify('SELECT %s, %s, %s, %s',
("O'Neil", None, True, [1, 2, 3])))
A string is quoted and its apostrophe escaped, None becomes
NULL, and a list becomes an ARRAY. The method
returns bytes; we can decode them to a string.
$ python mogrify.py b'SELECT name, price FROM cars WHERE id = 2' b"SELECT 'O''Neil', NULL, true, ARRAY[1,2,3]" SELECT '2026-11-17'::date
Dynamic SQL with the sql module
Query parameters can only stand for values. Table and column names cannot
be passed as parameters, and formatting them into the query with Python
string methods is unsafe. The psycopg2.sql module composes
queries from parts that are quoted correctly.
import psycopg2
from psycopg2 import sql
table = 'cars'
columns = ['name', 'price']
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
query = sql.SQL('SELECT {fields} FROM {table} WHERE {key} > %s '
'ORDER BY {key} LIMIT 3').format(
fields=sql.SQL(', ').join(map(sql.Identifier, columns)),
table=sql.Identifier(table),
key=sql.Identifier('price'),
)
print(query.as_string(con))
cur.execute(query, (30000,))
print(cur.fetchall())
con.close()
We build a query where the table and the column names come from variables.
query = sql.SQL('SELECT {fields} FROM {table} WHERE {key} > %s '
'ORDER BY {key} LIMIT 3').format(
sql.SQL holds a query template with {}
placeholders for the composable parts. The %s placeholder
remains for an ordinary query parameter.
fields=sql.SQL(', ').join(map(sql.Identifier, columns)),
table=sql.Identifier(table),
sql.Identifier quotes a name with double quotes; the
join method joins several parts with a separator. For values,
there is also sql.Literal.
print(query.as_string(con))
The as_string method shows the composed query. A composed
query is passed to execute like a string.
$ python sql_compose.py
SELECT "name", "price" FROM "cars" WHERE "price" > %s ORDER BY "price" LIMIT 3
[('Hummer', 41400), ('Mercedes', 57127), ('Audi', 62300)]
Type adaptation
psycopg2 converts Python objects to SQL values and PostgreSQL values back to
Python objects. Numbers, strings, booleans, None, dates and
times, intervals, Decimal, lists, and tuples are supported out
of the box.
import datetime
from decimal import Decimal
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('''SELECT 42, 3.14::float8, 19.99::numeric, true, NULL,
'text', DATE '2026-11-17', TIMESTAMPTZ '2026-11-17 10:30:00+01',
INTERVAL '1 day 2 hours', ARRAY[1, 2, 3]''')
for value in cur.fetchone():
print(f'{type(value).__name__:<10} {value!r}')
cur.execute('SELECT %s + %s, %s::date + 30',
(Decimal('0.10'), Decimal('0.20'), datetime.date(2026, 11, 17)))
print(cur.fetchone())
con.close()
We select values of various PostgreSQL types and print their Python types.
for value in cur.fetchone():
print(f'{type(value).__name__:<10} {value!r}')
A numeric value becomes a Decimal, a
timestamptz an aware datetime, an
interval a timedelta, and an array a list. The
time zone of the datetime is the session time zone, which is UTC
on our server.
cur.execute('SELECT %s + %s, %s::date + 30',
(Decimal('0.10'), Decimal('0.20'), datetime.date(2026, 11, 17)))
Python values are passed in the other direction. The decimal sum is exact, unlike with floats.
$ python data_types.py
int 42
float 3.14
Decimal Decimal('19.99')
bool True
NoneType None
str 'text'
date datetime.date(2026, 11, 17)
datetime datetime.datetime(2026, 11, 17, 9, 30, tzinfo=datetime.timezone.utc)
timedelta datetime.timedelta(days=1, seconds=7200)
list [1, 2, 3]
(Decimal('0.30'), datetime.date(2026, 12, 17))
JSON, arrays, and UUID
PostgreSQL has rich types such as jsonb, arrays, and
uuid. json and jsonb values are
converted to Python objects when read. When writing, we wrap a dictionary
in the Json adapter. UUID support has to be registered with
register_uuid.
import uuid
import psycopg2
from psycopg2.extras import Json, register_uuid
register_uuid()
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS events')
cur.execute('''CREATE TABLE events(id UUID PRIMARY KEY,
tags TEXT[], payload JSONB)''')
event_id = uuid.UUID('9f1c2d4e-5b6a-4c3d-8e7f-0a1b2c3d4e5f')
payload = {'user': 'jane', 'action': 'login', 'attempts': 2}
cur.execute('INSERT INTO events VALUES(%s, %s, %s)',
(event_id, ['auth', 'web'], Json(payload)))
cur.execute("SELECT id, tags, payload, payload->>'user' FROM events")
eid, tags, data, user = cur.fetchone()
print(repr(eid))
print(tags, type(tags).__name__)
print(data, type(data).__name__)
print(user)
con.close()
We store an event with a UUID key, an array of tags, and a JSON payload.
register_uuid()
The function registers adapters for the uuid.UUID type in both
directions.
cur.execute('INSERT INTO events VALUES(%s, %s, %s)',
(event_id, ['auth', 'web'], Json(payload)))
The list is converted to a TEXT[] array; the
Json adapter serializes the dictionary with the
json module.
cur.execute("SELECT id, tags, payload, payload->>'user' FROM events")
The ->> operator extracts a JSON field as text on the
server side.
$ python json_uuid.py
UUID('9f1c2d4e-5b6a-4c3d-8e7f-0a1b2c3d4e5f')
['auth', 'web'] list
{'user': 'jane', 'action': 'login', 'attempts': 2} dict
jane
Inserting an image
Binary data is stored in a bytea column. Large files are
usually better kept in a file system or object storage, with only their
paths in the database; small images and documents are fine in a table.
import psycopg2
from pathlib import Path
data = Path('mountains.jpg').read_bytes()
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS images')
cur.execute('CREATE TABLE images(id SERIAL PRIMARY KEY, name TEXT, data BYTEA)')
cur.execute('INSERT INTO images(name, data) VALUES(%s, %s) RETURNING id',
('mountains.jpg', psycopg2.Binary(data)))
print(f'image {cur.fetchone()[0]} saved, {len(data)} bytes')
con.close()
We read a JPEG image from the current working directory and insert it
into the images table.
data = Path('mountains.jpg').read_bytes()
We read the binary data of the file.
cur.execute('INSERT INTO images(name, data) VALUES(%s, %s) RETURNING id',
('mountains.jpg', psycopg2.Binary(data)))
The Binary wrapper adapts the data to a bytea
literal. Plain bytes objects are adapted the same way; the
wrapper makes the intent explicit.
$ python insert_image.py image 1 saved, 23917 bytes
Reading an image
Now we perform the reverse operation: we read the image from the table and write it to a new file.
import hashlib
import psycopg2
from pathlib import Path
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT name, data FROM images WHERE id = %s', (1,))
name, data = cur.fetchone()
print(type(data))
Path('copy.jpg').write_bytes(data)
for fname in (name, 'copy.jpg'):
digest = hashlib.sha256(Path(fname).read_bytes()).hexdigest()
print(f'{fname:<13} {digest[:16]}')
con.close()
We save the image as copy.jpg and compare the SHA-256 hashes
of both files.
cur.execute('SELECT name, data FROM images WHERE id = %s', (1,))
name, data = cur.fetchone()
A bytea value is returned as a memoryview.
Path('copy.jpg').write_bytes(data)
The memory view can be written to a file directly; we can also call
bytes(data) to get a bytes object.
$ python read_image.py <class 'memoryview'> mountains.jpg dcdcb94c540268ad copy.jpg dcdcb94c540268ad
Column metadata
Metadata is information about the data in the database. After a query, the
description attribute of the cursor describes the columns of
the result set.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('SELECT * FROM cars ORDER BY id LIMIT 4')
for col in cur.description:
print(col.name, col.type_code)
names = [col.name for col in cur.description]
print(f'{names[0]:>3} {names[1]:<10} {names[2]:>6}')
for car_id, name, price in cur:
print(f'{car_id:>3} {name:<10} {price:>6}')
con.close()
We print the column names and types, followed by a small table of cars with a header.
for col in cur.description:
print(col.name, col.type_code)
Each item of description is a Column object. The
type_code is the OID of the PostgreSQL type: 23 is
int4 and 1043 is varchar.
$ python column_names.py id 23 name 1043 price 23 id name price 1 Audi 62300 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000
Listing tables and columns
The information_schema views contain the metadata of the
database objects.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('''SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' ORDER BY table_name''')
print([name for (name,) in cur])
cur.execute('''SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = %s ORDER BY ordinal_position''', ('cars',))
for column in cur:
print(column)
con.close()
We list the tables in the public schema and the columns of
the cars table.
cur.execute('''SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = %s ORDER BY ordinal_position''', ('cars',))
The columns view has a row for each column of each table.
$ python list_tables.py
['cars', 'events', 'images', 'words']
('id', 'integer', 'NO')
('name', 'character varying', 'NO')
('price', 'integer', 'YES')
Exporting data with COPY
The COPY command is the fastest way to move data in and out of
PostgreSQL. The copy_expert method runs a COPY ... TO
STDOUT or COPY ... FROM STDIN command and connects it to
a Python file object.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur, open('cars.csv', 'w') as f:
cur.copy_expert('COPY (SELECT * FROM cars ORDER BY id) TO STDOUT '
'WITH (FORMAT csv, HEADER)', f)
print(f'{cur.rowcount} rows exported')
con.close()
We export the cars table to a CSV file with a header line.
cur.copy_expert('COPY (SELECT * FROM cars ORDER BY id) TO STDOUT '
'WITH (FORMAT csv, HEADER)', f)
COPY can export a query, not only a whole table; here we use
it to sort the rows. The data is written to the opened file.
$ python copy_to.py 8 rows exported $ cat cars.csv id,name,price 1,Audi,62300 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
In the reverse operation, we load the CSV file into a new table.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur, open('cars.csv') as f:
cur.execute('DROP TABLE IF EXISTS cars_copy')
cur.execute('CREATE TABLE cars_copy (LIKE cars)')
cur.copy_expert('COPY cars_copy FROM STDIN WITH (FORMAT csv, HEADER)', f)
print(f'{cur.rowcount} rows imported')
cur.execute('SELECT * FROM cars_copy WHERE price > 50000 ORDER BY id')
print(cur.fetchall())
con.close()
We create the cars_copy table with the structure of
cars and fill it from the file.
cur.execute('CREATE TABLE cars_copy (LIKE cars)')
The LIKE clause copies the column definitions of another
table.
cur.copy_expert('COPY cars_copy FROM STDIN WITH (FORMAT csv, HEADER)', f)
The HEADER option skips the first line of the file.
$ python copy_from.py 8 rows imported [(1, 'Audi', 62300), (2, 'Mercedes', 57127), (5, 'Bentley', 350000)]
COPY from memory
The copy_from method loads data in the default text format of
COPY: columns separated with tabs and rows with newlines. The
source can be any object with read and
readline methods, such as io.StringIO.
import io
import psycopg2
measurements = [(1, 21.5), (2, 22.1), (3, 19.8), (4, 20.4)]
buf = io.StringIO()
for sensor, value in measurements:
buf.write(f'{sensor}\t{value}\n')
buf.seek(0)
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS readings')
cur.execute('CREATE TABLE readings(sensor INT, value REAL)')
cur.copy_from(buf, 'readings', columns=('sensor', 'value'))
cur.execute('SELECT count(*), avg(value)::numeric(4, 2) FROM readings')
print(cur.fetchone())
con.close()
We bulk-load sensor measurements generated in the program, without a temporary file.
cur.copy_from(buf, 'readings', columns=('sensor', 'value'))
The method takes the file object, the table name, and optionally the
columns. Since psycopg2 2.9, the table name is quoted as an identifier, so
it cannot contain a schema; for schema.table, use
copy_expert.
$ python copy_from_memory.py
(4, Decimal('20.95'))
Transactions
A transaction is an atomic unit of database operations. The effects of all the SQL statements in a transaction are either all committed to the database or all rolled back.
In psycopg2, the first statement executed on a connection starts a
transaction implicitly; we do not write BEGIN. The transaction
ends with the commit or rollback method of the
connection, or with the end of a with con block. If an error
occurs, the transaction is aborted and no further statements are accepted
until it is rolled back.
For the following example, we create an accounts table. Its
CHECK constraint forbids negative balances.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con, con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS accounts')
cur.execute('''CREATE TABLE accounts(id INT PRIMARY KEY, owner TEXT,
balance NUMERIC(10, 2) CHECK (balance >= 0))''')
cur.execute('''INSERT INTO accounts VALUES
(1, 'Alice', 100.00), (2, 'Bob', 50.00)''')
con.close()
The table has two accounts. Alice has 100 and Bob 50.
Rolling back a transaction
A money transfer consists of two updates that must succeed or fail together.
import sys
import psycopg2
def transfer(con, src, dst, amount):
try:
with con, con.cursor() as cur:
cur.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s',
(amount, dst))
cur.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s',
(amount, src))
print(f'transferred {amount}')
except psycopg2.errors.CheckViolation as e:
print(f'transfer of {amount} failed: {e.diag.message_primary}')
def show(con):
with con.cursor() as cur:
cur.execute('SELECT owner, balance FROM accounts ORDER BY id')
print(cur.fetchall())
con.rollback()
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
transfer(con, 1, 2, int(sys.argv[1]))
show(con)
con.close()
The program transfers the amount given on the command line from Alice to Bob.
with con, con.cursor() as cur:
cur.execute('UPDATE accounts SET balance = balance + %s WHERE id = %s',
(amount, dst))
cur.execute('UPDATE accounts SET balance = balance - %s WHERE id = %s',
(amount, src))
We first credit Bob and then debit Alice. If the second update violates the
check constraint, the exception leaves the with con block and
the transaction is rolled back, including the first update.
except psycopg2.errors.CheckViolation as e:
print(f'transfer of {amount} failed: {e.diag.message_primary}')
Each PostgreSQL error has its own exception class in the
psycopg2.errors module. The diag attribute holds
the details of the error sent by the server.
con.rollback()
The SELECT in show starts a new transaction; we
end it, so the connection does not stay idle in a transaction.
$ 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'))]
Uncommitted changes
Closing a connection with an open transaction discards the changes. Without
commit, nothing is saved.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
cur = con.cursor()
cur.execute('DROP TABLE IF EXISTS friends')
cur.execute('CREATE TABLE friends(id SERIAL PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO friends(name) VALUES('Tom'), ('Rebecca'), ('Jim')")
print('transaction status:', con.info.transaction_status)
con.close()
We create the friends table and insert three rows, but we
never commit.
print('transaction status:', con.info.transaction_status)
The status 2 is TRANSACTION_STATUS_INTRANS from
psycopg2.extensions: the connection is in a transaction block.
When we close the connection, the server rolls the transaction back.
The psql tool shows that the table does not exist.
$ python no_commit.py transaction status: 2 $ docker exec pg psql -U postgres testdb -c "\dt friends" Did not find any tables named "friends".
Autocommit
In the autocommit mode, each statement is committed immediately, as in the
psql tool. Some commands, such as CREATE DATABASE
or VACUUM, cannot run inside a transaction block and need this
mode.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
con.autocommit = True
with con.cursor() as cur:
cur.execute('DROP TABLE IF EXISTS friends')
cur.execute('CREATE TABLE friends(id SERIAL PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO friends(name) VALUES('Jane'), ('Tom'), ('Robert')")
print('transaction status:', con.info.transaction_status)
cur.execute('DROP DATABASE IF EXISTS scratch')
cur.execute('CREATE DATABASE scratch')
print(cur.statusmessage)
con.close()
We create the friends table again and a new
scratch database, without calling commit.
con.autocommit = True
We set the connection to the autocommit mode.
print('transaction status:', con.info.transaction_status)
The status 0 (TRANSACTION_STATUS_IDLE) shows that no
transaction is open.
print(cur.statusmessage)
The statusmessage attribute holds the message returned by the
server for the last command.
$ python autocommit.py transaction status: 0 CREATE DATABASE $ docker exec pg psql -U postgres testdb -c "SELECT * FROM friends" id | name ----+-------- 1 | Jane 2 | Tom 3 | Robert (3 rows)
Handling errors
psycopg2 exceptions follow the DB API hierarchy: Error is the
base class, with subclasses like IntegrityError,
DataError, ProgrammingError, and
OperationalError. The psycopg2.errors module adds
a subclass for each PostgreSQL error code.
import psycopg2
from psycopg2 import errors
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
statements = [
('INSERT INTO accounts VALUES(%s, %s, %s)', (1, 'Carol', 10)),
('INSERT INTO accounts VALUES(%s, %s, %s)', (3, 'Carol', 'ten')),
('SELECT * FROM acounts', None),
('SELECT 1 / %s', (0,)),
]
for query, args in statements:
try:
with con, con.cursor() as cur:
cur.execute(query, args)
except errors.UniqueViolation as e:
print(f'duplicate key: {e.diag.message_detail}')
except psycopg2.Error as e:
print(f'{type(e).__name__} ({e.pgcode}): {e.diag.message_primary}')
con.close()
We run four statements that fail for different reasons.
except errors.UniqueViolation as e:
print(f'duplicate key: {e.diag.message_detail}')
We catch a specific error. The message_detail field tells us
which key is duplicated.
except psycopg2.Error as e:
print(f'{type(e).__name__} ({e.pgcode}): {e.diag.message_primary}')
Other errors are caught by the base class. The pgcode
attribute holds the five-character SQLSTATE code of the error.
$ python errors.py duplicate key: Key (id)=(1) already exists. InvalidTextRepresentation (22P02): invalid input syntax for type numeric: "ten" UndefinedTable (42P01): relation "acounts" does not exist DivisionByZero (22012): division by zero
Error codes
The errorcodes module maps SQLSTATE codes to their names, and
the errors.lookup function returns the exception class for a
code. Connection failures raise OperationalError.
import psycopg2
from psycopg2 import errorcodes, errors
print(errorcodes.lookup('23505'))
print(errors.lookup('23505'))
print(errors.UniqueViolation.__mro__[1:4])
try:
psycopg2.connect(dbname='testdb', user='postgres',
password='wrong', host='localhost')
except psycopg2.OperationalError as e:
print(str(e).strip())
We look up the unique violation code and try to connect with a wrong password.
print(errors.UniqueViolation.__mro__[1:4])
UniqueViolation is a subclass of IntegrityError,
so existing code that catches the DB API classes keeps working.
$ python error_lookup.py UNIQUE_VIOLATION <class 'psycopg2.errors.UniqueViolation'> (<class 'psycopg2.IntegrityError'>, <class 'psycopg2.DatabaseError'>, <class 'psycopg2.Error'>) connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "postgres"
Server-side cursors
A regular cursor transfers the whole result set to the client when the query is executed. For large results, this needs a lot of memory. A server-side cursor, created by giving the cursor a name, keeps the result on the server and transfers it in chunks.
import psycopg2
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
with con:
with con.cursor(name='big_query') as cur:
cur.itersize = 50_000
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:,}')
with con.cursor() as cur:
cur.execute('SELECT count(*) FROM pg_cursors')
print('open server cursors:', cur.fetchone()[0])
con.close()
We process a million rows without loading them into memory at once.
with con.cursor(name='big_query') as cur:
A named cursor creates a DECLARE ... CURSOR on the server. It
must be used inside a transaction, which the with con block
provides.
cur.itersize = 50_000
When we iterate over a named cursor, psycopg2 fetches
itersize rows (2000 by default) in each network round trip.
cur.execute('SELECT count(*) FROM pg_cursors')
The pg_cursors view lists the open cursors of the session.
The server-side cursor was closed at the end of its with
block.
$ python server_cursor.py 1,000,000 rows, sum of squares: 333,333,833,333,500,000 open server cursors: 0
Connection pool
Opening a connection is relatively expensive. A connection pool keeps
connections open and hands them out on demand. The
psycopg2.pool module has SimpleConnectionPool
for single-threaded programs and ThreadedConnectionPool for
multi-threaded ones.
from concurrent.futures import ThreadPoolExecutor
from psycopg2.pool import ThreadedConnectionPool
pool = ThreadedConnectionPool(minconn=2, maxconn=4, dbname='testdb',
user='postgres', password='s$cret',
host='localhost')
def price_of(name):
con = pool.getconn()
try:
with con, con.cursor() as cur:
cur.execute('SELECT price FROM cars WHERE name = %s', (name,))
return name, cur.fetchone()[0]
finally:
pool.putconn(con)
names = ['Audi', 'Skoda', 'Volvo', 'Bentley', 'Hummer', 'Citroen']
with ThreadPoolExecutor(max_workers=4) as executor:
for name, price in executor.map(price_of, names):
print(f'{name:<8} {price:>7}')
pool.closeall()
print('pool closed:', pool.closed)
Four worker threads look up car prices; each borrows a connection from the pool for its query.
pool = ThreadedConnectionPool(minconn=2, maxconn=4, dbname='testdb',
user='postgres', password='s$cret',
host='localhost')
The pool opens minconn connections at once and up to
maxconn on demand. When more than maxconn
connections are requested, getconn raises
PoolError; it does not wait. Connections returned beyond
minconn are closed.
con = pool.getconn()
try:
...
finally:
pool.putconn(con)
A borrowed connection must always be returned with putconn.
$ python pool.py Audi 62300 Skoda 9000 Volvo 29000 Bentley 350000 Hummer 41400 Citroen 21000 pool closed: True
LISTEN and NOTIFY
PostgreSQL can send asynchronous notifications between sessions. A session
subscribes to a channel with LISTEN; another sends a message
with NOTIFY or the pg_notify function. A message
is delivered when the sending transaction commits.
import select
import psycopg2
def connect():
con = psycopg2.connect(dbname='testdb', user='postgres',
password='s$cret', host='localhost')
con.autocommit = True
return con
listener = connect()
sender = connect()
with listener.cursor() as cur:
cur.execute('LISTEN orders')
with sender.cursor() as cur:
cur.execute("SELECT pg_notify('orders', 'order 1001 created')")
cur.execute("NOTIFY orders, 'order 1002 created'")
if select.select([listener], [], [], 5) != ([], [], []):
listener.poll()
while listener.notifies:
note = listener.notifies.pop(0)
print(note.channel, note.payload, note.pid == sender.info.backend_pid)
listener.close()
sender.close()
We open two connections in autocommit mode: one listens on the
orders channel, the other sends two messages.
if select.select([listener], [], [], 5) != ([], [], []):
listener.poll()
A connection has a fileno method, so we can wait for incoming
data with select, here with a timeout of five seconds. The
poll method reads the data and stores the notifications in
the notifies list.
note = listener.notifies.pop(0) print(note.channel, note.payload, note.pid == sender.info.backend_pid)
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
Source
In this article we have worked with PostgreSQL databases in Python using the psycopg2 module. See also Python psycopg 3, Python SQLite, Python PyMySQL, Python Peewee, and Python pyDAL.
Author
List all Python tutorials.