Psycopg 3.2 released – PostgreSQL driver for Python (psycopg.org)

Everyone seems still on psycopg2. Is there a compelling reason to switch? Is Psycopg3 faster?

I like that $1 etc can now be written instead of %s for placeholders in queries.

Even though the latter was already safe, it simply looked toe-curling (like you were writing naive SQL-injectable queries all the time).

Author here of the PostgreSQL raw queries feature mentioned in the release notes, which allows users to use PostgreSQL's native placeholders ($1, $2, etc.) instead of the standard %s placeholder [1].

    from psycopg import connect, RawCursor
    
    with connect(dsn, cursor_factory=RawCursor) as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT $1, $2", [1, "Hello"])
            assert cur.fetchone() == (1, "Hello")

The release notes highlight several interesting advantages I hadn't considered. However, they don't mention the primary motivation behind my contribution, so I thought I'd share it here:

I was concerned that psycopg replaces the %s placeholders using regexes, which might inadvertently alter my SQL queries. My goal was to ensure that my SQL queries were passed directly to PostgreSQL "as is," without any intermediate modifications. Only PostgreSQL's parser can safely and accurately parse all SQL queries. While this concern might seem theoretical if you're certain your queries don't contain any %s, it still felt much more reassuring to eliminate this potential source of bugs entirely.

[1] https://www.psycopg.org/psycopg3/docs/advanced/cursors.html#...

If you are operating in an environment with asyncio check out asyncpg - https://github.com/MagicStack/asyncpg

Performance charts speak for themselves. This lib uses the binary protocol while most are using text based comms.