SQL Integration Patterns

SQL Integration Patterns

Core Principles

1. Always use parameterized queries — Never interpolate user input into SQL strings. Use bind parameters to prevent SQL injection and improve query plan caching.
2. Manage connections explicitly — Open connections late, close them early. Use context managers or connection pools to prevent resource leaks.
3. Use ORMs for application logic, raw SQL for analytics — ORMs like SQLAlchemy and Django ORM excel at CRUD operations with business rules. Raw SQL is better for complex analytical queries and bulk operations.
4. Separate transformation logic from connection logic — Keep database credentials, connection setup, and query execution in distinct layers so each can be tested and maintained independently.
5. Prefer declarative over imperative — Tools like dbt, SQLAlchemy's declarative base, and dbplyr let you express what you want rather than how to get it, reducing bugs and improving readability.

Python Database Connections

Python offers multiple libraries for database access, each suited to different use cases. The core pattern is always the same: establish a connection, execute parameterized queries, and clean up resources.

import psycopg  # psycopg 3
import pandas as pd

# Context manager ensures connection closes even on error
conn_string = "host=warehouse.internal dbname=analytics user=etl_svc"

with psycopg.connect(conn_string) as conn:
    # Parameterized query — %s placeholders, tuple of values
    result = conn.execute(
        "SELECT order_id, total FROM orders WHERE region = %s AND status = %s",
        ("us-west", "shipped"),
    ).fetchall()

    # pandas integration for analytical workflows
    df = pd.read_sql(
        "SELECT date_trunc('month', order_date) AS month, sum(total) AS revenue "
        "FROM orders WHERE region = %(region)s GROUP BY 1",
        conn,
        params={"region": "us-west"},
    )

See python-db-connectors for: psycopg3 async patterns, pyodbc for SQL Server, mysql-connector, bulk inserts with COPY, and error handling strategies.