SQL with Python: From Raw Queries to Modern Analytics Engines

Python and SQL have been intertwined since 1996. That is not a typo. Before Django existed, before pandas existed, before "data science" was a phrase anyone used, Python already had a standardized specification for talking to relational databases. And three decades later, the relationship between Python and SQL is deeper and more varied than it has ever been.

This article covers the full landscape: the foundational DB-API specification, the raw connection-and-cursor pattern you need to understand before anything else, the ORM layer that sits on top, the modern in-process analytics engines that are reshaping what "SQL in Python" even means, async database patterns for high-concurrency applications, connection pooling strategies that separate production code from scripts, and the critical security patterns that prevent your application from becoming the next Little Bobby Tables cautionary tale. Real code. Real history. Real understanding of what is actually happening when Python talks to a database.

The Foundation: PEP 248, PEP 249, and the DB-API

Every Python library that connects to a relational database — whether it is sqlite3, psycopg2, mysql-connector-python, or cx_Oracle — implements the same interface. That interface is called the DB-API, and it is one of the oldest standardized specifications in the Python ecosystem.

The story begins with PEP 248, authored by Greg Stein and Marc-Andre Lemburg. The PEP was created on May 8, 1996, though the specification document itself was last updated on April 9, 1996 (PEP numbers were assigned retroactively to specifications that already existed). PEP 248 defined the Python Database API Specification v1.0. Its stated goal was to encourage similarity between the Python modules used to access databases, achieving consistency that leads to more portable code and broader database connectivity from Python. (Source: PEP 248, Python.org)

Three years later, PEP 249 superseded it with DB-API 2.0, adding class-based exceptions, clarified semantics for stored procedures, and a more rigorous type system. PEP 249 remains the active specification to this day — it was last substantively updated in 2012 when Daniele Varrazzo converted it from text format to reStructuredText, and it still defines the interface that every modern Python database driver implements. (Source: PEP 249, Python.org)

The beauty of this design is its simplicity. PEP 249 specifies exactly three objects that matter: a module-level connect() function that returns a Connection object; Connection objects that represent a database session and can commit(), rollback(), or produce Cursor objects; and Cursor objects that execute SQL statements and fetch results. That is the entire conceptual model. Here is what it looks like in practice with Python's built-in sqlite3 module:

import sqlite3

# Connection: represents a database session
conn = sqlite3.connect("inventory.db")

# Cursor: executes SQL and fetches results
cursor = conn.cursor()

# Execute: send SQL to the database
cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        price REAL NOT NULL,
        quantity INTEGER DEFAULT 0
    )
