Python - asyncpg

  • python/asyncpg-basic

    • 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

    Terminal window
    uv init asyncpg-basic
    cd asyncpg-basic
    uv add asyncpg

    Create and start a PostgreSQL instance as explained in Postgres - Basic.

    Then create a database named test:

    Terminal window
    create database test;

    Basic Usage

    test_database.py

    Create test_database.py:

    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:

    Terminal window
    <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