Skip to content

SQLAlchemy

Python's definitive database toolkit — a query builder and an ORM, usable separately.

Data & AnalyticsDataPython
Official docs ↗GitHub ↗

What it is

SQLAlchemy is a Python SQL toolkit and Object-Relational Mapping (ORM) library that gives developers full power and flexibility of SQL along with a high-level, Pythonic interface to relational databases.

SQLAlchemy allows you to define database schemas as Python classes (ORM), execute raw SQL queries, and manage connections. It supports multiple relational databases such as SQLite, PostgreSQL, MySQL, and Oracle.

Watch for
The 2.0 style differs substantially from the 1.x tutorials still widely indexed
Licence
MIT

When to use it

The question documentation cannot answer for you — because it cannot recommend something else.

Reach for it when

  • You need real control over the SQL your application generates
  • The schema is complex, with joins and relationships an ORM would otherwise obscure
  • Building on FastAPI or Flask, which have no ORM of their own

Look elsewhere when

  • You are inside Django, which has its own tightly integrated ORM
  • The task is a handful of simple queries where the driver alone would do

Installation

pip install sqlalchemy

Getting started

The smallest useful thing you can do with it, and what each part means.

Creating an Engine and connecting to a database
from sqlalchemy import create_engine
engine = create_engine('sqlite:///example.db')
Creates an engine that connects to a SQLite database file named `example.db`.
Defining a table using ORM
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
Defines a `User` table with columns `id`, `name`, and `age` using the ORM approach.
Creating tables in the database
Base.metadata.create_all(engine)
Creates all tables defined by ORM classes in the connected database.

Advanced usage

Where the library earns its place over a simpler alternative.

Inserting data
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
new_user = User(name='Alice', age=25)
session.add(new_user)
session.commit()
Creates a session, adds a new user to the table, and commits the transaction.
Querying data
users = session.query(User).filter_by(name='Alice').all()
for user in users:
    print(user.name, user.age)
Queries the `User` table for rows where name is 'Alice' and prints the results.
Updating data
user = session.query(User).filter_by(name='Alice').first()
user.age = 26
session.commit()
Updates the age of the first user named 'Alice' and commits the change.
Deleting data
user = session.query(User).filter_by(name='Alice').first()
session.delete(user)
session.commit()
Deletes the user named 'Alice' from the database.

Errors and fixes

The failures you are most likely to hit, and what actually resolves them.

IntegrityError
Occurs when database constraints are violated. Ensure unique or foreign key constraints are respected.
OperationalError
Check database connectivity, credentials, and correct SQL syntax.
ProgrammingError
Typically arises from invalid queries or table definitions. Verify table and column names.

Best practices

  • Use `sessionmaker` to create sessions instead of raw connections.
  • Close sessions after use to prevent resource leaks.
  • Use ORM for complex applications and raw SQL for optimized queries when needed.
  • Define relationships using `relationship` and `ForeignKey` for normalized schemas.
  • Use transactions to ensure data integrity.

Alternatives

Comparable options, and the reason you would pick one over the other.

Background

Why it exists, and what it was reacting to.

SQLAlchemy was created by Mike Bayer in 2005 to provide a powerful and flexible way to interact with relational databases in Python. It allows developers to use both raw SQL and ORM abstractions, making it suitable for both small projects and large-scale enterprise applications.

Related libraries

Others in Data & Analytics for Python.

← All Python librariesPython guide