SQLAlchemy ORM Basics in Python0%

SQLAlchemy ORM Basics in Python

Advanced8 min read•Updated: 2026-10-01

SQLAlchemy ORM Basics

In enterprise software engineering, connecting object-oriented application code with relational database schemas is a fundamental challenge. SQLAlchemy is the standard Object-Relational Mapping (ORM) toolkit for Python.

With the release of SQLAlchemy 2.0, the library unified its Core and ORM APIs, introduced first-class support for Python type annotations (Mapped, mapped_column), and transitioned to a declarative SQL-like querying paradigm.

1. The Modern SQLAlchemy 2.0 Declarative Architecture

SQLAlchemy maps Python classes to relational database tables. In 2.0, classes inherit from DeclarativeBase and define column attributes using type-annotated descriptors:

1 Python Application Domain Model          SQLAlchemy 2.0 Mapping           Relational Database Schema
2┌─────────────────────────────────┐      ┌─────────────────────────┐      ┌───────────────────────────┐
3│ class User(DeclarativeBase):    │      │                         │      │ CREATE TABLE users (      │
4│   id: Mapped[int] = ...         │ ───► │ type: Integer, PK       │ ───► │   id INTEGER PRIMARY KEY, │
5│   username: Mapped[str] = ...   │      │ type: String(50), NOT NULL│   │   username VARCHAR(50)... │
6│   email: Mapped[str] = ...      │      │ type: String(120), UNIQUE │   │   email VARCHAR(120)...   │
7└─────────────────────────────────┘      └─────────────────────────┘      └───────────────────────────┘

2. Engine, Metadata, and Session Lifecycle

Three primary architectural constructs manage database operations:

1
Engine: The low-level connection pool and SQL dialect translator. Created via create_engine().
2
Session: The Unit of Work and Identity Map pattern coordinator. It tracks changes to objects and flushes transactions.
3
DeclarativeBase: The root class collecting schema metadata and table definitions.

     Engine (create_engine) ◄─── Connection Pool & Dialect (SQLite/PostgreSQL)
               ▲
               │ Manages Connection
               ▼
     Session (sessionmaker) ◄─── Unit of Work & Identity Map
               │
      ┌────────┴────────┐
      ▼                 ▼
 User Object       Post Object  (Persistent In-Memory Domain Models)

The Four States of an ORM Entity

Transient: Newly instantiated object (User(...)); not associated with any session; has no database identity.
Pending: Added to a session (session.add(u)); not yet flushed to the database.
Persistent: Flushed or queried from the database; has a primary key; tracked in session.
Detached: The session was closed; the object remains in memory, but changes to it are untracked.

3. Production Implementation: Schema Definition & CRUD

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart

Step 1

def __repr__

self

Step 2

str:

4. Modern 2.0 Querying: `select()` and `session.scalars()`

In legacy SQLAlchemy 1.x, querying relied on session.query(User).filter(...).

In SQLAlchemy 2.0, all queries use explicit select() statements:

session.execute(select(User)): Returns a Result containing row tuples (User,).
session.scalars(select(User)): Automatically unwraps single-entity rows into ScalarResult containing User instances directly.

1# Modern 2.0 Query Idioms:
2stmt = select(User).where(User.is_active == True).order_by(User.username)
3active_users = session.scalars(stmt).all()

5. Architectural Summary Table

Construct	Role	2.0 Syntax
Model Base	Defines root declarative metadata	`class Base(DeclarativeBase): pass`
Typed Columns	Declares columns with Python types	`col: Mapped[type] = mapped_column(...)`
Engine	Connection pool & dialect gateway	`create_engine("dialect://user:pass@host/db")`
Session	Unit of Work coordinator	`with Session(engine) as session:`
Querying	Declarative SQL queries	`session.scalars(select(Model).where(...))`

⚠️ Common Advanced Mistakes & Gotchas

1. Mixing SQLAlchemy 1.x and 2.0 Syntax

SQLAlchemy 2.0 deprecates session.query(Model) in favor of declarative select(Model) executed via session.scalars(select(Model)).

2. Leaking Unclosed Sessions

Always manage database sessions with context managers (with Session(engine) as session:) to guarantee sessions and connection pool handles are released back to the pool.

💻 Try It Yourself: Modern SQLAlchemy 2.0 Declarative Model

Scenario

Define a modern SQLAlchemy 2.0 Product model using Mapped and mapped_column, insert records, and query with select().

Complete Solution

1from sqlalchemy import create_engine, select, String, Float
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
3 
4class Base(DeclarativeBase):
5    pass
6 
7class Product(Base):
8    __tablename__ = "products"
9    id: Mapped[int] = mapped_column(primary_key=True)
10    name: Mapped[str] = mapped_column(String(50))
11    price: Mapped[float] = mapped_column(Float)
12 
13engine = create_engine("sqlite:///:memory:")
14Base.metadata.create_all(engine)
15 
16with Session(engine) as session:
17    session.add(Product(name="Ergonomic Keyboard", price=3499.0))
18    session.commit()
19    
20    prod = session.scalars(select(Product).where(Product.name.like("%Keyboard%"))).first()
21    print(f"Found Product: {prod.name} (₹{prod.price:,.2f})")

Expected Output

1Found Product: Ergonomic Keyboard (₹3,499.00)

Interactive Quiz• Knowledge Check

Practice Quiz

1.

How are table columns defined with strict type-safety in modern SQLAlchemy 2.0? A. col = Column(Integer) B. col: Mapped[int] = mapped_column(...) C. col = Field(int) D. col = db.Integer()

Answer: B

Explanation:SQLAlchemy 2.0 introduced Mapped[T] and mapped_column(...) to integrate directly with Python's typing system (PEP 484) and static type checkers like Mypy.

2.

What is the difference between session.execute(select(User)) and session.scalars(select(User))? A. execute() only works for inserts, while scalars() works for selects. B. execute() returns rows of tuples (User,), whereas scalars() unwraps the first column of each row into raw scalar ORM instances. C. scalars() does not support where clauses. D. execute() bypasses the database engine.

Answer: B

Explanation:session.scalars() is a convenience method that automatically extracts the first element from each row tuple, yielding ORM entity instances directly.

3.

What is the state of a newly created ORM object user = User(name="Alex") before session.add(user) is executed? A. Persistent B. Pending C. Transient D. Detached

Answer: C

Explanation:A freshly instantiated ORM object that has not been attached to a session and has no database representation is in the Transient state.

4.

What does Base.metadata.create_all(engine) do? A. Deletes all data from the database. B. Inspects all mapped models registered under Base and issues CREATE TABLE DDL statements for any tables that do not yet exist in the database. C. Compiles Python code to SQLite binaries. D. Drops the database connection pool.

Answer: B

Explanation:create_all() examines the metadata dictionary collected by DeclarativeBase and generates the corresponding schema tables in the target database if they are missing.

5.

Why should SQLAlchemy sessions always be managed using a with Session(engine) as session: context manager block? A. Because Python refuses to compile sessions outside of with blocks. B. To guarantee that database connections are properly closed, pooled, and cleaned up upon exit, avoiding connection leaks. C. To turn on SQLite WAL mode. D. To disable transaction isolation.

Answer: B

Explanation:Using the session context manager guarantees that the session's internal resources and checked-out engine connections are closed and returned to the pool even if unhandled exceptions occur.

🚀 What's Next?

In the next lesson, we will continue your advanced Python journey with Relationships in Databases (9: Advanced Database Handling).

👉 Continue to Next Lesson: Relationships in Databases →

Next Lesson

Relationships in Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Project: Async Web Scraper	Relationships in Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Relationships in Databases

SQLAlchemy ORM Basics in Python

Advanced8 min read•Updated: 2026-10-01

Study Materials Cheatsheet

SQLAlchemy ORM Basics

1. The Modern SQLAlchemy 2.0 Declarative Architecture

SQLAlchemy maps Python classes to relational database tables. In 2.0, classes inherit from DeclarativeBase and define column attributes using type-annotated descriptors:

1 Python Application Domain Model          SQLAlchemy 2.0 Mapping           Relational Database Schema
2┌─────────────────────────────────┐      ┌─────────────────────────┐      ┌───────────────────────────┐
3│ class User(DeclarativeBase):    │      │                         │      │ CREATE TABLE users (      │
4│   id: Mapped[int] = ...         │ ───► │ type: Integer, PK       │ ───► │   id INTEGER PRIMARY KEY, │
5│   username: Mapped[str] = ...   │      │ type: String(50), NOT NULL│   │   username VARCHAR(50)... │
6│   email: Mapped[str] = ...      │      │ type: String(120), UNIQUE │   │   email VARCHAR(120)...   │
7└─────────────────────────────────┘      └─────────────────────────┘      └───────────────────────────┘

2. Engine, Metadata, and Session Lifecycle

Three primary architectural constructs manage database operations:

1
Engine: The low-level connection pool and SQL dialect translator. Created via create_engine().
2
Session: The Unit of Work and Identity Map pattern coordinator. It tracks changes to objects and flushes transactions.
3
DeclarativeBase: The root class collecting schema metadata and table definitions.

     Engine (create_engine) ◄─── Connection Pool & Dialect (SQLite/PostgreSQL)
               ▲
               │ Manages Connection
               ▼
     Session (sessionmaker) ◄─── Unit of Work & Identity Map
               │
      ┌────────┴────────┐
      ▼                 ▼
 User Object       Post Object  (Persistent In-Memory Domain Models)

The Four States of an ORM Entity

Transient: Newly instantiated object (User(...)); not associated with any session; has no database identity.
Pending: Added to a session (session.add(u)); not yet flushed to the database.
Persistent: Flushed or queried from the database; has a primary key; tracked in session.
Detached: The session was closed; the object remains in memory, but changes to it are untracked.

3. Production Implementation: Schema Definition & CRUD

Visual Architecture & Process Flow

How data and code flow step-by-step

Flowchart

Step 1

def __repr__

self

Step 2

str:

4. Modern 2.0 Querying: `select()` and `session.scalars()`

In legacy SQLAlchemy 1.x, querying relied on session.query(User).filter(...).

In SQLAlchemy 2.0, all queries use explicit select() statements:

session.execute(select(User)): Returns a Result containing row tuples (User,).
session.scalars(select(User)): Automatically unwraps single-entity rows into ScalarResult containing User instances directly.

1# Modern 2.0 Query Idioms:
2stmt = select(User).where(User.is_active == True).order_by(User.username)
3active_users = session.scalars(stmt).all()

5. Architectural Summary Table

Construct	Role	2.0 Syntax
Model Base	Defines root declarative metadata	`class Base(DeclarativeBase): pass`
Typed Columns	Declares columns with Python types	`col: Mapped[type] = mapped_column(...)`
Engine	Connection pool & dialect gateway	`create_engine("dialect://user:pass@host/db")`
Session	Unit of Work coordinator	`with Session(engine) as session:`
Querying	Declarative SQL queries	`session.scalars(select(Model).where(...))`

⚠️ Common Advanced Mistakes & Gotchas

1. Mixing SQLAlchemy 1.x and 2.0 Syntax

SQLAlchemy 2.0 deprecates session.query(Model) in favor of declarative select(Model) executed via session.scalars(select(Model)).

2. Leaking Unclosed Sessions

Always manage database sessions with context managers (with Session(engine) as session:) to guarantee sessions and connection pool handles are released back to the pool.

💻 Try It Yourself: Modern SQLAlchemy 2.0 Declarative Model

Scenario

Define a modern SQLAlchemy 2.0 Product model using Mapped and mapped_column, insert records, and query with select().

Complete Solution

1from sqlalchemy import create_engine, select, String, Float
2from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
3 
4class Base(DeclarativeBase):
5    pass
6 
7class Product(Base):
8    __tablename__ = "products"
9    id: Mapped[int] = mapped_column(primary_key=True)
10    name: Mapped[str] = mapped_column(String(50))
11    price: Mapped[float] = mapped_column(Float)
12 
13engine = create_engine("sqlite:///:memory:")
14Base.metadata.create_all(engine)
15 
16with Session(engine) as session:
17    session.add(Product(name="Ergonomic Keyboard", price=3499.0))
18    session.commit()
19    
20    prod = session.scalars(select(Product).where(Product.name.like("%Keyboard%"))).first()
21    print(f"Found Product: {prod.name} (₹{prod.price:,.2f})")

Expected Output

1Found Product: Ergonomic Keyboard (₹3,499.00)

Interactive Quiz• Knowledge Check

Practice Quiz

1.

How are table columns defined with strict type-safety in modern SQLAlchemy 2.0? A. col = Column(Integer) B. col: Mapped[int] = mapped_column(...) C. col = Field(int) D. col = db.Integer()

Answer: B

Explanation:SQLAlchemy 2.0 introduced Mapped[T] and mapped_column(...) to integrate directly with Python's typing system (PEP 484) and static type checkers like Mypy.

2.

Answer: B

Explanation:session.scalars() is a convenience method that automatically extracts the first element from each row tuple, yielding ORM entity instances directly.

3.

What is the state of a newly created ORM object user = User(name="Alex") before session.add(user) is executed? A. Persistent B. Pending C. Transient D. Detached

Answer: C

Explanation:A freshly instantiated ORM object that has not been attached to a session and has no database representation is in the Transient state.

4.

Answer: B

Explanation:create_all() examines the metadata dictionary collected by DeclarativeBase and generates the corresponding schema tables in the target database if they are missing.

5.

Answer: B

🚀 What's Next?

In the next lesson, we will continue your advanced Python journey with Relationships in Databases (9: Advanced Database Handling).

👉 Continue to Next Lesson: Relationships in Databases →

Next Lesson

Relationships in Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Project: Async Web Scraper	Relationships in Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

SQLAlchemy ORM Basics

1. The Modern SQLAlchemy 2.0 Declarative Architecture

2. Engine, Metadata, and Session Lifecycle

The Four States of an ORM Entity

3. Production Implementation: Schema Definition & CRUD

Visual Architecture & Process Flow

4. Modern 2.0 Querying: select() and session.scalars()

5. Architectural Summary Table

⚠️ Common Advanced Mistakes & Gotchas

1. Mixing SQLAlchemy 1.x and 2.0 Syntax

2. Leaking Unclosed Sessions

💻 Try It Yourself: Modern SQLAlchemy 2.0 Declarative Model

Scenario

Complete Solution

Expected Output

Practice Quiz

1.

2.

3.

4.

5.

🚀 What's Next?

Relationships in Databases

Related Lessons

Practice Quiz

Loading Page Content...

SQLAlchemy ORM Basics

1. The Modern SQLAlchemy 2.0 Declarative Architecture

2. Engine, Metadata, and Session Lifecycle

The Four States of an ORM Entity

3. Production Implementation: Schema Definition & CRUD

Visual Architecture & Process Flow

4. Modern 2.0 Querying: select() and session.scalars()

5. Architectural Summary Table

⚠️ Common Advanced Mistakes & Gotchas

1. Mixing SQLAlchemy 1.x and 2.0 Syntax

2. Leaking Unclosed Sessions

💻 Try It Yourself: Modern SQLAlchemy 2.0 Declarative Model

Scenario

Complete Solution

Expected Output

Practice Quiz

1.

2.

3.

4.

5.

🚀 What's Next?

Relationships in Databases

Related Lessons

Practice Quiz

4. Modern 2.0 Querying: `select()` and `session.scalars()`

4. Modern 2.0 Querying: `select()` and `session.scalars()`