""")

# Insert with parameterized query (NEVER string concatenation)
products = [
    ("Mechanical Keyboard", 89.99, 45),
    ("USB-C Hub", 34.50, 120),
    ("Monitor Stand", 42.00, 67),
]
cursor.executemany(
    "INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)",
    products
)

# Commit the transaction
conn.commit()

# Query and fetch results
cursor.execute("SELECT name, price FROM products WHERE price > ?", (40.0,))
rows = cursor.fetchall()

for name, price in rows:
    print(f"{name}: ${price:.2f}")

# Clean up
cursor.close()
conn.close()

Every line in that example follows the pattern that PEP 249 defines. And here is the critical point: if you swap sqlite3 for psycopg2 (PostgreSQL) or mysql.connector (MySQL), the code structure is virtually identical. The connect() call takes different parameters, and the placeholder syntax changes (PostgreSQL uses %s instead of ?), but the flow of connect, cursor, execute, fetch, commit, close is the same.

The Five Paramstyles

One of the practical complications of PEP 249 is that different database drivers use different placeholder syntax for parameterized queries. The specification defines five styles, and each driver declares which it supports via a paramstyle module-level attribute:

import sqlite3
import psycopg2

print(sqlite3.paramstyle)    # "qmark"     -> WHERE id = ?
print(psycopg2.paramstyle)   # "pyformat"  -> WHERE id = %(name)s

The five styles are: qmark (question mark placeholders), numeric (numbered like :1, :2), named (named like :name), format (C-style %s), and pyformat (Python-style %(name)s). This is the kind of practical detail that trips people up when switching databases, and the pandas documentation explicitly tells users to consult PEP 249's paramstyle definition when using pd.read_sql() with different database drivers.

SQL Injection: The Problem That Never Dies

Before going further, we need to address the single most important rule of writing SQL in any programming language: never construct SQL queries by concatenating user input into query strings.

The classic illustration comes from Randall Munroe's xkcd comic #327, "Exploits of a Mom," published in 2008. A school calls a parent to say they have lost their student records. It turns out the parent named her son Robert'); DROP TABLE Students;-- — "Little Bobby Tables." When the school's system inserted the name directly into a SQL string without sanitization, the injected SQL command deleted the entire Students table.

The comic is funny. The vulnerability is not. SQL injection ranked #5 on the OWASP Top 10 for 2025, dropping from #3 in 2021 — but not because it became less dangerous. According to OWASP's own data, the Injection category had the greatest number of CVEs of any category, with SQL injection alone accounting for over 14,000 CVEs. The drop in ranking reflects better framework defaults and developer awareness, not a decrease in severity. When SQL injection succeeds, the impact remains devastating. (Source: OWASP Top 10:2025, A05 Injection)

In Python, the fix is always the same: use parameterized queries.

# DANGEROUS: string concatenation
# An attacker can inject arbitrary SQL through the username variable
username = "admin' OR '1'='1"
cursor.execute(f"SELECT * FROM users WHERE name = '{username}'")
# Executes: SELECT * FROM users WHERE name = 'admin' OR '1'='1'
# This returns ALL users — authentication bypassed

# SAFE: parameterized query
# The database driver treats the parameter as a literal value, never as SQL
cursor.execute("SELECT * FROM users WHERE name = ?", (username,))
# The driver sends the query structure and the parameter separately
# No injection is possible regardless of what username contains

Context Managers: The Modern Pattern

Since Python 2.6, the with statement (defined by PEP 343 — Generator-based Context Managers) has been the idiomatic way to manage resources that need cleanup. All modern database drivers support this pattern:

import sqlite3

# Connection as context manager: auto-commits on success, rolls back on exception
with sqlite3.connect("inventory.db") as conn:
    cursor = conn.cursor()
    
    cursor.execute("""
        UPDATE products 
        SET quantity = quantity - 1 
        WHERE name = ? AND quantity > 0
    """, ("USB-C Hub",))
    
    if cursor.rowcount == 0:
        raise ValueError("Product out of stock or not found")
    
    # If we reach here without exception, the context manager commits
    # If an exception occurs, it rolls back automatically

This pattern eliminates an entire category of bugs: forgotten commits, unclosed connections, and transactions left dangling after exceptions. It is how you should write every database interaction.

SQLAlchemy: The Database Toolkit

For any application more complex than a script, raw DB-API calls become unwieldy. You end up writing the same boilerplate for connection management, result mapping, and transaction handling across every query. This is the problem that SQLAlchemy solves.

SQLAlchemy was created by Mike Bayer, first released in February 2006. Development began in 2005, and Bayer has described the project as emerging from years of building database abstraction layers across Java, Perl, and C. He drew on patterns from Martin Fowler's Patterns of Enterprise Architecture and on experience at Major League Baseball where eager loading became a core requirement. (Source: Talk Python to Me, Episode #5)

What makes SQLAlchemy architecturally distinct from simpler ORMs is its two-layer design. Bayer's stated philosophy is to keep developers working in SQL as much as possible, while automating the repetitive plumbing around it. (Source: Talk Python to Me, Episode #344)

Layer 1: Core (SQL Expression Language)

The Core layer provides a Pythonic way to construct SQL statements without writing raw strings. It is database-agnostic — the same expression compiles to the correct SQL dialect for PostgreSQL, MySQL, SQLite, or Oracle.

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Float, select, func

engine = create_engine("sqlite:///inventory.db")
metadata = MetaData()

products = Table("products", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String, nullable=False),
    Column("price", Float, nullable=False),
    Column("quantity", Integer, default=0),
    Column("category", String),
)

metadata.create_all(engine)

# Build a query using Python expressions — no raw SQL strings
query = (
    select(products.c.category, func.avg(products.c.price).label("avg_price"))
    .where(products.c.quantity > 0)
    .group_by(products.c.category)
    .having(func.avg(products.c.price) > 25.0)
    .order_by(func.avg(products.c.price).desc())
)

with engine.connect() as conn:
    results = conn.execute(query)
    for row in results:
        print(f"{row.category}: ${row.avg_price:.2f}")

The Core automatically generates parameterized queries. It handles identifier quoting, dialect-specific SQL syntax, connection pooling, and transaction management. Organizations like Reddit and Dropbox have built large systems using Core alone, without touching the ORM. (Source: SQLAlchemy Library page)

Layer 2: ORM (Object-Relational Mapper)

The ORM maps Python classes to database tables, allowing you to work with rows as Python objects. The syntax shown here uses SQLAlchemy 2.0's modern declarative style with Python type annotations — itself enabled by PEP 484 (Type Hints) and PEP 526 (Variable Annotations):

from sqlalchemy import create_engine, String, Float, Integer
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session

class Base(DeclarativeBase):
    pass

class Product(Base):
    __tablename__ = "products"
    
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    price: Mapped[float] = mapped_column(Float)
    quantity: Mapped[int] = mapped_column(Integer, default=0)
    category: Mapped[str] = mapped_column(String(50), nullable=True)
    
    def is_in_stock(self) -> bool:
        return self.quantity > 0
    
    def __repr__(self) -> str:
        return f"Product(name={self.name!r}, price={self.price})"

engine = create_engine("sqlite:///inventory.db")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # Create
    keyboard = Product(name="Mechanical Keyboard", price=89.99, 
                       quantity=45, category="peripherals")
    session.add(keyboard)
    session.commit()
    
    # Read with filtering
    expensive = session.query(Product).filter(
        Product.price > 50.0,
        Product.quantity > 0
    ).all()
    
    # Update
    keyboard.price = 79.99  # just modify the Python object
    session.commit()        # SQLAlchemy tracks changes and generates UPDATE
    
    # Query with aggregation
    from sqlalchemy import func
    stats = session.query(
        Product.category,
        func.count(Product.id).label("count"),
        func.sum(Product.price * Product.quantity).label("inventory_value")
    ).group_by(Product.category).all()

When Not to Use the ORM

The ORM is not always the right answer. Bulk data operations — inserting millions of rows, performing mass updates, or running analytical aggregations — are often significantly faster through Core or raw SQL. The ORM's change tracking and identity map create per-object overhead that becomes meaningful at scale. Similarly, if your queries are primarily read-only reports with complex GROUP BY clauses, window functions, and CTEs, the ORM's object-mapping overhead adds latency without providing any of its primary benefits (change tracking, relationship management, identity caching). In these cases, SQLAlchemy's Core layer or DuckDB provide better abstractions.

Connection Pooling: The Hidden Architecture

A detail that separates production database code from tutorial scripts is connection pooling. Opening a new TCP connection to a database server takes time — DNS resolution, TCP handshake, TLS negotiation, authentication, and protocol setup. In a web application handling hundreds of requests per second, opening and closing a connection per request would consume more time in connection overhead than in actual query execution.

SQLAlchemy solves this transparently. When you call create_engine(), it creates a connection pool by default:

from sqlalchemy import create_engine, text

# The engine manages a pool of database connections
engine = create_engine(
    "postgresql://user:pass@localhost/app",
    pool_size=10,          # maintain 10 persistent connections
    max_overflow=20,       # allow up to 20 more under load
    pool_timeout=30,       # wait 30s for a connection before error
    pool_recycle=3600,     # recycle connections after 1 hour
    pool_pre_ping=True,    # verify connections are alive before use
)

# Each connect() call checks out a connection from the pool
# Closing returns it to the pool — the TCP connection stays open
with engine.connect() as conn:
    result = conn.execute(text("SELECT 1"))

The pool_pre_ping=True setting is particularly important in production. Without it, your application will encounter intermittent OperationalError exceptions when a pooled connection has been silently terminated by a firewall, load balancer, or the database server's own idle timeout. The pre-ping issues a lightweight check query before handing the connection to your code.

For lightweight scripts or embedded databases like SQLite, pooling is unnecessary and sometimes counterproductive. SQLAlchemy uses NullPool (no pooling) by default for SQLite, since there is no network overhead to amortize.

DuckDB: SQL as Analytics Engine

A fundamentally different approach to SQL in Python has emerged in the last few years, and it is reshaping how data professionals think about the relationship between Python and databases.

DuckDB was developed by Mark Raasveldt and Hannes Mühleisen at the Centrum Wiskunde & Informatica (CWI) in Amsterdam — the same Dutch research institute where Guido van Rossum created Python in the early 1990s. First presented as a demo paper at ACM SIGMOD 2019, DuckDB is an in-process analytical database: it runs inside your Python process (no separate server), uses a columnar vectorized execution engine, and is optimized for the complex aggregations and joins that characterize analytical workloads. (Source: CWI publication record)

In their SIGMOD 2019 paper, Raasveldt and Mühleisen argued that SQLite's popularity proved a clear demand for embedded data management, but that no equivalent existed for analytical workloads. DuckDB was built to fill that gap. (Source: Raasveldt & Mühleisen, SIGMOD 2019)

The practical result is that DuckDB lets you write SQL queries against pandas DataFrames, Parquet files, CSV files, and JSON files — without loading everything into memory and without running a database server.

import duckdb
import pandas as pd

# DuckDB can query pandas DataFrames directly with SQL
sales_df = pd.DataFrame({
    "date": pd.date_range("2025-01-01", periods=1000, freq="D"),
    "product": ["Widget A", "Widget B", "Gadget X"] * 333 + ["Widget A"],
    "revenue": [round(x, 2) for x in __import__("random").choices(
        [i * 0.01 for i in range(1000, 10000)], k=1000
    )]
})

# SQL directly on a DataFrame variable — no import, no loading
result = duckdb.sql("""
    SELECT 
        product,
        DATE_TRUNC('month', date) AS month,
        SUM(revenue) AS total_revenue,
        COUNT(*) AS num_sales,
        ROUND(AVG(revenue), 2) AS avg_sale
    FROM sales_df
    GROUP BY product, DATE_TRUNC('month', date)
    ORDER BY month, total_revenue DESC
