fetch_val / fetch_row should return None on 0 rows (or provide *_or_none variants), to align with asyncpg / psycopg

[coseto6125]

Summary

fetch_val and fetch_row raise ConnectionExecuteError: query returned an unexpected number of rows when the query returns 0 rows. This forces every caller to wrap the call in a try/except and string-match on the error message to distinguish "no row" from real connection / SQL errors. This diverges from the de-facto convention established by asyncpg and psycopg, both of which return None in this case.

Reproduction (psqlpy 0.11.12)

import asyncio
from psqlpy import ConnectionPool

async def main():
    pool = ConnectionPool(dsn="postgresql://...")
    async with pool.acquire() as conn:
        # All three raise ConnectionExecuteError("query returned an unexpected number of rows")
        await conn.fetch_val("SELECT 1 WHERE false")
        await conn.fetch_row("SELECT 1 WHERE false")
        await conn.execute("CREATE TEMP TABLE t (id int)")
        await conn.fetch_val("UPDATE t SET id=1 WHERE id=999 RETURNING id")

asyncio.run(main())

Behavior comparison

Driver	fetch_val (0 rows)	fetch_row (0 rows)
asyncpg (fetchval / fetchrow)	returns None	returns None
psycopg 3 (fetchone after execute)	returns None	returns None
psqlpy 0.11.12	raises ConnectionExecuteError	raises ConnectionExecuteError

Why this matters

A common, legitimate pattern is UPDATE ... RETURNING col where the row may or may not exist:

# psqlpy — what every user ends up writing
try:
    new_id = await conn.fetch_val(
        "UPDATE users SET seen=NOW() WHERE id=$1 RETURNING id", [user_id]
    )
except ConnectionExecuteError as e:
    if "unexpected number of rows" in str(e):  # fragile string match
        new_id = None
    else:
        raise

Issues with the current behavior:

1. No way to distinguish 0-row from real errors without inspecting the message string. ConnectionExecuteError is also raised for connection drops, syntax errors, etc. — string matching is fragile to future message changes.
2. No paired API like fetch_val_or_none / fetch_one_or_none (cf. SQLAlchemy scalar_one_or_none).
3. Migration friction: developers porting from asyncpg / psycopg hit this on day one with no docs warning.
4. Forces try/except boilerplate at every call site, making code harder to read.

Proposed enhancement

Either of the following (in order of preference):

Option A — Return None on 0 rows (matches asyncpg / psycopg)

val = await conn.fetch_val("SELECT 1 WHERE false")  # → None
row = await conn.fetch_row("SELECT 1 WHERE false")  # → None

This is the least surprising behavior for users coming from other drivers. Multi-row results would still raise (the "expected exactly one row" case is the strict one).

Option B — Add paired *_or_none APIs (preserves current strictness)

val = await conn.fetch_val("SELECT 1")              # current strict behavior
val = await conn.fetch_val_or_none("SELECT 1 WHERE false")  # → None
row = await conn.fetch_row("SELECT 1")              # current strict behavior
row = await conn.fetch_row_or_none("SELECT 1 WHERE false")  # → None

This mirrors SQLAlchemy's scalar_one() vs scalar_one_or_none() and lets the caller pick strictness explicitly without a try/except.

Option C — Distinct exception subclass

If the strict behavior is intentional, at minimum subclass the exception so users can catch the specific case:

class NoRowsReturnedError(ConnectionExecuteError): ...
class TooManyRowsReturnedError(ConnectionExecuteError): ...

This makes the except clean without string matching.

Versions

• psqlpy 0.11.12
• PostgreSQL 16
• Python 3.13.9

Related

Discovered while investigating production issues; same investigation also produced #163 (jsonb int64 overflow). Both stem from type / boundary handling differences vs. asyncpg that bite users silently.