Lesson 2: SQLAlchemy ORM

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. It allows developers to interact with databases using Python objects instead of writing raw SQL queries. This abstraction makes code cleaner, more maintainable, and easier to scale.

Lesson Outline:

1. Introduction to SQLAlchemy
2. Setting Up the Database Model
3. Querying with ORM

1. Introduction to SQLAlchemy

What is SQLAlchemy?

• SQLAlchemy is a library that provides tools for working with relational databases in Python.
• It supports both Core (SQL Expression Language)for raw SQL queries and ORM for working with Python objects.
• SQLAlchemy supports multiple databases like SQLite, MySQL, PostgreSQL, etc.

Why Use SQLAlchemy ORM?

• Simplifies database interactions by allowing developers to work with Python classes and objects.
• Enhances code readability and maintainability.
• Supports complex database operations with less boilerplate code.

Installing SQLAlchemy:

pip install SQLAlchemy

2. Setting Up the Database Model

SQLAlchemy ORM maps Python classes to database tables. Here’s how to define a database model:

Step 1: Import Required Modules

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base

from sqlalchemy.orm import sessionmaker

Step 2: Create an Engine and Base Class

# Connecting to SQLite database

engine = create_engine(‘sqlite:///students.db’, echo=True)

Base = declarative_base()

Step 3: Define a Model (Table)

class Student(Base):

__tablename__ = ‘students’

id = Column(Integer, primary_key=True)

name = Column(String)

age = Column(Integer)

grade = Column(String)

 

def __repr__(self):

return f”<Student(name='{self.name}’, age={self.age}, grade='{self.grade}’)>”

Step 4: Create the Table

Base.metadata.create_all(engine)

This command generates the students table in the students.db database.

3. Querying with ORM

Creating a Session:
Before querying, we need a session to interact with the database.

Session = sessionmaker(bind=engine)

session = Session()

Inserting Data:

new_student = Student(name=’Alice’, age=20, grade=’A’)

session.add(new_student)

session.commit()

Querying Data:

• Retrieve All Records:

students = session.query(Student).all()

for student in students:

print(student)

• Filter Records:

student = session.query(Student).filter_by(name=’Alice’).first()

print(student)

Updating Records:

student.age = 21

session.commit()

Deleting Records:

session.delete(student)

session.commit()

Key Takeaways:

• SQLAlchemy ORM helps manage database operations using Python classes.
• The Sessionobject is crucial for adding, querying, updating, and deleting records.
• Using SQLAlchemy ORM improves code readability and efficiency, especially for complex applications.

Next Steps: Explore relationships between tables using foreign keys and advanced querying techniques.