""").df()  # .df() returns the result as a pandas DataFrame

print(result.head(10))

DuckDB also queries files directly without loading them into memory first — a capability that is transformative when working with large datasets:

import duckdb

# Query a Parquet file directly — DuckDB reads only needed columns and row groups
result = duckdb.sql("""
    SELECT 
        customer_segment,
        COUNT(*) AS orders,
        SUM(order_total) AS revenue
    FROM 'orders_2025.parquet'
    WHERE order_date >= '2025-06-01'
    GROUP BY customer_segment
    HAVING COUNT(*) > 100
    ORDER BY revenue DESC
""").df()

# Query across multiple CSV files using glob patterns
monthly = duckdb.sql("""
    SELECT * FROM read_csv_auto('logs/2025-*.csv')
    WHERE status_code >= 500
""").df()

DuckDB's SQL parser is derived from PostgreSQL's parser via the libpg_query library (created by Lukas Fittl), so its SQL dialect is familiar to anyone who has worked with PostgreSQL. It supports window functions, CTEs, correlated subqueries, lateral joins, and complex nested types — far beyond what you can comfortably express in pandas. (Source: Crunchy Data engineering blog)

Connecting pandas to Databases

Pandas bridges the gap between SQL databases and Python's data analysis workflow through read_sql() and to_sql(). These functions rely directly on PEP 249-compliant drivers (via SQLAlchemy or raw connections) to move data between DataFrames and database tables.

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql://user:pass@localhost/analytics")

# Read: SQL query result directly into a DataFrame
df = pd.read_sql("""
    SELECT 
        department,
        hire_date,
        salary
    FROM employees 
    WHERE hire_date >= '2024-01-01'
""", engine, parse_dates=["hire_date"])

# Transform in pandas
df["tenure_days"] = (pd.Timestamp.now() - df["hire_date"]).dt.days
summary = df.groupby("department")["salary"].agg(["mean", "median", "count"])

# Write: DataFrame back to a database table
summary.to_sql("department_salary_summary", engine, if_exists="replace")

A significant performance improvement arrived in pandas 2.2 (January 2024) with support for ADBC (Arrow Database Connectivity) drivers. Traditional database connectivity through SQLAlchemy reads data row-by-row, then converts to pandas' columnar layout — an inherently inefficient translation. ADBC drivers read data in columnar format from the start, using Apache Arrow as the interchange format, which can make reads substantially faster. The ADBC specification itself reached version 1.0.0 in January 2023, with DuckDB among the projects that provided early feedback on the standard's design. (Source: Apache Arrow blog, Introducing ADBC)

import pandas as pd
import adbc_driver_postgresql.dbapi as pg_dbapi

# ADBC: columnar reads — significantly faster for large result sets
with pg_dbapi.connect("postgresql://user:pass@localhost/analytics") as conn:
    df = pd.read_sql("SELECT * FROM large_table", conn)

Async Database Access: The Concurrency Pattern

Web frameworks like FastAPI and Starlette are built on Python's asyncio event loop. If your database queries block the event loop, your application's throughput collapses — a single slow query stalls every other request waiting behind it. Async database access solves this by yielding control back to the event loop while waiting for database I/O.

SQLAlchemy 2.0 introduced production-ready async support, built on the greenlet library to bridge asyncio's syntax with SQLAlchemy's existing architecture. Bayer's approach was deliberate: rather than rewriting the ORM for async from scratch, the team built an async wrapper that context-switches transparently under the hood. (Source: SQLAlchemy 2.0.0 Release Announcement)

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy import select

# Use an async-compatible driver (asyncpg for PostgreSQL)
async_engine = create_async_engine("postgresql+asyncpg://user:pass@localhost/app")
AsyncSessionLocal = sessionmaker(async_engine, class_=AsyncSession)

async def get_expensive_products():
    async with AsyncSessionLocal() as session:
        result = await session.execute(
            select(Product).where(Product.price > 50.0)
        )
        return result.scalars().all()

# In a FastAPI endpoint:
# @app.get("/products/expensive")
# async def expensive_products():
#     return await get_expensive_products()

The Right Tool for the Job

Each approach to SQL in Python has a clear use case. Understanding when to reach for which tool is what separates effective Python developers from those who force every problem through the same hammer.

Raw DB-API (sqlite3, psycopg2) is appropriate when you need minimal dependencies, maximum control over the exact SQL being executed, or are building a lightweight script or utility. It is also what you should learn first, because every other tool is built on top of it. Choose this when your application has fewer than a handful of queries, when startup time matters, or when you need to avoid third-party dependencies entirely.

SQLAlchemy Core is appropriate when you are building an application that needs to work across multiple database backends, or when you want the safety of parameterized query construction without the overhead of full object-relational mapping. It is the choice of teams that want their Python code to express SQL intent clearly. Reach for Core when you have complex query logic that varies at runtime — dynamic filtering, conditional joins, multi-tenant query construction — where raw SQL strings would become unmaintainable.

SQLAlchemy ORM is appropriate when you are building a web application or service where database rows map naturally to domain objects — users, orders, products, sessions. The ORM handles change tracking, relationship loading, and transaction management so you do not have to. Avoid it for bulk data operations, write-heavy analytical pipelines, or when you find yourself constantly fighting the ORM to produce the exact query you want.

DuckDB is appropriate for analytical workloads: aggregating large datasets, querying Parquet or CSV files, running complex GROUP BY and window function queries, or when you want SQL's expressiveness applied directly to in-memory DataFrames or files on disk without any server infrastructure. DuckDB is not a replacement for PostgreSQL in a web application — it excels at read-heavy analytics, not concurrent write-heavy transactional workloads.

pandas read_sql / to_sql is appropriate when you need to pull data from a database into a DataFrame for further analysis, visualization, or transformation — bridging the SQL and Python data science worlds. For large result sets (millions of rows), prefer ADBC drivers or DuckDB's direct file querying over the traditional SQLAlchemy-backed read_sql() path.

A Complete Real-World Pattern

Here is a pattern that combines several of these tools in a realistic analytics workflow — loading from a production database, analyzing with DuckDB's SQL engine, and writing results back:

import pandas as pd
import duckdb
from sqlalchemy import create_engine

# Pull raw data from production PostgreSQL
engine = create_engine("postgresql://readonly:pass@prod-db/app")
orders = pd.read_sql("""
    SELECT order_id, customer_id, product_id, 
           quantity, unit_price, order_date
    FROM orders
    WHERE order_date >= '2025-01-01'
