Using Postgres with python is easy and provides first class database capabilities for applications and data processing. When starting a new project, you need to choose which PostgreSQL python driver to use. The best choice depends on your deployment, python version, and background. Here’s a quick guide to choosing between three popular python PostgreSQL drivers:
This is the most common and widely supported Python PostgreSQL driver. It provides a DB-API compatible wrapper on top of the native libpq postgresql library. It supports automatic mapping of query results to python dictionaries as well as named tuples, and is commonly used by ORM’s and frameworks, including SQLAlchmey. Because psycopg2 uses libpq, it supports the same environment variables as libpq for connection properties (PGDATABASE, PGUSER, PGHOST, etc) as well as using a .pgpass file. And, supports
COPY directly for bulk loading. If you use Postgres from multiple languages this driver will feel the most “native PG.”
Using libpq means psycopg2 obviously depends on libpq which is no problem for most developers but some cases require special care. A recent enhancement from the psycopg2 team makes it simple to install the full package with bundled libpq binary dependencies via pip. This makes it easy for manual install or scripted install on servers with CPython, but the full libpq-dev packages are required for use with PyPy (using the psycopg2cffi port which lags a little behind the main version). And there is a different pre-compiled version that works with Amazon Web Services linux – so that can be used with AWS Lambda.
An actively maintained, pure python DB-API compatible driver that works across OS and in many different Python environments, such as PyPy and Jython. With no native dependencies, it is easy to package pg8000 for distribution with an application with Python 2 or Python 3. pg8000 tries to be focused on just PostgreSQL database access, ignoring some of the convenience functions in libpq. For example, pg800 does not directly support a .pgpass file or environment variables – but you can use the pgpasslib project in conjunction. pg8000 does not include the many Extras included with psycopg2 to retrieve dictionaries or named tuples but does support most data types.
pg8000 is a good choice for distributing with an application bundle, or use with PyPy.
An exciting and new (in 2016) driver supporting Python/asyncio. Rather than using libpq and the text format protocol, asyncpg implements the Postgres binary protocol, which provides better type support and faster performance. asyncpg is designed (and tested) to be fast, able to achieve much higher retrieval speeds that either driver above. See the performance benchmarking provided by the authors. With the emphasis on speed, asyncpg does not support the DB-API so code feels different than other Python database access.
asyncpg is a C-extension, so only works with CPython and requires Python 3.5+; but has no dependencies, so a simple pip install (or app bundling) works and supports PostgreSQL 9.1+. asyncpg is definitely worth looking at if you’re using Python 3.5.
For more information, see the project pages for each driver discussed above: