Python Academy · Lesson

SQLAlchemy Core vs ORM

Understand the two layers.

SQLAlchemy Core vs ORM is a free Python Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What is SQLAlchemy?

SQLAlchemy is the most popular Python toolkit for working with relational databases. It offers two layers: Core and the ORM.

You install it with pip install sqlalchemy.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
print('Engine:', engine)

The Engine

Both layers start with an Engine, created by create_engine(). It manages the connection pool and knows how to talk to your specific database via a URL.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///app.db')
print('Dialect:', engine.dialect.name)

The Two Layers

Core is a SQL expression toolkit: you build queries that map closely to SQL.

The ORM sits on top of Core and maps Python classes to tables, so you work with objects instead of rows.

# Core: think in tables and SQL expressions
# ORM: think in Python classes and objects
print('Core = SQL toolkit, ORM = object mapping')

Core: Defining a Table

In Core you describe tables with Table and MetaData objects explicitly.

from sqlalchemy import MetaData, Table, Column, Integer, String
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String))
print('Table defined:', users.name)

Core: Building a Query

Core builds queries with Python expressions like select() that translate directly to SQL.

from sqlalchemy import MetaData, Table, Column, Integer, String, select
metadata = MetaData()
users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String))
stmt = select(users).where(users.c.name == 'Alice')
print(stmt)

ORM: A Mapped Class

In the ORM, a Python class represents a table. Each attribute maps to a column. You query and save objects, not raw rows.

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
print('Mapped class:', User.__tablename__)

When to Use Core

Core fits when you want fine control over the exact SQL, do bulk data work, or run complex reporting queries where objects add overhead.

# Core is great for:
# - bulk inserts/updates
# - complex analytical queries
# - close control over generated SQL
print('Core: control and performance')

When to Use the ORM

The ORM fits typical application code: you model business entities as classes, get change tracking, and write less boilerplate.

# ORM is great for:
# - application domain models
# - automatic change tracking
# - relationships between objects
print('ORM: productivity and clarity')

They Share the Engine

Because the ORM is built on Core, both use the same Engine and connection pool. You can even mix them in one application.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///:memory:')
# Core uses engine.connect()
# ORM uses a Session bound to the same engine
print('One engine powers both layers')

Creating Tables

With the ORM, Base.metadata.create_all(engine) issues the CREATE TABLE statements for all mapped classes.

from sqlalchemy import create_engine
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
class Base(DeclarativeBase):
    pass
class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
print('Tables created')

Choosing a Layer

Most apps default to the ORM for everyday work, then drop to Core for the rare heavy or highly tuned query. Knowing both gives you flexibility.

# Rule of thumb:
# Start with the ORM. Use Core where you need raw power.
print('Default ORM, escape to Core when needed')

Quick Check

Test your understanding of the two layers.

Recap

You met SQLAlchemy's architecture.

The Engine powers everything via create_engine()
Core is a SQL expression toolkit for control and performance
The ORM maps classes to tables for productive app code
Both share the same engine and can be mixed

Frequently asked questions

Is the “SQLAlchemy Core vs ORM” lesson free?

Yes — the full text of “SQLAlchemy Core vs ORM” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “SQLAlchemy Core vs ORM”?

Understand the two layers. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SQLAlchemy Core vs ORM” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy