Psycopg

Introduction#

Psycopg is the most popular PostgreSQL adapter for Python.

Federico Di Gregorio, the author of the package, after dealing with a badly written python+postgres driver in the company he worked for at the time, he wrote his own simplified version (psycopg) wanted to call it psychopg (a reference to their psychotic driver). However, he typed the name wrong.

Project Setup#

uv init psycopg-basic
cd postgres
uv add "psycopg[binary]"

Basic Usage#

Module Usage

Row Factories.#

Row Factories allows you to map returned database data to any Python object, including standard lib dataclasses, models from the great attrs library, and yes, Pydantic models!

This is the best compromise of approaches: the ultimate flexibility of raw SQL, with the validation / type safety capabilities of Pydantic to model the database.

Psycopg also handles things like variable input sanitation to avoid SQL injection.

Pending#

• FastAPI, Pydantic, Psycopg3: The Ultimate Trio for Python Web APIs