LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
Python

Python - Object-Relational Mapping (ORM)

SQLAlchemy ORM lets you interact with relational databases using Python classes and objects instead of writing raw SQL strings.

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.

SQLAlchemy ORM Architecture
Mapping Python classes directly to database rows
Python Class (Model) Item Class Definition
SQLAlchemy Session
SQLite / SQL Server Relational Table Rows
Python Objects Instance Data Access
Python Model Active Step Database Table

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.

Comparing raw SQL execution with SQLAlchemy ORM
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.

  1. Raw SQL strings are not checked by Python until runtime.
  2. Switching database engines requires rewriting custom SQL syntax.
  3. 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.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.