How to Get Started with SQLAlchemy in Python 2026

Introduction

SQLAlchemy is the reference ORM (Object-Relational Mapping) for Python, used by millions of developers to interact with relational databases without writing raw SQL. In 2026, its 2.x version offers modern, performant, type-safe syntax, ideal for web apps (FastAPI, Django) or data scripts.

Why choose it? Unlike native SQL, SQLAlchemy abstracts database dialects (SQLite, PostgreSQL, MySQL), handles migrations, and prevents SQL injection. Imagine mapping your Python objects directly to tables: a User becomes a database row without boilerplate.

This beginner tutorial takes you from the basics (installation, engine) to advanced CRUD operations with SQLite (perfect for testing without a server). At the end, you'll have a fully executable script ready for production. Time: 15 minutes. Ready to turn your data into objects? (128 words)

Prerequisites

Python 3.10 or higher installed
pip (Python package manager)
Basic Python knowledge (classes, functions)
An editor like VS Code
No external database required (we use embedded SQLite)

Installing SQLAlchemy

terminal

pip install sqlalchemy

This command installs SQLAlchemy 2.x via pip, including everything for ORM, engine, and database dialects. Check with pip show sqlalchemy: version >=2.0 required for this modern syntax. Avoid pip install --user in production—use virtual environments (venv recommended).

Understanding the Engine and Database

The engine is the heart of SQLAlchemy: it manages database connections. The declarative base defines your models. We use create_engine with SQLite (sqlite:///monapp.db) for a local file. Sessions encapsulate transactions, acting like a temporary "context" for your database operations.

Configuring the Engine and Session

database.py

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

# SQLite Engine (local file)
engine = create_engine("sqlite:///users.db", echo=True)

# Base for models
Base = declarative_base()

# Session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Helper function to get a session
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

print("Engine and session configured!")

This code creates a persistent SQLite engine in users.db, an ORM base, and a session factory. echo=True logs SQL for debugging (disable in production). get_db() is a generator for FastAPI-style usage. Pitfall: forget finally.close() and you'll leak connections.

Defining Your First Model

A model is a Python class inheriting from Base. Use Column for fields: Integer(primary_key=True) for auto-incrementing IDs. SQLAlchemy infers types and constraints. We'll call it User with name, email, and age.

Defining the User Model

models.py

from sqlalchemy import Column, Integer, String
from database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, index=True)
    email = Column(String, unique=True, index=True)
    age = Column(Integer, default=18)

print(User.__tablename__)  # Verification

This model maps to a users table with indexes for query performance. unique=True on email prevents duplicates. default=18 for age. Run it to see __tablename__. Pitfall: without index=True on frequently filtered fields, queries slow down.

Creating Tables in the Database

create_tables.py

from database import engine, Base
from models import User

# Create all tables from models
Base.metadata.create_all(bind=engine)
print("Tables created successfully!")

metadata.create_all() inspects models and creates tables if missing. Great for development; use Alembic for migrations in production. Run once. Pitfall: rerunning won't crash, but use drop_all() to reset (careful with data!).

Basic CRUD Operations

CREATE: add objects to a session then commit(). READ: query.filter(). UPDATE: modify and commit. DELETE: delete() then commit. Always use a transactional session.

CREATE and READ (Insert and List)

crud_create_read.py

from database import SessionLocal, get_db
from models import User

db = SessionLocal()

# CREATE
user1 = User(name="Alice", email="alice@example.com", age=25)
user2 = User(name="Bob", email="bob@example.com", age=30)
db.add(user1)
db.add(user2)
db.commit()
db.refresh(user1)  # Reload generated ID

# READ
users = db.query(User).all()
for user in users:
    print(f"ID: {user.id}, Name: {user.name}, Email: {user.email}")

db.close()

Batch add with add(), commit to persist. query.all() lists everything; refresh() syncs the object. Runnable after tables. Pitfall: no commit() means nothing saved! Always close the session.

UPDATE and DELETE

crud_update_delete.py

from database import SessionLocal
from models import User

db = SessionLocal()

# UPDATE: find by ID, modify, commit
user = db.query(User).filter(User.id == 1).first()
if user:
    user.age = 26
    db.commit()
    print(f"User {user.name} updated.")

# DELETE
user_to_del = db.query(User).filter(User.email == "bob@example.com").first()
if user_to_del:
    db.delete(user_to_del)
    db.commit()
    print("User deleted.")

# Check remaining
db.refresh(user)
print(f"Remaining users: {db.query(User).count()}")

db.close()

Filter with filter() and first(). Update directly on the object, delete similarly. count() for stats. Pitfall: filter() returns a query, not an object; use one() for uniques or handle None.

Best Practices

Always use sessions with context managers (with db:) for auto-close.
Migrations with Alembic: pip install alembic, init, and autogenerate for production.
Validation: integrate Pydantic for inputs before DB.
Indexes and relationships: add relationship() for foreign keys.
Async: switch to create_async_engine for async apps (FastAPI).
Environments: store DB URL in env vars (python-dotenv).

Common Errors to Avoid

Forgetting commit(): data stays in memory, lost on close.
Unclosed sessions: connection leaks, pool crashes (default limit ~5).
No indexes on queried fields: poor performance on large volumes (>10k rows).
SQL injection: always use ORM queries, never f-strings in raw SQL.

Next Steps

Official docs: SQLAlchemy 2.0
Alembic migrations: Alembic Guide
Integrate with FastAPI: Learni tutorial coming soon!
Expert training: Check out Learni's courses on advanced Python and databases.

How to Get Started with SQLAlchemy in Python in 2026