Pyscopg
On this page
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-basiccd postgresuv add "psycopg[binary]"Basic 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.