SQLAlchemy in Python: Tutorial, ORM & CRUD Operations

What Is SQLAlchemy in Python?

SQLAlchemy is an open-source SQL toolkit and Object Relational Mapper (ORM) for Python. It connects Python applications with relational databases such as SQLite, PostgreSQL, MySQL, and Oracle.

An ORM maps database tables to Python classes and database rows to Python objects. This allows developers to create, retrieve, update, and delete database records through Python code.

For example, a database table named users can be represented by a Python class called User. Each instance of that class can represent a user record.

SQLAlchemy supports two main approaches:

• SQLAlchemy Core: Builds SQL expressions and provides direct access to tables, connections, and transactions.
• SQLAlchemy ORM: Maps Python classes to database tables and manages records through sessions.

These approaches can be used independently or together, depending on the application’s requirements.

SQLAlchemy Core vs ORM

Understanding the difference between SQLAlchemy Core and ORM helps developers choose the right approach for a project.

For example, SQLAlchemy Core can select data directly from a table, while the ORM can retrieve records as Python objects.

Both approaches use SQLAlchemy’s underlying database connectivity and SQL expression capabilities.

Installing SQLAlchemy and Creating a Database Engine

Install SQLAlchemy using pip:

pip install sqlalchemy

For this example, SQLite is used because it does not require a separate database server.

Create a database engine:

from sqlalchemy import create_engine

engine = create_engine(
    "sqlite:///blog.db",
    echo=True
)

The create_engine() function configures the database connection interface and manages connections through a connection pool where appropriate.

The sqlite:///blog.db URL specifies a SQLite database file named blog.db in the current working directory.

The echo=True parameter prints SQL statements and related logging information, making it useful for learning and debugging.

Defining SQLAlchemy Models in Python

A SQLAlchemy ORM model is a Python class mapped to a database table. In SQLAlchemy 2.0, the declarative mapping style uses DeclarativeBase, Mapped, and mapped_column().

The following example defines a User model and a Post model with a one-to-many relationship.

from sqlalchemy import String, ForeignKey
from sqlalchemy.orm import (
    DeclarativeBase,
    Mapped,
    mapped_column,
    relationship
)

class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    email: Mapped[str] = mapped_column(
        String(100),
        unique=True
    )

    posts: Mapped[list["Post"]] = relationship(
        back_populates="author"
    )


class Post(Base):
    __tablename__ = "posts"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(120))

    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id")
    )

    author: Mapped["User"] = relationship(
        back_populates="posts"
    )


Base.metadata.create_all(engine)

Here is what the main components do:

• __tablename__ specifies the database table name.
• mapped_column() defines mapped columns and their properties.
• primary_key=True identifies the primary key column.
• ForeignKey("users.id") links a post to a user.
• relationship() defines the object-level relationship between the models.
• Base.metadata.create_all(engine) creates the mapped tables that do not already exist.

A user can have multiple posts, while each post references one user. The back_populates argument connects both sides of the relationship.

SQLAlchemy Session Management

A SQLAlchemy session manages ORM objects and coordinates database operations within a unit of work. It tracks changes to objects and flushes those changes to the database when required.

The following example creates a user and a related post, commits the transaction, and retrieves the user.

from sqlalchemy import select
from sqlalchemy.orm import Session

with Session(engine) as session:
    user = User(
        name="Naveen",
        email="naveen@example.com"
    )

    user.posts.append(
        Post(title="Learning SQLAlchemy")
    )

    session.add(user)
    session.commit()

    stmt = select(User).where(
        User.name == "Naveen"
    )

    found = session.scalars(stmt).first()

    if found:
        print(found.email)
        print(len(found.posts))

The Session context manager closes the session when the block ends. The commit() method commits the transaction, while select() constructs a query using SQLAlchemy’s expression API.

The session also tracks related objects, so the user and post can be persisted together through the same transaction.

In production applications, use short-lived sessions and handle database errors appropriately. If a flush or commit fails, call session.rollback() before reusing that session.

SQLAlchemy CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These four operations form the foundation of most database-driven applications.

1. Create Records

Add a new object to the session and commit it.

with Session(engine) as session:
    user = User(
        name="Priya",
        email="priya@example.com"
    )

    session.add(user)
    session.commit()

2. Read Records

Use select() to retrieve data from the database.

with Session(engine) as session:
    stmt = select(User).where(
        User.name == "Priya"
    )

    user = session.scalars(stmt).first()

    if user:
        print(user.email)

3. Update Records

Load the record, modify its attributes, and commit the changes.

with Session(engine) as session:
    user = session.scalars(
        select(User).where(User.name == "Priya")
    ).first()

    if user:
        user.name = "Priya Sharma"
        session.commit()

SQLAlchemy detects the modified attribute and generates the appropriate UPDATE statement when changes are flushed.

4. Delete Records

Use session.delete() to mark a persistent object for deletion.

with Session(engine) as session:
    user = session.scalars(
        select(User).where(
            User.name == "Priya Sharma"
        )
    ).first()

    if user:
        session.delete(user)
        session.commit()

This example deletes the user record. If related posts exist, the database’s foreign-key rules and the ORM relationship configuration determine how deletion is handled. Configure deletion behavior deliberately rather than assuming related records will always be removed automatically.

CRUD Operations at a Glance

SQLAlchemy Relationships and Foreign Keys

Database relationships connect related information stored in separate tables. SQLAlchemy ORM supports common relationship patterns, including one-to-many, many-to-one, one-to-one, and many-to-many.

In the example above, a user can publish multiple posts. The user_id foreign key stores the relationship in the database, while User.posts and Post.author provide convenient object-level navigation.

Relationships are useful in blogging platforms, e-commerce applications, learning management systems, and customer management systems.

For example, an e-commerce database might connect customers to orders and orders to order items. Properly defined foreign keys and ORM relationships help maintain data integrity and make related records easier to query.

SQLAlchemy with Flask and FastAPI

SQLAlchemy is commonly used in Python backend development with frameworks such as Flask and FastAPI.

• With Flask: The Flask-SQLAlchemy extension integrates SQLAlchemy with Flask’s application context and provides convenient database access patterns.
• With FastAPI: A common approach is to provide a session through a dependency function and ensure that it is cleaned up after the request.

Both frameworks benefit from separating database models, request validation, business logic, and API routes. Pydantic is commonly used with FastAPI to validate request data and serialize response data.

For larger applications, this separation makes testing, debugging, and maintaining database operations easier.

SQLAlchemy Best Practices

Following good database practices helps make SQLAlchemy applications more reliable, secure, and maintainable.

• Use short-lived sessions: Create an appropriate session scope for each request or unit of work instead of sharing one global session across requests.
• Handle transaction failures: Roll back a session after a failed flush or commit before attempting further operations.
• Use migrations: Manage schema changes with Alembic rather than relying on create_all() to modify existing tables.
• Avoid the N+1 query problem: Use loading strategies such as selectinload() when retrieving collections of related objects.
• Use parameterized expressions: Prefer SQLAlchemy expressions over manually concatenating untrusted input into SQL strings.
• Configure relationships carefully: Choose appropriate foreign-key constraints, loading strategies, and deletion behavior.
• Manage transactions deliberately: Commit successful changes and roll back failed transactions to preserve data consistency.

These practices are particularly important in production applications where multiple users, concurrent requests, and complex database relationships are involved.

Common SQLAlchemy Mistakes Beginners Should Avoid

Understanding these details early helps developers build safer and more predictable Python database applications.

Conclusion

With these concepts in place, SQLAlchemy becomes a practical tool for building maintainable, database-driven Python applications.