psycopg2

psycopg2 is a PostgreSQL database adapter for the Python programming language. It implements the Python DB-API 2.0 specification, providing a Pythonic interface for executing SQL commands, managing transactions, and retrieving results from a PostgreSQL database. The library is implemented as a C extension and relies on PostgreSQL's libpq client library to handle communication with the server, which helps deliver high performance and efficient data transfer.

Key features include support for parameterized queries using the standard %s placeholders, management of transactions through commit and rollback, and support for server-side cursors and the COPY protocol for bulk data transfer. It also exposes a range of cursor factories and utilities through psycopg2.extras (for example DictCursor and RealDictCursor) and provides a simple connection pool via psycopg2.pool. The API emphasizes compatibility with the Python DB-API 2.0 while exposing PostgreSQL-specific capabilities where appropriate.

Installation is typically done from PyPI with pip install psycopg2 or the psycopg2-binary package, the latter including the necessary libpq binaries to simplify installation on platforms without a system PostgreSQL client. Building from source is also possible and may be preferred when compiling against a specific libpq version.

psycopg2 is a mature, widely used solution for Python applications that interact with PostgreSQL. It remains actively maintained and widely deployed in production environments. The project has a newer generation, psycopg3, but psycopg2 continues to receive updates and remains compatible with a broad range of PostgreSQL server versions and Python releases.