Most applications store data in databases. Python connects to SQL and NoSQL databases through built-in modules and third-party libraries.

SQLite — Built-In, Zero Config

Perfect for development, prototypes, and embedded apps:

  import sqlite3

conn = sqlite3.connect("app.db")
conn.row_factory = sqlite3.Row  # dict-like access
cursor = conn.cursor()

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

cursor.execute(
    "INSERT INTO users (username, email) VALUES (?, ?)",
    ("alice", "[email protected]"),
)
conn.commit()

cursor.execute("SELECT * FROM users WHERE username = ?", ("alice",))
user = cursor.fetchone()
print(dict(user))

conn.close()

Always use ? placeholders — never f-strings for SQL values.

Context Manager Pattern 

  with sqlite3.connect("app.db") as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM users")
    count = cursor.fetchone()[0]
    print(f"Total users: {count}")
# Connection auto-committed and closed

SQLAlchemy ORM

The standard ORM for Python — database-agnostic:

  pip install sqlalchemy
  
  from sqlalchemy import create_engine, Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import DeclarativeBase, sessionmaker, relationship
from datetime import datetime

engine = create_engine("sqlite:///app.db")
Session = sessionmaker(bind=engine)

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(100), nullable=False)
    posts = relationship("Post", back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    body = Column(String, nullable=False)
    author_id = Column(Integer, ForeignKey("users.id"))
    author = relationship("User", back_populates="posts")

Base.metadata.create_all(engine)

CRUD with SQLAlchemy 

  with Session() as session:
    # Create
    user = User(username="bob", email="[email protected]")
    session.add(user)
    session.commit()

    # Read
    users = session.query(User).filter(User.username.like("b%")).all()
    user = session.query(User).filter_by(username="bob").first()

    # Update
    user.email = "[email protected]"
    session.commit()

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

PostgreSQL 

  pip install psycopg2-binary sqlalchemy
  
  engine = create_engine("postgresql://user:pass@localhost:5432/mydb")

SQLAlchemy code stays the same — only the connection URL changes.

Raw SQL with SQLAlchemy

When you need full SQL control:

  from sqlalchemy import text

with Session() as session:
    result = session.execute(
        text("SELECT username, COUNT(*) as post_count FROM users u "
             "JOIN posts p ON u.id = p.author_id GROUP BY username")
    )
    for row in result:
        print(row.username, row.post_count)

Migrations with Alembic

Track schema changes over time:

  pip install alembic
alembic init migrations
alembic revision --autogenerate -m "create users table"
alembic upgrade head

Transactions 

  with Session() as session:
    try:
        session.add(User(username="charlie", email="[email protected]"))
        session.add(Post(title="First", body="Hello", author_id=1))
        session.commit()
    except Exception:
        session.rollback()
        raise

Database Best Practices

  1. Use parameterized queries — prevent SQL injection
  2. Use connection pooling in production (SQLAlchemy handles this)
  3. Index frequently queried columns
  4. Use migrations — never modify production schema manually
  5. Close connections — use context managers
  6. Separate config — database URL from environment variables
  import os
DATABASE_URL = os.environ["DATABASE_URL"]
engine = create_engine(DATABASE_URL, pool_size=10, max_overflow=20)

Databases are the persistence layer of virtually every production Python application.