Connecting Python to SQL Databases (sqlite3, SQLAlchemy)

The previous article covered writing SQL queries — this one covers python sqlite3 SQLAlchemy tutorial territory: actually connecting Python code to a live database to run those queries. The short version, stated upfront: use sqlite3 unless there's a specific reason not to, and reach for SQLAlchemy specifically when the same code needs to also run against PostgreSQL or MySQL without rewriting your entire data-access layer.

Two Ways Python Talks to a Database

The missing piece

Writing SQL queries, covered in the previous article, is one skill. Actually connecting Python code to a live database, running those queries, and getting results back into Python is a genuinely separate, missing piece — the entire focus of this article.

The short version, stated upfront

Use sqlite3 unless there's a specific reason not to. Reach for SQLAlchemy specifically when the same code needs to also run against PostgreSQL, MySQL, or another database backend, without rewriting your entire data-access layer for each one.

What this article covers

sqlite3 for direct, lightweight database work, SQLAlchemy for a more portable, more scalable approach, and — genuinely the most practically useful part of this article — how both connect naturally to pandas for actual analysis.

Getting Started with sqlite3: Python's Built-In Database Module

What sqlite3 provides

The Python sqlite3 module provides an interface for interacting with SQLite databases — lightweight, serverless, and self-contained, storing an entire database as a single file on disk. Since it's part of the standard library, no installation is required at all, exactly the same "batteries included" principle covered in the earlier standard library overview article.

The basic connection pattern

import sqlite3

c sqlite3.connect("app.db")

sqlite3.connect("app.db") creates the file if it doesn't already exist. This convenience is also worth flagging as a genuine, classic bug source: a typo in the file path silently creates a brand-new, empty database at the mistyped location, rather than raising an error telling you the file you actually meant to open doesn't exist. If you're ever confused about why a query returns no data, or a table "doesn't exist" that you're certain you created, double-check the exact path you connected with first.

The core CRUD workflow

cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT,
        salary REAL
    )
""")

cursor.execute("INSERT INTO employees (name, salary) VALUES (?, ?)", ("Alex", 65000))
conn.commit()

cursor.execute("SELECT * FROM employees WHERE salary > ?", (60000,))
results = cursor.fetchall()
print(results)   # [(1, 'Alex', 65000.0)]

A cursor, obtained from the connection, is what actually executes SQL statements — CREATE TABLE, INSERT, SELECT, and everything else covered in the previous SQL article. .fetchone() retrieves a single row from the most recent query; .fetchall() retrieves every remaining row, as a list of tuples.

The critical safety habit: parameterized queries

Notice the ? placeholders in both INSERT and SELECT above, with the actual values passed as a separate tuple argument, rather than directly embedded into the SQL string itself. This is genuinely non-negotiable, not a stylistic preference:

# DANGEROUS — never do this with any value that could come from user input
name = "Alex"
cursor.execute(f"SELECT * FROM employees WHERE name = '{name}'")

# SAFE — always do this instead
cursor.execute("SELECT * FROM employees WHERE name = ?", (name,))

String-formatting values directly into a SQL query opens the door to SQL injection — a genuinely serious security vulnerability where a maliciously crafted input value can alter the query's actual meaning entirely (potentially deleting data, or exposing data that was never meant to be accessible). Parameterized queries (? placeholders, with values passed separately) prevent this entirely, since the database driver handles the values as pure data, never as executable SQL syntax — this is a habit worth building from the very first query you ever write, not something to retrofit later, covered again explicitly in Section 5.

3. Moving to SQLAlchemy: A Portable Database Interface

The problem SQLAlchemy solves

sqlite3 genuinely works well for simple tasks — but writing raw SQL query strings gets messy as a project grows, and it ties your code specifically to SQLite's exact syntax, making a later switch to a different database backend genuinely painful. SQLAlchemy's ORM (Object-Relational Mapping) layer lets you work with a database using Python classes and objects instead of hand-written SQL commands, and — genuinely the bigger practical benefit for most projects — it abstracts away the database-specific syntax differences between SQLite, PostgreSQL, MySQL, and others.

The starting point: create_engine()

from sqlalchemy import create_engine

engine = create_engine("sqlite:///data.db")

Every SQLAlchemy application starts with an Engine — a central, typically module-level or application-level object that manages connectivity and maintains a pool of reusable database connections underneath. It's configured through a single URL string, and this is genuinely the core of SQLAlchemy's portability: swapping the connection string's prefix is often the only change needed to point the exact same code at a completely different database:

sqlite_engine = create_engine("sqlite:///data.db")
postgres_engine = create_engine("postgresql://user:password@localhost/mydb")
mysql_engine = create_engine("mysql://user:password@localhost/mydb")

Core vs. ORM: two ways to work once connected

SQLAlchemy offers two distinct working styles. Core lets you write SQL-like expressions directly in Python, without hand-typing raw SQL strings, but without the full class-based object mapping either:

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM employees WHERE salary > :min_salary"),
                            {"min_salary": 60000})
    for row in result:
        print(row)

The full ORM goes further, letting you define Python classes that map directly to database tables, and interact with rows as if they were ordinary Python objects:

from sqlalchemy.orm import DeclarativeBase, Session
from sqlalchemy import Column, Integer, String, Float

class Base(DeclarativeBase):
    pass

class Employee(Base):
    __tablename__ = "employees"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    salary = Column(Float)

with Session(engine) as session:
    high_earners = session.query(Employee).filter(Employee.salary > 60000).all()

Beginners can start productively with Core alone, before adopting full ORM patterns — Core's text()-based expressions are already a meaningful step up from raw sqlite3 string queries, and the full ORM's additional class-mapping complexity is worth adopting once a project's genuinely growing complexity justifies it, rather than as a mandatory starting point.

Bridging to Pandas: to_sql() and read_sql()

Reading a query directly into a DataFrame

import pandas as pd

query = """
    SELECT region, month, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region, month
