? Python SQLAlchemy

⚡ Quick Overview

SQLAlchemy is a popular SQL toolkit and Object-Relational Mapper (ORM) for Python. It provides:

A high-level ORM layer to map Python classes to database tables.
A powerful Core (SQL Expression Language) for writing SQL in Pythonic syntax.
Support for multiple databases (SQLite, MySQL, PostgreSQL, etc.).
Connection management, transactions, and efficient query execution.

With SQLAlchemy, you can focus on Python objects instead of hand-writing SQL for everything, while still keeping full control when you need raw SQL.

? Key Concepts

Engine – Core interface to the database; manages connections.
Connection / Session – Handles conversations with the database.
Declarative Base – Base class for defining ORM models.
ORM Model – Python class mapped to a database table.

Column & Types – Represent table columns and their data types.
Query – High-level API to select, filter, and join tables.
Relationships – Define links between tables (one-to-many, many-to-many).
Metadata – Stores information about tables, columns, and constraints.

✏️ Syntax and Theory

Typical workflow when using SQLAlchemy ORM:

Create an Engine pointing to a database URL.
Define a Base class using declarative_base().
Create ORM model classes that inherit from the Base.
Use Session objects to insert, update, delete, and query records.
Commit transactions to persist changes.

? Basic Setup Example

Below is a minimal example that shows how to set up SQLAlchemy with SQLite and define a simple model.

? View Code Example – Engine, Base, and Model

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

# 1. Create engine (here using SQLite database file)
engine = create_engine("sqlite:///students.db", echo=True)

# 2. Base class for ORM models
Base = declarative_base()

# 3. ORM model mapped to a "students" table
class Student(Base):
    __tablename__ = "students"

    id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)
    age = Column(Integer)
    city = Column(String(100))

    def __repr__(self):
        return f"<Student id={self.id} name={self.name!r}>"

# 4. Create all tables in the database
Base.metadata.create_all(engine)

# 5. Create a Session factory
SessionLocal = sessionmaker(bind=engine)

? CRUD Operations Example

Once the model and engine are ready, you can perform CRUD (Create, Read, Update, Delete) operations using a session.

? View Code Example – Insert and Query Data

from sqlalchemy.orm import Session

# Create a new session
with Session(engine) as session:
    # INSERT: create Student objects
    s1 = Student(name="Alice", age=21, city="Pune")
    s2 = Student(name="Bob", age=22, city="Mumbai")

    session.add_all([s1, s2])
    session.commit()  # commit transaction to save to DB

    # SELECT: query all students
    all_students = session.query(Student).all()
    print("All students:", all_students)

    # FILTER: students from Pune
    pune_students = session.query(Student).filter(Student.city == "Pune").all()
    print("Pune students:", pune_students)

    # UPDATE: change a student's city
    alice = session.query(Student).filter(Student.name == "Alice").first()
    alice.city = "Delhi"
    session.commit()

    # DELETE: remove a student
    bob = session.query(Student).filter(Student.name == "Bob").first()
    session.delete(bob)
    session.commit()

? Live Output and Explanation

What this program does

Creates a SQLite database file named students.db (if it does not already exist).
Creates a table students with columns id, name, age, and city.
Inserts two students: Alice and Bob.
Queries and prints all students and only students from Pune.
Updates Alice's city to Delhi and deletes Bob's record.

Because echo=True was passed to create_engine(), SQLAlchemy will also print the generated SQL statements to the console. This helps you see exactly which SQL is executed behind the scenes.

? Use Cases

Building web applications (e.g., with Flask or FastAPI) that use relational databases.
Rapid prototyping when you want ORM convenience but retain SQL control.
Data migration scripts or admin tools that interact with existing databases.
Applications that must support multiple database backends with minimal code changes.

✅ Tips and Best Practices

Use Session as a context manager (with Session(engine) as session:) to auto-handle cleanup.
Keep your models in a separate module (e.g., models.py) for better organization.
Use meaningful column names and enforce constraints with nullable=False, unique constraints, and indexes.
Enable echo=True during development for debugging, but turn it off in production.
Prefer ORM for common CRUD operations, but do not hesitate to use Core / raw SQL for complex or performance-critical queries.

? Try It Yourself

Modify the Student model to add a email column. Insert a few students and verify that the new column is stored.
Create a new model Course with fields like id, name, and credits. Map it to a new table using SQLAlchemy ORM.
Add a relationship between Student and Course (e.g., many-to-many using an association table). Try inserting sample data and querying which students are enrolled in which courses.
Try changing the database URL to use an in-memory SQLite database ("sqlite:///:memory:") and observe the behavior.

As you practice, pay attention to how SQLAlchemy translates your Python code into SQL and how sessions manage pending changes before committing to the database.

? Interactive Thought Experiment

Use this quick helper to imagine how many records you might be inserting with SQLAlchemy. This is just a front-end calculator; in a real app, such numbers would guide how you batch inserts and design transactions.

Estimated number of students: