PostgreSQL

Supported drivers:

dbapi	default	driver	connection class
psycopg2		postgresql+psycopg2	psycopg2.extensions.connection
psycopg3		postgresql+psycopg	psycopg.Connection | psycopg2.ConnectionAsync
aiopg		postgresql+aiopg	aiopg.connection.Connection

psycopg2

psycopg2 is the default dbapi driver for PostgreSQL in pydapper.

Installation

pippoetry

pip install pydapper[psycopg2]

poetry add pydapper -E psycopg2

DSN format

TemplateExampleExample (Default Driver)

dsn = f"postgresql+psycopg2://{user}:{password}@{host}:{port}/{dbname}"

dsn = "postgresql+psycopg2://myuser:mypassword@localhost:5432/mydb"

dsn = "postgresql://myuser:mypassword@localhost:5432/mydb"

Example - connect

Please see the psycopg2 docs for a full description of the context manager behavior.

import pydapper

with pydapper.connect("postgresql://pydapper:pydapper@localhost/pydapper") as commands:
    print(type(commands))
    # <class 'pydapper.postgresql.psycopg2.Psycopg2Commands'>

    print(type(commands.connection))
    # <class 'psycopg2.extensions.connection'>

    with commands.cursor() as raw_cursor:
        print(type(raw_cursor))
        # <class 'psycopg2.extensions.cursor'>

Example - using

Use pydapper with a psycopg2 connection pool.

from psycopg2.pool import SimpleConnectionPool

import pydapper

my_pool = SimpleConnectionPool(1, 10, "postgresql://pydapper:pydapper@localhost/pydapper")


commands = pydapper.using(my_pool.getconn())
print(type(commands))
# <class 'pydapper.postgresql.psycopg2.Psycopg2Commands'>

print(type(commands.connection))
# <class 'psycopg2.extensions.connection'>

my_pool.putconn(commands.connection)

Transactions

psycopg2 connects with autocommit off. All of pydapper's transaction APIs (commit(), rollback(), transaction()) are supported. Exiting with pydapper.connect(...) delegates to psycopg2's context manager: it commits on clean exit, rolls back on error, and never closes the connection — close it explicitly:

with connect("postgresql://pydapper:pydapper@localhost/pydapper") as commands:
    commands.execute(insert_sql, params=task)
# the exit committed the insert, but the connection is still open
commands.connection.close()

See Transactions and Context manager semantics for the cross-driver picture.

psycopg3

psycopg3 is special because the driver supports both sync and async apis. Connecting with both is listed below, but note that the difference will be getting an CommandsAsync object instead of a Commands object when connecting in async mode.

Installation

pippoetry

pip install pydapper[psycopg]

poetry add pydapper -E psycopg

DSN format

TemplateExample

dsn = f"postgresql+psycopg://{user}:{password}@{host}:{port}/{dbname}"

dsn = "postgresql+psycopg://myuser:mypassword@localhost:5432/mydb"

Example - connect

Please see the psycopg docs for a full description of the context manager behavior.

import pydapper

with pydapper.connect("postgresql+psycopg://pydapper:pydapper@localhost/pydapper") as commands:
    print(type(commands))
    # <class 'pydapper.postgresql.psycopg3.Psycopg3Commands'>

    print(type(commands.connection))
    # <class 'psycopg.Connection'>

    with commands.cursor() as raw_cursor:
        print(type(raw_cursor))
        # <class 'psycopg.Cursor'>

Example - connect_async

Please see the psycopg docs for a full description of the context manager behavior.

import asyncio

import pydapper


async def main():
    async with pydapper.connect_async("postgresql+psycopg://pydapper:pydapper@localhost/pydapper") as commands:
        print(type(commands))
        # <class 'pydapper.postgresql.psycopg3.Psycopg3CommandsAsync'>

        print(type(commands.connection))
        # <class 'psycopg.AsyncConnection'>

        async with commands.cursor() as raw_cursor:
            print(type(raw_cursor))
            # <class 'psycopg.AsyncCursor'>


asyncio.run(main())

using, using_async and connection pools

Use pydapper with a psycopg connection pool. The package that handles connection pools is distributed separately from the psycopg, and is called psycopg_pool; it supports both sync and async connection pools.

psycopg_pool installation

pippoetry

