Object-Relational Mapping (ORM)

What is ORM?

ORM stands for Object-Relational Mapping. It allows you to interact with a relational database using objects in your code instead of raw SQL queries. Essentially, it maps database tables to classes and rows to objects.

How it Works

1. Database table → Class
2. Row in table → Instance of class (object)
3. Columns → Attributes of the object
4. SQL queries → Methods on objects

Example: Suppose you have a users table in your database:

id	name	email
1	Alice	alice@mail.com
2	Bob	bob@mail.com

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

# Querying without raw SQL
user = session.query(User).filter_by(name="Alice").first()
print(user.email)  # alice@mail.com

Benefits of ORM

• Abstraction & Simplicity:
  • No need to write raw SQL for every query.
  • Makes code easier to read and maintain.
• Faster Development:
  • CRUD operations (Create, Read, Update, Delete) are simple.
  • Example: session.add(user) → adds new row.
• Database-Agnostic:
  • Switch databases with minimal code changes.
  • ORM translates operations into SQL for each DB engine.
• Prevents SQL Injection: ORM safely handles user inputs internally.
• Object-Oriented:
  • Works naturally with Python, Java, or C#.
  • Manipulate data as objects instead of rows/columns.

Popular ORMs

Language	ORM Library
Python	SQLAlchemy, Django ORM, Peewee
Java	Hibernate, JPA
C#	Entity Framework
Ruby	ActiveRecord

ORM Mapping Visual

DATABASE TABLE (Relational)
┌─────────┬─────────┬─────────────────┐
│ id      │ name    │ email           │
├─────────┼─────────┼─────────────────┤
│ 1       │ Alice   │ alice@mail.com  │
│ 2       │ Bob     │ bob@mail.com    │
└─────────┴─────────┴─────────────────┘

⬇ Object-Relational Mapping (ORM)

OBJECTS IN CODE (Classes & Instances)
class User:
    def __init__(self, id, name, email):
        self.id = id
        self.name = name
        self.email = email

# Instances (rows)
user1 = User(1, "Alice", "alice@mail.com")
user2 = User(2, "Bob", "bob@mail.com")

⬇ ORM Querying / Interaction
user = session.query(User).filter_by(name="Alice").first()
print(user.email)  # Output: alice@mail.com
      

Summary

1. Table → Class: Each table in the database becomes a class in your code.
2. Row → Object: Each row of data becomes an instance (object) of that class.
3. Column → Attribute: Table columns map directly to object attributes.

SQL Queries → Methods
Methods like filter_by, add, update, and delete automatically generate the SQL for you.