Course topics

By WebNest Studio Engineering Team

Python Tutorial

SQLAlchemy Core and ORM

Writing SQL by hand is fine for small scripts, but larger applications benefit from SQLAlchemy, the most popular Python database toolkit. It has two layers: Core, a Pythonic way to build and run SQL against any database, and the ORM (Object-Relational Mapper), which maps database tables to Python classes so you work with objects instead of rows.

The same code runs on SQLite, PostgreSQL, MySQL, SQL Server and Oracle — only the connection URL changes. This lesson uses the modern SQLAlchemy 2.0 style with type-annotated models. FastAPI, Flask and many other frameworks are commonly paired with SQLAlchemy.

SQLAlchemy is not available in the browser runner; install it with pip install sqlalchemy and run the examples locally. They use SQLite, so no server is needed.

Why it matters

Most Python applications do not write SQL by hand for every query. An ORM maps tables to classes and rows to objects, so data is handled as ordinary Python objects, and the same code runs on different databases. SQLAlchemy is the standard ORM outside Django and is the usual partner of FastAPI and Flask.

Engine, Connection and Session

create_engine(url) creates an engine that manages a pool of connections. URLs look like sqlite:///app.db, postgresql+psycopg://user:pass@host/db or mysql+pymysql://user:pass@host/db. With Core you use engine.connect() / engine.begin() and text() or select(). With the ORM you use a Session: it tracks the objects you add or change and writes them to the database when you commit().

Declaring Models

Models subclass a DeclarativeBase. Each attribute is annotated with Mapped[type] and optionally configured with mapped_column() (primary keys, lengths, uniqueness, foreign keys). relationship() links models, for example one author to many books, with back_populates keeping both sides in sync. Base.metadata.create_all(engine) creates the tables; in real projects, use Alembic migrations to evolve the schema.

Querying

Build queries with select(Model).where(...).order_by(...).limit(...) and run them with session.scalars(query) (model objects) or session.execute(query) (rows). .one(), .first() and .all() fetch results; session.get(Model, id) loads by primary key. Joins and aggregates use .join(), .group_by() and func.count() / func.sum(). Values are always sent as bound parameters, so SQLAlchemy queries are safe from SQL injection.

Examples

SQLAlchemy Core: engine, text() and bound parameters

Python
# pip install sqlalchemy
from sqlalchemy import create_engine, text

engine = create_engine("sqlite:///:memory:")
# PostgreSQL: create_engine("postgresql+psycopg://user:password@localhost:5432/shop")
# MySQL:      create_engine("mysql+pymysql://user:password@localhost:3306/shop")

with engine.begin() as conn:                     # begin() commits at the end of the block
    conn.execute(text("CREATE TABLE stores (city TEXT, revenue INTEGER)"))
    conn.execute(text("INSERT INTO stores VALUES (:city, :revenue)"), [
        {"city": "Pune", "revenue": 820000},
        {"city": "Nashik", "revenue": 310000},
        {"city": "Nagpur", "revenue": 540000},
    ])

with engine.connect() as conn:
    result = conn.execute(text("SELECT city, revenue FROM stores WHERE revenue > :minimum ORDER BY revenue DESC"),
                          {"minimum": 400000})
    for row in result:
        print(row.city, row.revenue)
Output
Pune 820000
Nagpur 540000

ORM models, relationships and full CRUD

Python
from sqlalchemy import create_engine, select, func, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, Session

class Base(DeclarativeBase):
    pass

class Author(Base):
    __tablename__ = "authors"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), unique=True)
    books: Mapped[list["Book"]] = relationship(back_populates="author", cascade="all, delete-orphan")

    def __repr__(self):
        return f"Author({self.name!r})"

class Book(Base):
    __tablename__ = "books"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    price: Mapped[int]
    author_id: Mapped[int] = mapped_column(ForeignKey("authors.id"))
    author: Mapped[Author] = relationship(back_populates="books")

    def __repr__(self):
        return f"Book({self.title!r}, {self.price})"

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # Create
    asha = Author(name="Asha", books=[Book(title="Python Basics", price=450),
                                      Book(title="Python Testing", price=599)])
    ravi = Author(name="Ravi", books=[Book(title="Deep Python", price=899)])
    session.add_all([asha, ravi])
    session.commit()
    print("ids:", asha.id, ravi.id)

    # Read
    book = session.scalars(select(Book).where(Book.title == "Deep Python")).one()
    print(book, "by", book.author.name)

    # Update: change the attribute, then commit
    book.price = 799
    session.commit()

    cheap = session.scalars(select(Book).where(Book.price < 700).order_by(Book.price)).all()
    print(cheap)

    stats = session.execute(
        select(Author.name, func.count(Book.id), func.sum(Book.price))
        .join(Author.books)
        .group_by(Author.name)
        .order_by(Author.name)
    ).all()
    print(stats)

    # Delete: the cascade removes Asha's books too
    session.delete(asha)
    session.commit()
    print(session.scalars(select(Book)).all())
