Object-Relational Mapping (ORM) in Python – Abstracting Databases with SQLAlchemy
Quick Answer: SQLAlchemy ORM lets you interact with relational databases using Python classes and objects instead of writing raw SQL strings.
Imagine Priya manages grocery inventory data across multiple shops in Bengaluru.
Writing raw SQL strings like INSERT INTO items... inside Python code can lead to typos, security risks, and messy maintenance.
How would you manage database tables and complex queries cleanly using pure Python code?
1. Why Do We Need an ORM?
The problem without an ORM
We could write raw SQL queries directly inside Python strings using database connectors.
Example 1: Raw SQL queries without an ORM
This approach works, but string concatenation makes queries fragile and hard to maintain.
import sqlite3
connection = sqlite3.connect ("shop.db")
cursor = connection.cursor ()
cursor.execute ("SELECT name, price FROM items WHERE price > 50")
rows = cursor.fetchall ()
for row in rows:
print(row)
connection.close ()
Expected output
('Rice', 55.0)
This code works for small tasks, but it has key drawbacks.
We need an object-relational mapper to bridge Python classes and database tables.
The solution: SQLAlchemy ORM
ORM = a tool that maps database tables to Python classes.
Model = a Python class representing a specific database table.
Session = a workspace object that tracks changes and executes database queries.
Figure 1: SQLAlchemy translates Python object method calls into SQL commands automatically.
2. Implement SQLAlchemy Models and Queries in Python
Let us write a complete runnable Python program using SQLAlchemy to define models, add records, and run complex queries.
Example 2: Defining models and querying with SQLAlchemy
This code creates an in-memory database, defines an Item model, and performs queries.
from sqlalchemy import create_engine, select, String, Float
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, sessionmaker
class Base (DeclarativeBase):
pass
class Item (Base):
__tablename__ = "items"
id: Mapped[int ] = mapped_column(primary_key=True )
name: Mapped[str ] = mapped_column(String(50 ))
price: Mapped[float ] = mapped_column(Float)
def __repr__ (self ):
return $"Item(id={self.id}, name='{self.name}', price={self.price})"
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all (engine)
SessionLocal = sessionmaker(bind=engine)
with SessionLocal () as session:
rice = Item(name="Rice", price=55.0 )
sugar = Item(name="Sugar", price=42.0 )
session.add_all ([rice, sugar])
session.commit ()
stmt = select(Item).where(Item.price > 50.0 )
results = session.scalars (stmt).all ()
for item in results:
print(item)
Expected output
Item(id=1, name='Rice', price=55.0)
Notice how we query the database using Python class attributes (Item.price > 50.0) instead of writing raw SQL strings.
The Mapped type annotations ensure strict type checking for our model fields.
| Raw SQL Approach | SQLAlchemy ORM Approach |
|---|---|
| Queries written as plain strings | Queries constructed using Python classes and methods |
| Results returned as tuples | Results returned as fully typed Python objects |
| Database-specific query syntax | Database-agnostic abstraction layer |
The important terms to remember
Design tip: Always use context managers (with SessionLocal() as session:) to ensure database sessions close properly.
3. Common Mistakes to Avoid
1. Forgetting to commit database transactions
Adding items to a session without calling session.commit() leaves changes unsaved in the database.
Better approach: Always commit the session after inserting or modifying records.
4. Interview Questions & Answers
Question 1: What are the main benefits of using an ORM like SQLAlchemy?
What the interviewer expects: A clear explanation of abstraction, security, and maintainability.
Strong answer: "An ORM abstracts database interactions by letting developers write Python classes instead of raw SQL strings. This prevents SQL injection vulnerabilities, provides database independence, and returns query results as rich Python objects."
What interviewers expect at different levels
5. Practice Exercises
Beginner: Customer model
Create a SQLAlchemy Customer model with id, name, and email fields, then insert two records.
Expected result: Console prints both customer objects.
Intermediate: Filtering records
Query all customers whose email ends with @gmail.com using SQLAlchemy select() and where().
Hint: Use the Python string method like("%@gmail.com") inside the filter.
6. Frequently Asked Questions (FAQ)
Can SQLAlchemy connect to SQL Server and PostgreSQL?
Yes. SQLAlchemy supports multiple relational databases including SQLite, PostgreSQL, MySQL, and Microsoft SQL Server.
What is the difference between execute() and scalars()?
The scalars() method extracts model instances directly from query result rows for easier iteration.
7. Next Steps
Now that you have mastered SQLAlchemy ORM, you are ready to explore NoSQL integration with MongoDB in the next article.
- Raw SQL strings are not checked by Python until runtime.
- Switching database engines requires rewriting custom SQL syntax.
- Query results return as raw tuples instead of rich Python objects.
- ORM = object-relational mapping between tables and classes.
- DeclarativeBase = base class used to declare mapped models.
- Mapped = type annotation helper for modern SQLAlchemy attributes.
- Session = database transaction workspace.
- Junior developer: Knows how to define models and query simple records.
- Senior developer: Understands session lifecycles, lazy versus eager loading, and performance tuning.
- Technical architect: Designs database migration strategies and multi-tenant schema models.