Async PostgreSQL for Python, built in Rust

# Query multiple rows
users = await pool.query("SELECT * FROM users WHERE age > $1", [25])
for user in users:
    print(f"ID: {user['id']}, Name: {user['name']}, Age: {user['age']}")

# Query a single row - raises ProgrammingError on zero matches;
# extra matches beyond the first are silently discarded
user = await pool.query_one("SELECT * FROM users WHERE id = $1", [1])
print(f"Found user: {user['name']}")

# There's no execute_batch() - run each statement individually
for name, age in [("Bob", 25), ("Charlie", 35), ("Diana", 28)]:
    await pool.execute(
        "INSERT INTO users (name, age) VALUES ($1, $2)", [name, age]
    )

if not pool.is_closed():
    print("Pool is open")

# pool.transaction() is a real BEGIN round-trip, so it must be
# awaited before it can be used as a context manager -
# `async with pool.transaction():` raises TypeError.
tx = await pool.transaction()
async with tx:
    await tx.execute("INSERT INTO users (name) VALUES ($1)", ["Alice"])
    await tx.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = $1", [1])
    # Commits automatically on success, rolls back on exception

# Manual transaction management also works outside a `with` block
tx = await pool.transaction()
try:
    await tx.execute("INSERT INTO orders (user_id, amount) VALUES ($1, $2)", [1, 99.99])
    await tx.commit()
except Exception as e:
    await tx.rollback()
    print(f"Transaction failed: {e}")

# Non-primitive types bind natively - no str() + ::type cast needed.
import uuid
from datetime import datetime, timezone
from decimal import Decimal

await pool.execute(
    "INSERT INTO events (at, uid, price, meta, blob) VALUES ($1,$2,$3,$4,$5)",
    [
        datetime.now(timezone.utc),      # -> TIMESTAMPTZ
        uuid.uuid4(),                    # -> UUID
        Decimal("9.99"),                 # -> NUMERIC, exact
        {"ok": True, "tags": ["a", "b"]},# -> JSONB
        b"\x00payload",                  # -> BYTEA
    ],
)

# A homogeneous list/tuple binds as a real Postgres array
await pool.execute(
    "INSERT INTO tags (id, names, scores) VALUES ($1, $2, $3)",
    [1, ["red", "blue"], [10, 20, None]],  # None -> NULL array element
)

# Reading back decodes to the right Python type automatically
row = await pool.query_one("SELECT * FROM events WHERE id = $1", [1])
assert isinstance(row["at"], datetime)