Output
ids: 1 2
Book('Deep Python', 899) by Ravi
[Book('Python Basics', 450), Book('Python Testing', 599)]
[('Asha', 2, 1049), ('Ravi', 1, 799)]
[Book('Deep Python', 799)]

A session factory, get(), and rolling back on errors

Python
from sqlalchemy import create_engine, String
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)

engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
SessionLocal = sessionmaker(bind=engine)          # create once, reuse everywhere

def register(email):
    with SessionLocal() as session:
        try:
            user = User(email=email)
            session.add(user)
            session.commit()
            return f"registered {email} as user {user.id}"
        except IntegrityError:
            session.rollback()
            return f"{email} is already registered"

print(register("asha@example.com"))
print(register("asha@example.com"))
with SessionLocal() as session:
    print(session.get(User, 1).email, session.get(User, 99))
Output
registered asha@example.com as user 1
asha@example.com is already registered
asha@example.com None

Schema migrations with Alembic

Python
pip install alembic
alembic init migrations                 # creates alembic.ini and migrations/
# set sqlalchemy.url in alembic.ini and target_metadata = Base.metadata in migrations/env.py
alembic revision --autogenerate -m "create authors and books"
alembic upgrade head                    # apply all migrations
alembic downgrade -1                    # undo the last one
Output
INFO  [alembic.runtime.migration] Running upgrade  -> 3f2a1c9d8e7b, create authors and books

Common Mistakes

  • Mixing the legacy 1.x query API (session.query) with 2.0-style select() without understanding the difference.
  • Forgetting session.commit(), so changes are lost when the session closes.
  • Triggering the "N+1 queries" problem by accessing relationships in a loop — use selectinload() or joinedload().
  • Using create_all() to change existing tables; it only creates missing tables — use Alembic migrations.
  • Sharing one Session across threads or web requests instead of one session per unit of work.

Exercise

Using SQLAlchemy 2, which must be installed with pip install sqlalchemy, define a User model, create its table in an in-memory SQLite database, add two users, and print their names in alphabetical order.

Expected output: ['Asha', 'Ravi']

Python · starter code
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


# TODO: class User(Base) with __tablename__ "users", an integer primary key id
#       and a name column


engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

with Session(engine) as session:
    # TODO: add users named "Ravi" and "Asha", and commit
    # TODO: select the names ordered by name, and print them as a list
    pass
Show hints
  • Columns are declared as id: Mapped[int] = mapped_column(primary_key=True).
  • session.scalars(select(User.name).order_by(User.name)).all() returns a list of the names.
Show solution
Python · solution
from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "users"

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


engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add_all([User(name="Ravi"), User(name="Asha")])
    session.commit()

    names = session.scalars(select(User.name).order_by(User.name)).all()
    print(names)

Quick quiz

1. What does an ORM do?

  • A. Maps database tables to classes and rows to objects
  • B. Replaces the database
  • C. Encrypts the data
  • D. Speeds up every query
Show answer

A. It generates the SQL from operations on objects.

2. In SQLAlchemy, which object tracks changes to objects and sends them to the database?

  • A. The Engine
  • B. The Session
  • C. The Model
  • D. The Metadata
Show answer

B. The engine manages connections; the session is the unit of work.

3. What is Alembic used for?

  • A. Testing queries
  • B. Connection pooling
  • C. Database schema migrations
  • D. Caching results
Show answer

C. It records each change to the schema as a versioned script that can be applied or reversed.

Interview questions

What are the advantages and disadvantages of an ORM?

Show answer

Advantages: less repetitive SQL, queries built from Python objects, protection against SQL injection by default, the same code working across databases, and relationships between tables available as attributes. Disadvantages: it hides what SQL is actually run, so inefficient queries are easy to write without noticing; complex queries can be harder to express; and there is some overhead. Knowing SQL remains necessary.

What is the N+1 query problem?

Show answer

It happens when code loads a list of N objects with one query and then touches a related attribute on each, causing one more query per object: N+1 queries where one or two would do. It is slow and easy to miss, because the code looks innocent. The fix is to load the related data up front, in SQLAlchemy with options such as selectinload or joinedload.

Key Points to Remember

  • SQLAlchemy Core builds and runs SQL; the ORM maps tables to classes.
  • create_engine(url) works with SQLite, PostgreSQL, MySQL and more — only the URL changes.
  • Declare models with DeclarativeBase, Mapped[...] and mapped_column(); link them with relationship().
  • Query with select(...) and session.scalars()/execute(); commit to save changes.
  • Use sessionmaker for sessions and Alembic for schema migrations.

Practice in the playground

Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.

Run the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.

WebNest Studio

Written by

WebNest Studio Engineering Team

Practical lessons from developers working with Java, Python, React, databases and production systems.

Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering