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 `psycopg3`¶

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

import psycopg3

# Connect to an existing database
with psycopg3.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:

The function connect() creates a new database session and returns a new Connection instance. AsyncConnection.connect() creates an asyncio connection instead.
The Connection class encapsulates a database session. It allows to:
- create new Cursor instances using the cursor() method to execute database commands and queries,
- terminate transactions using the methods commit() or rollback().
The class Cursor allows interaction with the database:
- send commands to the database using methods such as execute() and executemany(),
- retrieve data from the database, iterating on the cursor or using methods such as fetchone(), fetchmany(), fetchall().
Using these objects as context managers (i.e. using with) will make sure to close them and free their resources at the end of the block (notice that this is different from psycopg2).

Basic module usage¶

Main objects in psycopg3¶

Main objects in `psycopg3`¶