SQLAlchemy: Turn Your Python Classes Into Database Tables

Picture a bike repair shop. The owner keeps one notebook for customers, another for bikes, and a third for repair jobs. Each week she works out who owes what, matching names across three notebooks. A spreadsheet helps at first, until the same customer appears under two spellings and a paid job gets counted twice. It is all there, but the links between the pieces live only in her head. That is the point where a Python developer reaches for SQLAlchemy. It turns customers, bikes, and jobs into Python classes backed by tables, with the relationships written down in code instead of remembered. The question is whether it is the right weight for the job in front of you.

What I would check before using it

My first check is how connected the data is. One table with no relationships does not need an ORM, and a plain sqlite3 script would be simpler. Once you have three tables that point at each other, customers owning bikes and bikes having jobs, and questions that join them, the mapping earns its keep.

My second check is who will read the code. SQLAlchemy lets the team work in Python and treat rows as objects, which suits a team that thinks in classes. But it adds its own ideas, sessions and lazy loading, and a developer who only knows SQL has to learn those before they can debug anything.

My third check is how the database will change over time. Adding a column is easy on day one and painful on day one hundred, when real customer data is already in the tables. SQLAlchemy pairs with Alembic, its migration tool, so schema changes become versioned scripts instead of hand-edited tables.

My fourth check is performance. The ORM is fast enough for a shop like this one and slow enough to notice at scale. The classic trap is the N+1 query: loading fifty bikes and then touching each bike's jobs fires fifty extra queries. A reporting dashboard that scans millions of rows belongs in raw SQL.

My verdict, as judgement: use SQLAlchemy when a Python app owns several related tables and the code will keep growing with them. Skip it for a one-off script, a single table, or a heavy reporting job. The recommendation flips when the data flattens to one table, or when the team needs to squeeze speed out of bulk queries.

One bike shop, run end to end

The example is a small bike repair shop with three tables. A customer has a phone number that must be unique, a bike belongs to one customer, and a repair job belongs to one bike and carries a cost and a status of paid or unpaid. The models below are the whole schema, written as Python classes, and the relationships are what make the join query possible. The script uses a local SQLite file, so it runs anywhere with nothing to install except SQLAlchemy itself.

from sqlalchemy import Column, Float, ForeignKey, Integer, String, create_engine, func from sqlalchemy.orm import declarative_base, relationship, sessionmaker Base = declarative_base() class Customer(Base): __tablename__ = "customers" id = Column(Integer, primary_key=True) name = Column(String, nullable=False) phone = Column(String, unique=True, nullable=False) bikes = relationship("Bike", back_populates="owner") class Bike(Base): __tablename__ = "bikes" id = Column(Integer, primary_key=True) brand = Column(String, nullable=False) model = Column(String, nullable=False) owner_id = Column(Integer, ForeignKey("customers.id"), nullable=False) owner = relationship("Customer", back_populates="bikes") jobs = relationship("RepairJob", back_populates="bike") class RepairJob(Base): __tablename__ = "repair_jobs" id = Column(Integer, primary_key=True) bike_id = Column(Integer, ForeignKey("bikes.id"), nullable=False) description = Column(String, nullable=False) cost = Column(Float, nullable=False) status = Column(String, nullable=False) # "unpaid" or "paid" bike = relationship("Bike", back_populates="jobs") engine = create_engine("sqlite:///shop.db") Base.metadata.create_all(engine) session = sessionmaker(bind=engine)()

The shop has three customers. Ayesha Khan owns a Trek Marlin 7 and a Giant Escape 3, Bilal Ahmed owns a Hero Ranger 26, and Sara Iqbal owns a Specialized Sirrus X. Six repair jobs are spread across the bikes, from a Rs 900 puncture repair to a Rs 6,500 chain and cassette replacement. Two jobs are already paid, and the rest are outstanding. The question the owner actually asks each week is how much each customer still owes, and that is a join across all three tables: customers to their bikes, bikes to their jobs, filtered to unpaid jobs, grouped by customer.

The thing that went wrong came before the query. A second record for Ayesha was added with the same phone number, 0301-5550142, which the unique constraint on the phone column forbids. Here is the real output from the run:

Seeded 3 customers, 4 bikes, 6 repair jobs. Adding a duplicate customer with Ayesha's phone number... IntegrityError: UNIQUE constraint failed: customers.phone Customers after the failed insert: 3 Outstanding repair cost per customer: - Ayesha Khan: Rs 8,300 outstanding - Bilal Ahmed: Rs 1,600 outstanding

The duplicate never reached the database, and the customer count stayed at 3, because the code caught the error and called session.rollback(). Without that rollback the session stays broken and the next query fails, so catching the error is only half the fix. The other half is checking for an existing customer by phone before inserting a new one, which is how the shop would stop the same customer being entered twice in real use. After the rollback the join query ran cleanly and gave the answer the owner needed: Ayesha owes Rs 8,300, from an unpaid brake job at Rs 1,800 and an unpaid chain replacement at Rs 6,500, while Bilal owes Rs 1,600. Sara does not appear in the list at all, and that is correct, because her only job, a full service at Rs 3,500, is already paid. The query only joins rows that exist, so a customer with no unpaid jobs simply has no row, and a report that needs to show zero balances would use an outer join instead.

Common mistakes and how to fix them

First, forgetting session.commit(). The symptom is data that works fine during the run and vanishes afterwards, because objects added to a session are only staged until they are committed. The fix is to commit after each unit of work and check the database file afterwards.

Second, using objects after the session closes. The symptom is DetachedInstanceError when you touch a relationship on an object whose session is gone. The fix is to load everything you need, including related rows, before closing the session, or keep the session open until the work is done.

Third, skipping the rollback after an error. The symptom is PendingRollbackError on the very next query after an IntegrityError, even though the bad row was never saved. The fix is to call session.rollback() in the except block, every time, before doing anything else with that session.

Fourth, loading related rows one by one. The symptom is a page that slows down as the data grows, because each bike triggers its own query for its jobs. The fix is eager loading with joinedload or selectinload, so the related rows arrive in one or two queries instead of dozens.

My call on SQLAlchemy

SQLAlchemy is at its best when the data has shape: tables that point at each other, rules like a unique phone number that belong in the database, and questions that join across those tables. The bike shop needed a short set of models, a session, and one query, and the duplicate phone number was caught by the database. For a single flat table or a report that scans millions of rows, plain SQL is shorter and faster. If your data looks like this shop, try it on one small set of tables and see whether the code reads more clearly than the SQL it replaces. If it does, keep going.