🧱 Python Tutorial — ORM Basics with SQLAlchemy

Introduction 🌟

SQLAlchemy is the most popular Python ORM (Object Relational Mapper). It allows you to interact with databases using Python classes instead of raw SQL queries.

Note

💡 Database-agnostic (MySQL, PostgreSQL, SQLite, SQL Server, etc.)
💡 Modern ORM with high flexibility
💡 Supports both ORM (high-level) & Core (low-level SQL)

1. Install SQLAlchemy 📦

install.sh

pip install sqlalchemy

✔ For PostgreSQL/MySQL, install additional drivers (psycopg2, mysql-connector)

2. Creating the Database Engine 🔌

engine.py

from sqlalchemy import create_engine

engine = create_engine("sqlite:///orm_demo.db", echo=True)

✔ echo=True prints SQL queries
✔ Use other drivers for different DBs:

Code Snippet

postgresql://user:password@localhost/dbname

Code Snippet

mysql+mysqlconnector://user:password@localhost/dbname

3. Declarative Base — Foundation of ORM 🏗️

base.py

from sqlalchemy.orm import declarative_base

Base = declarative_base()

✔ All ORM models inherit from Base

4. Defining a Model (Table Structure) 📄

model.py

from sqlalchemy import Column, Integer, String
from base import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

    def __repr__(self):
        return f"<User(name={self.name}, age={self.age})>"

✔ Model class represents a table
✔ Each attribute = a column

5. Creating Tables in the Database 🧱

create_tables.py

from base import Base
from engine import engine
from model import User

Base.metadata.create_all(engine)

✔ Creates all tables defined under Base

6. Creating a Session (database interaction) 🧠

session.py

from sqlalchemy.orm import sessionmaker
from engine import engine

Session = sessionmaker(bind=engine)
session = Session()

7. Insert Data ➕

insert.py

u1 = User(name="Sathish", age=25)
session.add(u1)
session.commit()

Insert Multiple

bulk_insert.py

session.add_all([
    User(name="Kumar", age=30),
    User(name="Priya", age=27),
])
session.commit()

8. Query Data 🔍

query_all.py

users = session.query(User).all()
print(users)

Filter

filter.py

young_users = session.query(User).filter(User.age < 30).all()
print(young_users)

Filter with multiple conditions

multi_filter.py

session.query(User).filter(
    User.age > 20,
    User.name.like("S%")
).all()

9. Updating Records ✏️

update_user.py

user = session.query(User).filter_by(name="Sathish").first()
user.age = 26
session.commit()

10. Deleting Records ❌

delete_user.py

user = session.query(User).filter_by(name="Kumar").first()
session.delete(user)
session.commit()

11. Using SQLAlchemy Operators ⚙️

operators.py

from sqlalchemy import or_, and_

session.query(User).filter(
    or_(User.age < 25, User.name == "Priya")
).all()

12. Ordering & Limiting Results 📊

order_limit.py

session.query(User).order_by(User.age.desc()).limit(5).all()

13. Relationship Basics (Foreign Keys) 🔗

relationship.py

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship

class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)

    user = relationship("User", backref="posts")

✔ Enables joining User ↔ Posts

14. Joining Tables 🪢

join_example.py

session.query(User, Post).join(Post).all()

15. Using SQLAlchemy with Context Manager 🤝

context.py

from sqlalchemy.orm import Session

with Session(engine) as session:
    users = session.query(User).all()
    print(users)

16. Raw SQL with SQLAlchemy (Optional) ⚡

raw_sql.py

result = session.execute("SELECT * FROM users")
for row in result:
    print(row)

17. Dropping Tables ⚠️

drop_tables.py

Base.metadata.drop_all(engine)

Note

⚠️ Irreversible operation

SQLAlchemy ORM Cheat Sheet 📘

OperationCommand
Create tableBase.metadata.create_all()
Insertsession.add(), add_all()
Selectsession.query(User)
Filterfilter(), filter_by()
UpdateEdit object + commit
Deletesession.delete()
Joinjoin()
Relationshipsrelationship()

Best Practices 💡

  • ✔ Use models to represent database tables
  • ✔ Use sessions responsibly (commit/rollback)
  • ✔ Use relationships to avoid manual joins
  • ✔ Avoid raw SQL unless necessary
  • ✔ Configure connection pooling for production

Conclusion 🎉

>>“SQLAlchemy ORM transforms database tables into Python objects — giving you clean, elegant, and maintainable data access.” ✨

You now understand the Basics of SQLAlchemy ORM! Want the next topic? Try Advanced ORM, SQLAlchemy Relationships, Alembic Migrations, or FastAPI + SQLAlchemy. Just tell me! 😊