ZetCode

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.

version.py
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.

connection_info.py
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.

connect_env.py
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.

context_managers.py
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.

create_table.py
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.

execute_many.py
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.

execute_values.py
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.

returning_id.py
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.

fetch_all.py
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.

fetch_one.py
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).

fetch_many.py
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.

iterate_cursor.py
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.

dict_cursor.py
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.

named_tuple_cursor.py
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.

parameterized_query.py
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.

query_in.py
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.

sql_injection.py
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.

mogrify.py
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.

sql_compose.py
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.

data_types.py
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.

json_uuid.py
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.

insert_image.py
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.

read_image.py
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.

column_names.py
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.

list_tables.py
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.

copy_to.py
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.

copy_from.py
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.

copy_from_memory.py
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.

create_accounts.py
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.

transfer.py
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.

no_commit.py
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.

autocommit.py
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.

errors.py
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.

error_lookup.py
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.

server_cursor.py
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.

pool.py
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.

listen_notify.py
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

psycopg2 documentation

psycopg2 – Basic module usage

psycopg2 – extras module

psycopg2 – sql module

psycopg2 – errors module

PostgreSQL documentation

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

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

List all Python tutorials.