Basic module usage

The basic Psycopg usage is common to all the database adapters implementing the DB-API protocol. Other database adapters, such as the builtin sqlite3 or psycopg2, have roughly the same pattern of interaction.

Main objects in Psycopg 3

Here is an interactive session showing some of the basic commands:

# Note: the module name is psycopg, not psycopg3
import psycopg

# Connect to an existing database
with psycopg.connect("dbname=test user=postgres") as conn:

    # Open a cursor to perform database operations
    with conn.cursor() as cur:

        # Execute a command: this creates a new table
        cur.execute("""
            CREATE TABLE test (
                id serial PRIMARY KEY,
                num integer,
                data text)
            """)

        # Pass data to fill a query placeholders and let Psycopg perform
        # the correct conversion (no SQL injections!)
        cur.execute(
            "INSERT INTO test (num, data) VALUES (%s, %s)",
            (100, "abc'def"))

        # Query the database and obtain data as Python objects.
        cur.execute("SELECT * FROM test")
        cur.fetchone()
        # will return (1, 100, "abc'def")

        # You can use `cur.fetchmany()`, `cur.fetchall()` to return a list
        # of several records, or even iterate on the cursor
        for record in cur:
            print(record)

        # Make the changes to the database persistent
        conn.commit()

In the example you can see some of the main objects and methods and how they relate to each other:

See also

A few important topics you will have to deal with are:

Shortcuts

The pattern above is familiar to psycopg2 users. However, Psycopg 3 also exposes a few simple extensions which make the above pattern leaner:

  • the Connection objects exposes a execute() method, equivalent to creating a cursor, calling its execute() method, and returning it.

    # This
    cur = conn.execute(...)
    
    # is equivalent to:
    cur = conn.cursor()
    cur.execute(...)
    
  • The Cursor.execute() method returns self. This means that you can chain a fetch operation, such as fetchone(), to the execute() call:

    # This
    cur.execute(...)
    record = cur.fetchone()
    
    cur.execute(...)
    for record in cur:
        ...
    
    # is equivalent to:
    record = cur.execute(...).fetchone()
    for record in cur.execute(...):
        ...
    

Using them together, in simple cases, you can go from creating a connection to using a result in a single expression:

print(psycopg.connect(DSN).execute("SELECT now()").fetchone()[0])
# 2042-07-12 18:15:10.706497+01:00

Connection context

Psycopg 3 Connection can be used as a context manager:

with psycopg.connect() as conn:
    ... # use the connection

# the connection is now closed

When the block is exited, if there is a transaction open, it will be committed. If an exception is raised within the block the transaction is rolled back. In either case the connection is closed.

AsyncConnection can be also used as context manager, using async with, but be careful about its quirkiness: see with async connections for details.