Working with Databases in Python

Databases are at the heart of data engineering. Whether you are extracting from a source database or loading into a data warehouse, you need to interact with SQL databases efficiently and safely from Python. This lesson covers SQLAlchemy, connection pooling, ORM vs raw SQL, migrations, and bulk operations.

---

SQLAlchemy: The Standard

SQLAlchemy is the most popular database toolkit for Python. It provides two layers:

Layer	Description	When to Use
Core	SQL expression language	Data engineering, ETL
ORM	Object-Relational Mapping	Application development

Installing

pip install sqlalchemy psycopg2-binary  # PostgreSQL
pip install sqlalchemy pymysql          # MySQL

---

Creating a Connection

from sqlalchemy import create_engine

# PostgreSQL
engine = create_engine(
    "postgresql://user:password@localhost:5432/mydb",
    pool_size=5,           # Number of connections to keep open
    max_overflow=10,       # Extra connections allowed beyond pool_size
    pool_timeout=30,       # Seconds to wait for a connection
    pool_recycle=1800,     # Recycle connections after 30 minutes
    echo=False,            # Set True to log all SQL
)

# Test the connection
with engine.connect() as conn:
    result = conn.execute("SELECT 1")
    print(result.scalar())  # 1

Connection URL Formats