Psycopg 3.3 released

We have released Psycopg 3.3 — and you should be excited about it!

Template string queries

This version lets you take advantage of one of the biggest innovation in Python 3.14: the template strings, which allow you to write expressive and safe queries.

How does it look? Something like:

def fetch_person(conn, name):

    # 'name' will be handled safely: as a server-side parameter or
    # correctly quoted and escaped if client-side binding is required
    cur = conn.execute(t"SELECT * FROM people WHERE name = {name}")
    return cur.fetchone()

The syntax is the same as that of f-strings, introduced back in the venerable Python 3.6 (perhaps the feature that finally ended Python 2?), but now paired with the safety and adaptation flexibility of Psycopg 3. Template strings also help you generate dynamic SQL statements much more succinctly than with the psycopg.sql module:

def delete_something(conn, table_name, name):

    # Mixing client-side query composition with server-side parameters binding
    conn.execute(t"DELETE FROM {table_name:i} WHERE name = {name}")

    # Composing non-parametric statements entirely client-side
    conn.execute(t"NOTIFY {table_name + '.deleted':i}, {name:l}")

Check out the complete t-string support documentation for inspiration!

More flexible composite adaptation

Previously, it was only possible to adapt PostgreSQL composites to Python sequence types with a strict 1:1 mapping to the fields of the database type.

We have now gained extra flexibility: we can customize both how to create generic Python objects, for example ones only taking keyword arguments, and how to extract a sequence of fields from the attributes of non-sequence objects... Dataclasses anyone?

from dataclasses import dataclass
from psycopg.types.composite import CompositeInfo, register_composite

@dataclass
class MiniPerson:
    age: int
    name: str
    height: float | None = None

    @classmethod
    def from_db(cls, seq, info):
        return cls(name=seq[0], age=seq[1])

    def to_db(self, info):
        return [self.name, self.age]

conn.execute("CREATE TYPE mini_person AS (name text, age int)")
info = CompositeInfo.fetch(conn, "mini_person")

register_composite(
    info, conn, factory=MiniPerson,
    make_object=MiniPerson.from_db, make_sequence=MiniPerson.to_db)

conn.execute("SELECT ('John', 33)::mini_person").fetchone()[0]
# MiniPerson(age=33, name='John', height=None)

conn.execute(
    "SELECT (%(person)s).name || ' next year will be ' ||  (%(person)s).age + 1",
    {"person": MiniPerson(name="John", age=33)},
).fetchone()[0]
# 'John next year will be 34'

Solving the 'fetchone()' annoyance with type checkers

If you use Mypy or other type checkers with Psycopg, you've probably seen false positives when calling fetchone(). Even if you are 100% certain your query will return a row, fetchone() is annotated as possibly returning None — so type checkers complain about patterns like:

cur.execute("SELECT count(*) FROM my_table")  # Always returns exactly one value
count = cur.fetchone()[0]   # Error: value of type "tuple | None" is not indexable

In Psycopg 3.3, the cursor has become an iterator, whereas it was previously only an iterable. The distinction is subtle but meaningful: an iterator holds its own iteration state and does not need to create a new object for each pass.

More importantly, this change means you can use next() or anext() to retrieve a record — and these functions never return None. This makes Mypy happy, and probably you too:

cur.execute("SELECT count(*) FROM my_table")
count = next(cur)[0]

Improvements to the connection pools

A connection pool’s parameters can now be changed dynamically — useful for example to support short-lived secret tokens as passwords, as requested by some cloud database providers.

A useful drain() method is now available to re-create all connections in a pool. This is helpful, for instance, when the database needs to be introspected to find the OIDs of extension types to register: without draining the pool the connections already in the pool would remain stale after the adapters have been configured.

...And more!

Other improvements include greater flexibility when navigating results after a fetchmany() call or after statements returning multiple result sets, the ability to reconfigure loaders after a query has run, and many other assorted enhancements. You can find the full list in the psycopg release notes and the pool release notes!

Your help is welcome

Psycopg is the de-facto standard for communication between Python and PostgreSQL — two major components powering countless businesses and mission-critical infrastructure. Maintaining such an important library to the highest standards of reliability, performance and security requires a lot of care and ongoing work.

If you use Python and PostgreSQL and want to help ensure that the interface between them remains robust and continues to improve, supporting new language and database features, please consider supporting the project 💜

Thank you very much, and happy hacking!