Python SQLAlchemy ORM: Displaying and Filtering Data

Introduction

SQLAlchemy ORM offers a powerful abstraction for interacting with databases in Python. This tutorial demonstrates how to fetch and filter data from an existing SQLite table using SQLAlchemy ORM with practical examples.

Part 1: Retrieving First 10 Records

The following code retrieves the first 10 records from the student table. Ensure your table is already populated with data.

# Import necessary modules
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import declarative_base, sessionmaker

# Base class for ORM models
Base = declarative_base()

# Define the Student table
class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    class_column = Column("class", String, nullable=False)  # Use "class_column" to avoid reserved keyword conflict
    mark = Column(Float, nullable=False)
    gender = Column(String, nullable=False)

# Connect to SQLite database, update your local system path 
engine = create_engine("sqlite:///E:\\testing\\sqlite\\my_db.db")
Session = sessionmaker(bind=engine)
session = Session()

# Fetch first 10 records
students = session.query(Student).limit(10).all()
for student in students:
    print(student.name, student.class_column, student.mark, student.gender)

Part 2: Filtering by Gender

To filter male students, use the filter method as shown below:

# Filter male students
male_students = session.query(Student).filter(Student.gender == 'male').all()
for student in male_students:
    print(student.name, student.class_column, student.mark, student.gender)

Part 3: Filtering by Class and Marks

The following code retrieves students from the Eight class who scored 60 or above:

# Filter students from class 'Eight' with marks >= 60
filtered_students = session.query(Student).filter(
    Student.class_column == "Eight",
    Student.mark >= 60
).all()
for student in filtered_students:
    print(student.name, student.class_column, student.mark)

Complete Code

Below is the complete script with all the discussed functionalities. You can copy the code for practice.

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

Base = declarative_base()

class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    class_column = Column("class", String, nullable=False)
    mark = Column(Float, nullable=False)
    gender = Column(String, nullable=False)

engine = create_engine("sqlite:///E:\\testing\\sqlite\\my_db.db")
Session = sessionmaker(bind=engine)
session = Session()

# Fetch first 10 records
print("First 10 records:")
students = session.query(Student).limit(10).all()
for student in students:
    print(student.name, student.class_column, student.mark, student.gender)

# Filter male students
print("\nMale students:")
male_students = session.query(Student).filter(Student.gender == 'male').all()
for student in male_students:
    print(student.name, student.class_column, student.mark, student.gender)

# Filter students from class 'Eight' with marks >= 60
print("\nStudents from class 'Eight' with marks >= 60:")
filtered_students = session.query(Student).filter(
    Student.class_column == "Eight",
    Student.mark >= 60
).all()
for student in filtered_students:
    print(student.name, student.class_column, student.mark)