Python - asyncpg

python/asyncpg-basic

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

On this page

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