pip install psycopg_pool

poetry add psycopg_pool

Example using

from psycopg_pool import ConnectionPool

import pydapper

my_pool = ConnectionPool("postgresql://pydapper:pydapper@localhost/pydapper", min_size=1, max_size=10)

commands = pydapper.using(my_pool.getconn())
print(type(commands))
# <class 'pydapper.postgresql.psycopg3.Psycopg3Commands'>

print(type(commands.connection))
# <class 'psycopg.Connection'>

my_pool.putconn(commands.connection)

Example using_async

import asyncio

from psycopg_pool import AsyncConnectionPool

import pydapper


async def main():
    async with AsyncConnectionPool(
        "postgresql://pydapper:pydapper@localhost/pydapper", min_size=1, max_size=10, open=False
    ) as pool:
        conn = await pool.getconn()
        async with pydapper.using_async(conn) as commands:
            print(type(commands))
            # <class 'pydapper.postgresql.psycopg3.Psycopg3CommandsAsync'>

            print(type(commands.connection))
            # <class 'psycopg.AsyncConnection'>

            pool.putconn(commands.connection)


asyncio.run(main())

Transactions

psycopg connects with autocommit off, in both modes. All of pydapper's transaction APIs are supported — sync commit()/rollback()/transaction() on Commands, and their unsuffixed coroutine/async-CM twins on CommandsAsync (await commands.commit(), async with commands.transaction():). psycopg is the only first-party async adapter that supports transactions.

Exiting with pydapper.connect(...) (or async with pydapper.connect_async(...)) delegates to psycopg's connection context manager, which behaves the same in both modes: it rolls back on error, commits on clean exit, and then closes the connection (unless the connection belongs to a pool). Unlike psycopg2, there is nothing left to close after the block.

See Transactions and Context manager semantics for the cross-driver picture.

aiopg

Installation

pippoetry

pip install pydapper[aiopg]

poetry add pydapper -E aiopg

DSN format

TemplateExample

dsn = f"postgresql+aiopg://{user}:{password}@{host}:{port}/{dbname}"

dsn = "postgresql+aiopg://myuser:mypassword@localhost:5432/mydb"

Example - connect_async

Please see the aiopg docs for a full description of the context manager behavior.

import asyncio

import pydapper


async def main():
    async with pydapper.connect_async("postgresql+aiopg://pydapper:pydapper@localhost/pydapper") as commands:
        print(type(commands))
        # <class 'pydapper.postgresql.aiopg.AiopgCommands'>

        print(type(commands.connection))
        # <class 'aiopg.connection.Connection'>

        async with commands.cursor() as raw_cursor:
            print(type(raw_cursor))
            # <class 'aiopg.connection.Cursor'>


asyncio.run(main())

Example - using_async

Use pydapper with a aiopg connection pool.

import asyncio

import aiopg

import pydapper


async def main():
    async with aiopg.create_pool("postgresql://pydapper:pydapper@localhost/pydapper") as pool:
        conn = await pool.acquire()
        async with pydapper.using_async(conn) as commands:
            print(type(commands))
            # <class 'pydapper.postgresql.aiopg.AiopgCommands'>

            print(type(commands.connection))
            # <class 'aiopg.connection.Connection'>


asyncio.run(main())

Transactions

aiopg always runs in autocommit mode — it cannot be disabled, and the client cannot change the isolation level (the server's default, normally READ COMMITTED, applies). Every statement is durable the moment it executes, and there is no connection-level transaction to manage:

• AiopgCommands does not declare AdapterCapability.TRANSACTIONS, so pydapper's commit(), rollback(), and transaction() raise UnsupportedFeatureError before the connection is touched.
• The driver's own connection-level commit()/rollback() raise psycopg2.ProgrammingError ("cannot be used in asynchronous mode").
• Exiting async with pydapper.connect_async(...) closes the connection and nothing else — in autocommit mode there is no pydapper-managed transaction for it to commit or roll back (a transaction you open yourself with an explicit BEGIN is simply discarded by that close).
• aiopg ships its own SQL-emitting Transaction helper (BEGIN/COMMIT through a cursor); pydapper does not use it. If you need real async transactions on PostgreSQL, use the psycopg driver (above) instead.

See Transactions and Context manager semantics for the cross-driver picture.