Database Access

sqlite3 — Built-in Database

Python includes sqlite3 in the standard library — a fully functional SQL database that stores everything in a single file. It's perfect for small applications, prototyping, and learning SQL. No server setup required.

Connecting and Creating Tables

import sqlite3

# Connect (creates file if it doesn't exist)
with sqlite3.connect("myapp.db") as conn:
    cursor = conn.cursor()

    # Create table
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            age INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)

    conn.commit()
    print("Table created!")

Table created!

CRUD Operations

import sqlite3

with sqlite3.connect("myapp.db") as conn:
    cursor = conn.cursor()

    # CREATE — always use parameterized queries!
    cursor.execute(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        ("Alice", "[email protected]", 30)
    )

    # Insert multiple rows
    users = [
        ("Bob", "[email protected]", 25),
        ("Charlie", "[email protected]", 35),
    ]
    cursor.executemany(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        users
    )
    conn.commit()

    # READ
    cursor.execute("SELECT * FROM users WHERE age > ?", (28,))
    for row in cursor.fetchall():
        print(f"  {row}")

    # UPDATE
    cursor.execute(
        "UPDATE users SET age = ? WHERE name = ?",
        (31, "Alice")
    )

    # DELETE
    cursor.execute("DELETE FROM users WHERE name = ?", ("Charlie",))
    conn.commit()

  (1, 'Alice', '[email protected]', 30, '2024-01-15 10:30:00')
  (3, 'Charlie', '[email protected]', 35, '2024-01-15 10:30:00')

# NEVER build SQL with f-strings or string concatenation!
name = "Alice'; DROP TABLE users; --"
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# ALWAYS use parameterized queries (? placeholders)
cursor.execute("SELECT * FROM users WHERE name = ?", (name,))

Dict-like Row Access

import sqlite3

with sqlite3.connect("myapp.db") as conn:
    conn.row_factory = sqlite3.Row  # Enable dict-like access

    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")

    for row in cursor.fetchall():
        print(f"  {row['name']} ({row['email']}), age {row['age']}")

  Alice ([email protected]), age 31
  Bob ([email protected]), age 25

SQLAlchemy — The Python ORM

For larger applications, an ORM (Object-Relational Mapper) lets you work with database records as Python objects. SQLAlchemy is the most popular Python ORM:

pip install sqlalchemy

Defining Models

from sqlalchemy import create_engine, Column, Integer, String, DateTime
from sqlalchemy.orm import declarative_base, Session
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    age = Column(Integer)
    created_at = Column(DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f"User(id={self.id}, name='{self.name}')"

# Create engine and tables
engine = create_engine("sqlite:///app.db", echo=False)
Base.metadata.create_all(engine)

ORM CRUD

from sqlalchemy.orm import Session

with Session(engine) as session:
    # Create
    alice = User(name="Alice", email="[email protected]", age=30)
    session.add(alice)
    session.commit()
    print(f"Created: {alice}")

    # Read
    user = session.query(User).filter_by(name="Alice").first()
    print(f"Found: {user}")

    all_users = session.query(User).filter(User.age > 25).all()
    print(f"Users over 25: {all_users}")

    # Update
    user.age = 31
    session.commit()

    # Delete
    session.delete(user)
    session.commit()

Created: User(id=1, name='Alice')
Found: User(id=1, name='Alice')
Users over 25: [User(id=1, name='Alice')]

Relationships

ORMs shine when modeling relationships between tables:

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    title = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey("users.id"))

    # Relationship — access author as Python object
    author = relationship("User", back_populates="posts")

# Add reverse relationship to User
User.posts = relationship("Post", back_populates="author")

# Usage
with Session(engine) as session:
    alice = User(name="Alice", email="[email protected]", age=30)
    post = Post(title="My First Post", author=alice)
    session.add(post)
    session.commit()

    # Navigate relationships naturally
    print(f"{post.author.name} wrote '{post.title}'")

Alice wrote 'My First Post'