Type to search…

asyncpg

asyncpg is a database interface library designed specifically for PostgreSQL and asyncio.

Introduction

asyncpg is an efficient implementation of the PostgreSQL binary protocol for Python’s asyncio. It provides fast queries, native protocol features, and convenient primitives for async I/O.

Setup

ps
uv init asyncpg-basic
cd asyncpg-basic
uv add asyncpg

Create and start a PostgreSQL instance as explained in Basic.

Then create a database named test:

shell
create database test;

Basic Usage

Create test_database.py:

python
import asyncio
import asyncpg
import datetime


async def main():

    # Establish a connection to an existing database named "test"
    conn = await asyncpg.connect("postgresql://postgres:password@localhost/test")

    try:
        # Execute a statement to create a new table.
        await conn.execute(
            """
          create table if not exists users(
            id serial primary key,
            name text unique,
            dob date
        )"""
        )

        # Insert a record into the created table.
        await conn.execute(
            """
            insert into users(name, dob) values($1, $2) on conflict(name) do nothing""",
            "Alice",
            datetime.date(1980, 25, 1),
        )

        # Select a row from the table.
        row = await conn.fetchrow("select * from users where name = $1", "Alice")
        # *row* now contains <Record id=1 name='Alice' dob=datetime.date(1980, 25, 1)>
        print(row)

    finally:
        # Close the connection.
        await conn.close()


asyncio.run(main())

Run the script and you should see:

shell
<Record id=1 name='Alice' dob=datetime.date(1980, 1, 25)>

Notes on parameters and DB-API

  • asyncpg uses PostgreSQL’s native parameter placeholders: $1, $2, …
  • asyncpg does not implement PEP 249 (DB-API 2.0). It favors PostgreSQL’s native parameterization and avoids query rewriting.

Test

Further reading