How do I interact with databases using SQLAlchemy in Python?

· Category: Python Programming

Short answer

SQLAlchemy is the most popular SQL toolkit and ORM for Python. It provides an ORM layer for mapping classes to tables and a Core layer for constructing SQL expressions programmatically.

Steps

  1. Install SQLAlchemy: pip install sqlalchemy.
  2. Create an engine connected to your database.
  3. Define mapped classes and create tables.
  4. Use sessions to add, query, and commit records.
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

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

engine = create_engine("sqlite:///example.db", echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

session.add(User(name="Alice"))
session.commit()

print(session.query(User).all())

Tips

  • Use Alembic for database migrations in production.
  • SQLAlchemy 2.0+ uses new query syntax (select(), Session.execute()) for better type safety.
  • Connection pooling is built-in and configurable via the engine.

Common issues

  • Forgetting to call session.commit() leaves changes in memory only.
  • N+1 query problems occur when accessing related objects inside a loop without eager loading (joinedload).
  • SQLite does not support some advanced features like ALTER COLUMN; use PostgreSQL or MySQL for production.