Async operations

psycopg3 Connection and Cursor have counterparts AsyncConnection and AsyncCursor supporting an asyncio interface.

The design of the asynchronous objects is pretty much the same of the sync ones: in order to use them you will only have to scatter the await keyword here and there.

async with await psycopg3.AsyncConnection.connect(
        "dbname=test user=postgres") as aconn:
    async with await aconn.cursor() as acur:
        await acur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))
        await acur.execute("SELECT * FROM test")
        await acur.fetchone()
        # will return (1, 100, "abc'def")
        async for record in acur:
            print(record)

with async connections and cursors

As seen in the basic usage, connections and cursors can act as context managers, so you can run:

with psycopg3.connect("dbname=test user=postgres") as conn:
    with conn.cursor() as cur:
        cur.execute(...)
    # the cursor is closed upon leaving the context
# the transaction is committed, the connection closed

For asynchronous connections and cursor it’s almost what you’d expect, but not quite. Please note that connect() and cursor() don’t return a context: they are both factory methods which return an object which can be used as a context. That’s because there are several use cases where it’s useful to handle the object manually and close() them when required.

As a consequence you cannot use async with connect(): you have to do it in two steps instead, as in

aconn = await psycopg3.AsyncConnection.connect():
async with aconn:
    cur = await aconn.cursor()
    async with cur:
        await cur.execute(...)

which can be condensed as:

async with await psycopg3.AsyncConnection.connect() as aconn:
    async with await aconn.cursor() as cur:
        await cur.execute(...)

…but no less than that: you still need to do the double async thing.

Asynchronous notifications

Psycopg allows asynchronous interaction with other database sessions using the facilities offered by PostgreSQL commands LISTEN and NOTIFY. Please refer to the PostgreSQL documentation for examples about how to use this form of communication.

Because of the way sessions interact with notifications (see NOTIFY documentation), you should keep the connection in autocommit mode if you wish to receive or send notifications in a timely manner.

Notifications are received as instances of Notify. If you are reserving a connection only to receive notifications, the simplest way is to consume the Connection.notifies generator. The generator can be stopped using close().

Note

You don’t need an AsyncConnection to handle notifications: a normal blocking Connection is perfectly valid.

The following example will print notifications and stop when one containing the stop message is received.

import psycopg3
conn = psycopg3.connect("", autocommit=True)
conn.cursor().execute("LISTEN mychan")
gen = conn.notifies()
for notify in gen:
    print(notify)
    if notify.payload == "stop":
        gen.close()
print("there, I stopped")

If you run some NOTIFY in a psql session:

=# notify mychan, 'hello';
NOTIFY
=# notify mychan, 'hey';
NOTIFY
=# notify mychan, 'stop';
NOTIFY

You may get output from the Python process such as:

Notify(channel='mychan', payload='hello', pid=961823)
Notify(channel='mychan', payload='hey', pid=961823)
Notify(channel='mychan', payload='stop', pid=961823)
there, I stopped

Alternatively, you can use add_notify_handler() to register a callback function, which will be invoked whenever a notification is received, during the normal query processing; you will be then able to use the connection normally. Please note that in this case notifications will not be received immediately, but only during a connection operation, such as a query.

conn.add_notify_handler(lambda n: print(f"got this: {n}"))

# meanwhile in psql...
# =# notify mychan, 'hey';
# NOTIFY

print(conn.cursor().execute("select 1").fetchone())
# got this: Notify(channel='mychan', payload='hey', pid=961823)
# (1,)