psqlpy Documentation

PSQLPy is composed of several key abstractions for managing PostgreSQL interactions:

• ConnectionPool: Manages a collection of connections and provides them upon request.
• ConnectionPoolBuilder: A chainable builder used to configure and initialize a ConnectionPool step-by-step.
• Connection: Represents a single database connection. You can obtain one from a ConnectionPool or create one directly using the connect method.
• Transaction: Represents a database transaction, which can be initiated from a Connection.
• Cursor: An object used to execute queries and fetch results. Cursors can be created from a Connection, a Transaction, or a PreparedStatement.
• PreparedStatement: Represents a PostgreSQL prepared statement for optimized query execution.
• Listener: An object used to interface with PostgreSQL's LISTEN/NOTIFY functionality. Listeners can be created from a ConnectionPool.
• QueryResult: Represents a collection of results returned from the database.
• SingleQueryResult: Represents a single result row or object returned from the database.
• Exceptions: The library provides custom exception types for handling database-specific errors.

PSQLPy is built around several key abstractions that manage the lifecycle of a database interaction. Understanding these components is essential for managing connections and executing queries:

• Connection pool: The primary entry point and main object in the library. It manages the lifecycle of connections, including initialization, creation, and storage. It must be started before any other operations.
• Connection: Represents a single active database connection, typically retrieved from a Connection pool.
• Transaction: Represents a database transaction, which is created from a Connection.
• Cursor: Represents a database cursor, which is created from a Transaction.
• Results: The data structure representing the data returned from the driver after executing queries.
• Exceptions: Custom exceptions used for error handling (documentation for these is currently in development).

The Listener object provides access to PostgreSQL's LISTEN and NOTIFY functionality. You can interact with notifications using two distinct patterns:

1. Background Task Pattern: Register asynchronous callbacks for specific channels. The listen() method starts a non-blocking background task in the Rust event loop to handle incoming notifications.
2. Async Iterator Pattern: Iterate directly over the Listener object to receive ListenerNotificationMsg objects as they arrive.

To use either pattern, you must first create a Listener from a ConnectionPool and call await listener.startup().

from psqlpy import ConnectionPool, Listener

db_pool = ConnectionPool(dsn="postgres://postgres:postgres@localhost:5432/postgres")

# Pattern 1: Background Task (Callbacks)
async def main_callback():
    listener: Listener = db_pool.listener()
    await listener.startup()
    await listener.add_callback(channel="my_channel", callback=my_callback_func)
    listener.listen()

# Pattern 2: Async Iterator
async def main_iterator():
    listener: Listener = db_pool.listener()
    await listener.startup()
    async for listener_msg in listener:
        print(listener_msg)

A Cursor object represents a server-side cursor in PostgreSQL.

Important Lifecycle Note: Cursors always live inside a transaction. If you do not explicitly begin a transaction, one will be opened automatically to support the cursor.

There are three primary ways to use a cursor:

1. Pre-Initialization: Pass the query and parameters to connection.cursor() and call await cursor.start().
2. Post-Initialization: Create a blank cursor with connection.cursor() and then call await cursor.execute(...) with the query and parameters.
3. Async Context Manager: Use async with connection.cursor(...) as cursor: to handle the lifecycle automatically.

# Async Context Manager pattern
async with connection.cursor(
    querystring="SELECT * FROM users WHERE id > $1",
    parameters=[100],
    array_size=10,
) as cursor:
    result: QueryResult = await cursor.fetchone()