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:1521@localhost/mydb"

dsn = "postgresql://myuser:mypassword:1521@localhost/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'>

The `psycopg2` context manager does not close your connection...

You must close it explicitly after exiting the context block:

with connect("postgresql://pydapper:pydapper@localhost/pydapper") as commands:
    # do stuff

# connection is still open, lets close it
commands.connection.close()

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)

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:1521@localhost/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())

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:1521@localhost/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())

Note

aiopg always runs in autocommit mode.

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())