What's the best way to work with databases in Python?

Python offers several approaches to database access depending on your needs.

SQLAlchemy is the most popular ORM. SQLAlchemy Core provides SQL expression language; SQLAlchemy ORM adds object-relational mapping. Define models as classes, query with Python code, and let it generate SQL. Version 2.0 modernized the API with better typing.

For async applications, SQLAlchemy 2.0 supports async natively, or use encode/databases for a lightweight async option. asyncpg provides fast async PostgreSQL access if you want to write raw SQL.

Raw SQL with psycopg (PostgreSQL) or other drivers gives maximum control and performance. Use parameterized queries to prevent SQL injection. Libraries like pypika help build SQL safely without full ORM overhead.

Connection pooling is essential for production. ORMs handle this, or use standalone pools like PgBouncer for PostgreSQL. Opening connections is expensive—reuse them.

Migrations manage schema changes. Alembic works with SQLAlchemy; it generates migrations from model changes and applies them safely. Always version your schema changes.

Repository pattern abstracts data access behind methods (get_user_by_id, create_order). This separates database logic from business logic, making testing easier and allowing you to swap implementations.

For simple scripts, sqlite3 is built-in and requires no setup. For applications, PostgreSQL is the common choice for its features and reliability.