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][1], which allow you to write [expressive and safe queries][2]. [1]: https://docs.python.org/3.14/whatsnew/3.14.html#pep-750-template-string-literals [2]: https://www.psycopg.org/psycopg3/docs/basic/tstrings.html 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][3], 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`][4] 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}") [3]: https://docs.python.org/3/tutorial/inputoutput.html#formatted-string-literals [4]: https://www.psycopg.org/psycopg3/docs/api/sql.html Check out the complete [t-string support documentation][5] for inspiration! [5]: https://www.psycopg.org/psycopg3/docs/basic/tstrings.html # More flexible composite adaptation Previously, it was only possible to [adapt PostgreSQL composites][6] to Python sequence types with a strict 1:1 mapping to the fields of the database type. [6]: https://www.psycopg.org/psycopg3/docs/basic/pgtypes.html#adapt-composite 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][7], whereas it was previously only an [iterable][8]. 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()`][9] or [`anext()`][10] 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] [7]: https://docs.python.org/3/glossary.html#term-iterator [8]: https://docs.python.org/3/glossary.html#term-iterable [9]: https://docs.python.org/3/library/functions.html#next [10]: https://docs.python.org/3/library/functions.html#anext # 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()`][11] 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. [11]: https://www.psycopg.org/psycopg3/docs/api/pool.html#psycopg_pool.ConnectionPool.drain # ...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][12] and the [pool release notes][13]! [12]: https://www.psycopg.org/psycopg3/docs/news.html#psycopg-3-3-0 [13]: https://www.psycopg.org/psycopg3/docs/news_pool.html#psycopg-pool-3-3-0 # 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](https://github.com/sponsors/dvarrazzo) 💜 Thank you very much, and happy hacking!
