Advertisement
🗄️ Real-World

Python Database Programming – SQLite & SQLAlchemy

Python works with virtually every database. The built-in sqlite3 module requires zero setup; SQLAlchemy provides an ORM for larger projects.

⏱️ 20 min read🎯 Real-World📅 Updated 2026

SQLite – Zero Setup

Python
import sqlite3

conn = sqlite3.connect("myapp.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL
    )
''')

cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
conn.commit()

cursor.execute("SELECT * FROM users WHERE name = ?", ("Alice",))
user = cursor.fetchone()
print(dict(user))
conn.close()

Context Manager Pattern

Python
from contextlib import contextmanager

@contextmanager
def get_db(path="myapp.db"):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

with get_db() as db:
    db.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Bob", "bob@example.com"))
    users = db.execute("SELECT * FROM users").fetchall()

SQLAlchemy ORM

Python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import DeclarativeBase, Session

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

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)

Base.metadata.create_all(engine)

with Session(engine) as session:
    user = User(name="Charlie", email="charlie@example.com")
    session.add(user)
    session.commit()
    all_users = session.query(User).all()
    charlie = session.query(User).filter_by(name="Charlie").first()
    charlie.email = "new@example.com"
    session.commit()
Tip: Use sqlite3 for small apps. Switch to SQLAlchemy + PostgreSQL for concurrent writes or production scale.

Databases From Python: Parameterize or Get Hacked

Python talks to databases through a standard API (DB-API 2.0), so sqlite3, psycopg (Postgres), and others share the same connect/cursor/execute pattern. The single most important rule lives here: never build SQL with string formatting.

import sqlite3
conn = sqlite3.connect("app.db")
cur = conn.cursor()

# ❌ SQL INJECTION — a malicious name can drop your tables
cur.execute(f"SELECT * FROM users WHERE name = '{name}'")

# ✅ parameterized — the driver safely escapes the value
cur.execute("SELECT * FROM users WHERE name = ?", (name,))
rows = cur.fetchall()
conn.commit()          # writes aren't saved until you commit
conn.close()
StepCall
connectconnect(...)
run querycursor.execute(sql, params)
readfetchone() / fetchall()
save writescommit()

SQL injection — the classic vulnerability: if you interpolate user input into a query string, an attacker can inject SQL (e.g. ' OR '1'='1) to read or destroy data. Always pass values as parameters (the ? or %s placeholders) and let the driver escape them. Other essentials: writes need an explicit commit() (or wrap in a transaction and roll back on error); close connections (or use a with block / connection pool). For larger apps, an ORM like SQLAlchemy gives you Python objects instead of raw SQL — and parameterizes automatically.

🏋️ Practical Exercise

Work with a database:

  1. Use the built-in sqlite3 module to create a table and insert a few rows.
  2. Query the rows back with a parameterized SELECT.
  3. Wrap the connection in a context manager so it commits/closes properly.
  4. Define the same table as a SQLAlchemy ORM model and add a record through it.

🔥 Challenge Exercise

Build a small contacts database with SQLite: create the schema, then implement create, read, update, and delete operations using parameterized queries to prevent SQL injection. Use a context manager for connection handling and add basic error handling for constraint violations. Bonus: re-implement the same CRUD with the SQLAlchemy ORM and compare the developer experience.

📋 Summary

  • SQLite is a zero-setup, file-based database built into Python via the sqlite3 module.
  • Always use parameterized queries (? placeholders) — never string formatting — to prevent SQL injection.
  • A transaction groups operations; commit saves them, rollback undoes them.
  • Context managers ensure connections are committed and closed even on errors.
  • An ORM like SQLAlchemy maps tables to Python classes, trading some control for productivity.
  • Server databases (PostgreSQL, MySQL) suit concurrent, large-scale applications.

Interview Questions on Databases

  • What is the difference between SQLite and a server database like PostgreSQL?
  • Why must you use parameterized queries instead of string formatting?
  • What is SQL injection and how do you prevent it?
  • What is an ORM and what are its trade-offs?
  • What is the difference between commit and rollback?
  • Why is connection handling a good fit for context managers?
  • What is a database transaction?
🌐 API Development 🔁 Context Managers 🎯 Django 🏛️ Classes ⚠️ Exception Handling

FAQ

Why must I use parameterized queries? +

Building SQL by concatenating user input lets attackers inject malicious SQL (SQL injection). Parameterized queries send values separately from the query text, so input is treated as data, never executable SQL — and it also handles quoting and types correctly.

When should I use an ORM versus raw SQL? +

An ORM like SQLAlchemy boosts productivity, maps rows to objects, and reduces boilerplate — great for typical application logic. Raw SQL gives maximum control and performance for complex queries. Many projects mix both.

What is a transaction? +

A transaction is a group of database operations that succeed or fail together. You commit to make them permanent or rollback to discard them, which keeps data consistent if something goes wrong mid-way.

Is SQLite suitable for production? +

Yes, for many use cases — embedded apps, small-to-medium websites, prototypes, and local tools. For high write concurrency or distributed access, a server database like PostgreSQL is a better fit.

Advertisement