""", engine, parse_dates=["order_date"])

customers = pd.read_sql("SELECT * FROM customers", engine)

# Analyze with DuckDB SQL — joins, window functions, CTEs
analysis = duckdb.sql("""
    WITH order_values AS (
        SELECT 
            o.customer_id,
            c.segment,
            o.order_date,
            o.quantity * o.unit_price AS order_value,
            ROW_NUMBER() OVER (
                PARTITION BY o.customer_id 
                ORDER BY o.order_date
            ) AS order_sequence
        FROM orders o
        JOIN customers c ON o.customer_id = c.customer_id
    )
    SELECT 
        segment,
        COUNT(DISTINCT customer_id) AS unique_customers,
        ROUND(AVG(order_value), 2) AS avg_order_value,
        ROUND(AVG(CASE WHEN order_sequence = 1 
                       THEN order_value END), 2) AS avg_first_order,
        ROUND(AVG(CASE WHEN order_sequence > 1 
                       THEN order_value END), 2) AS avg_repeat_order
    FROM order_values
    GROUP BY segment
    ORDER BY avg_order_value DESC
""").df()

print(analysis)

# Write results to a reporting database
reporting_engine = create_engine("postgresql://writer:pass@reporting-db/dashboards")
analysis.to_sql("customer_segment_analysis", reporting_engine, if_exists="replace")

This workflow demonstrates why Python has become the lingua franca of data work: you can pull from a production database, run analytical SQL without a data warehouse, and push results to a reporting layer — all in a single script.

Securing Database Credentials

Hardcoding database passwords in source code is a security failure that parameterized queries cannot fix. Every code example in this article uses inline connection strings for readability — you should never do this in real applications.

import os
from sqlalchemy import create_engine

# Read credentials from environment variables — never hardcode them
db_url = os.environ["DATABASE_URL"]
engine = create_engine(db_url)

# For more structured credential management:
engine = create_engine(
    "postgresql://{user}:{password}@{host}/{dbname}".format(
        user=os.environ["DB_USER"],
        password=os.environ["DB_PASSWORD"],
        host=os.environ["DB_HOST"],
        dbname=os.environ["DB_NAME"],
    )
)

In production environments, use secret management services (AWS Secrets Manager, HashiCorp Vault, Azure Key Vault, or GCP Secret Manager) to rotate credentials automatically. For local development, .env files loaded via python-dotenv keep credentials out of version control. The principle is straightforward: if git log can reveal your database password, your security posture has a structural flaw.

The PEPs That Built This Ecosystem

Beyond PEP 248 and 249, several other Python Enhancement Proposals have directly shaped how SQL works in Python.

PEP 343 (The "with" Statement) made context managers possible, giving us the with connection: and with Session() as session: patterns that handle commits, rollbacks, and cleanup automatically.

PEP 249's autocommit attribute was integrated into Python's sqlite3 module in Python 3.12, aligning the standard library more closely with the DB-API specification's transaction control model. Before 3.12, sqlite3 used a non-standard implicit transaction behavior that frequently confused developers and led to subtle bugs. The new autocommit parameter on sqlite3.connect() gives explicit control over PEP 249-compliant transaction handling. The PEP itself notes that when DB-API 2.0 was written in 1999, Python's warning framework did not yet exist — a reminder of how long this specification has been evolving. (Source: Python 3.12 sqlite3 documentation)

PEP 484 (Type Hints) and PEP 526 (Variable Annotations) enabled SQLAlchemy 2.0's modern declarative syntax, where Mapped[int] and Mapped[str] annotations define both the Python type and the database column type in a single declaration.

PEP 492 (Async/Await) enabled SQLAlchemy's async support, allowing database queries in asyncio-based web frameworks like FastAPI and Starlette without blocking the event loop.

PEP 574 (Pickle Protocol 5) improved serialization of large data objects like DataFrames and query results for inter-process communication — relevant when distributed systems pass SQL query results between workers.

Key Takeaways

1. The foundation has not changed: PEP 249 defined connect, cursor, execute, fetch, commit in 1999, and every modern tool — SQLAlchemy, DuckDB, pandas — ultimately builds on that same pattern.
2. Parameterized queries are non-negotiable: SQL injection is structurally prevented when query structure and parameter values travel separately. String concatenation is never the answer. But parameterization alone is not a complete security strategy — apply defense in depth.
3. Context managers eliminate an entire bug category: Use with for every database interaction. Forgotten commits and dangling transactions belong to a style of Python that should stay in the past.
4. Connection pooling separates production from prototype: If your application opens more than a handful of connections per minute, pooling is not an optimization — it is a requirement.
5. DuckDB changes what "SQL in Python" means for analytics: Querying DataFrames and files with full SQL — including window functions and CTEs — without a server is a genuine shift in how data work gets done.
6. Async is a tool, not a default: Use async database access for high-concurrency web applications. Use synchronous access for everything else. Complexity has a cost.
7. Know when to stop using raw cursors: Raw DB-API is the right starting point and the right tool for scripts. SQLAlchemy is the right tool once your application has grown past that. There is a clear line between them.
8. Credentials belong in your environment, not in your code: Parameterized queries protect your data from attackers. Secret management protects your data from your own source code.

The relationship between Python and SQL is not a single tool or a single pattern — it is a layered ecosystem built on thirty years of standardization, starting with Greg Stein's PEP 248 in 1996 and continuing through DuckDB's in-process analytical engine today. Learn the raw DB-API pattern first. Use parameterized queries always. Reach for SQLAlchemy when your application outgrows raw cursors. Reach for DuckDB when your analytical queries outgrow pandas. And never, under any circumstances, name your child Robert'); DROP TABLE Students;--.