"""

df = pd.read_sql(query, engine)
print(df.head())

Tying directly back to the earlier pandas articles in this series: pd.read_sql(query, engine) runs a SQL query and returns the result directly as a DataFrame — letting a SQL join or aggregation from the previous article feed straight into pandas for further analysis or visualization, with zero manual conversion step required.

The mirror-image operation: writing a DataFrame to a table

df.to_sql("sales_summary", engine, if_exists="replace", index=False)

df.to_sql() writes a pandas DataFrame directly into a new or existing database table — genuinely useful for loading a cleaned CSV (tying back to the earlier CSV and data-cleaning articles) into a proper, queryable database, rather than continuing to work with a plain file. if_exists="replace" overwrites the table if it already exists; if_exists="append" adds new rows to an existing table instead.

A practical example: Excel to in-memory SQLite

df = pd.read_excel("sales_report.xlsx")

memory_engine = create_engine("sqlite:///:memory:")
df.to_sql("sales", memory_engine, index=False)

result = pd.read_sql("SELECT region, SUM(amount) as total FROM sales GROUP BY region", memory_engine)
print(result)

sqlite:///:memory: creates a genuine SQLite database that exists entirely in RAM, with no file ever written to disk at all — genuinely useful for exactly this kind of quick, exploratory SQL-based analysis, importing data from an Excel file (tying back to the earlier pandas reading article) and using SQL's GROUP BY/aggregation syntax to explore it, without permanently creating a database file you'd need to clean up afterward.

Choosing the Right Tool and Best Practices

A practical decision guide

Reach for sqlite3 for quick scripts, small local tools, or any situation where zero dependencies genuinely matter most — it's part of the standard library, requiring nothing beyond Python itself. Reach for SQLAlchemy when a project might genuinely need to switch database backends later, when working with larger or more complex schemas where the ORM's object-mapping genuinely simplifies the resulting code, or when a team's existing codebase already standardizes on it.

Connection management best practices

Always close connections explicitly, or — considerably more reliable — use a with statement, exactly tying back to the earlier context managers article in this series:

with sqlite3.connect("app.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM employees")
    results = cursor.fetchall()
# connection automatically closed here, even if an error occurred above

Be aware that an in-memory SQLite database only lives as long as its connection stays open — the moment that connection closes, every table and row created inside it is gone permanently, with no way to recover it. This is exactly the intended, expected behavior for the quick, exploratory use case shown in Section 4 — but it's worth understanding clearly, so you don't accidentally rely on an in-memory database persisting data across separate script runs.

Closing security reminder

Worth repeating explicitly, since it's genuinely the single most important habit in this entire article: always use parameterized queries (sqlite3's ? placeholders) or SQLAlchemy's expression building (text(...) with named parameters, or the ORM's own filtering methods) rather than directly formatting user input into a SQL string. This is a habit worth building from the very first query you ever write in a real project — not something to retrofit later, once a genuine SQL injection vulnerability has already made it into production code that's handling real, potentially untrusted input.