This guide demonstrates how to perform basic Create, Read, Update, and Delete (CRUD) operations using SQLAlchemy's Object-Relational Mapper (ORM), along with managing one-to-many and many-to-many relationships.
- Defining Database Models
SQLAlchemy's ORM allows you to map Python objects to database tables. This involves defining classes that inherit from a declarative base and specifying table names and columns.
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String
# Define the base class for declarative models
Base = declarative_base()
# Define the User model, mapping to the 'user' table
class User(Base):
__tablename__ = 'user'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(32), index=True)
# Database engine setup (replace with your actual database credentials and name)
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:password@localhost:3306/mydatabase?charset=utf8")
# Create all tables defined in Base's metadata
Base.metadata.create_all(engine)
- CRUD Operatinos
2.1 Inserting Data
To insert data, you create instances of your ORM model and add them to a database session.
from sqlalchemy.orm import sessionmaker
from my_models import User, engine # Assuming User model is in my_models.py
# Create a session factory
Session = sessionmaker(bind=engine)
db_session = Session()
# Insert a single record
user_record = User(name="Alice")
db_session.add(user_record)
db_session.commit()
# Insert multiple records using add_all
new_users = [
User(name="Bob"),
User(name="Charlie")
]
db_session.add_all(new_users)
db_session.commit()
db_session.close()
2.2 Querying Data
Data retrieval is performed using the query method on the session, followed by filters and execution methods like all() or first().
from sqlalchemy.orm import sessionmaker
from my_models import User, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Select all users
all_users = db_session.query(User).all()
for user in all_users:
print(f"ID: {user.id}, Name: {user.name}")
# Select users with ID greater than a certain value
filtered_users = db_session.query(User).filter(User.id > 10).all()
# Select a single user by ID
single_user = db_session.query(User).filter(User.id == 5).first()
if single_user:
print(f"Found user: {single_user.name}")
db_session.close()
2.3 Updating Data
Updates are typically performed by querying for the record(s) to modify, applying changes, and then committing the session.
from sqlalchemy.orm import sessionmaker
from my_models import User, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Update a specific user's name
user_to_update = db_session.query(User).filter(User.id == 1).first()
if user_to_update:
user_to_update.name = "Alice Smith"
db_session.commit()
# Update multiple records based on a filter
update_count = db_session.query(User).filter(User.id < 10).update({"name": "Updated Name"})
print(f"Number of records updated: {update_count}")
db_session.commit()
db_session.close()
2.4 Deleting Data
Deletion involves querying for the record(s) and using the delete() method on the query.
from sqlalchemy.orm import sessionmaker
from my_models import User, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Delete a specific user
user_to_delete = db_session.query(User).filter(User.id == 2).first()
if user_to_delete:
db_session.delete(user_to_delete)
db_session.commit()
# Delete multiple users based on a filter
delete_count = db_session.query(User).filter(User.id > 100).delete()
print(f"Number of records deleted: {delete_count}")
db_session.commit()
db_session.close()
2.5 Advanced Querying
SQLAlchemy provides powerful tools for complex queries, including logical operators, string matching, ordering, grouping, and subqueries.
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import text, and_, or_, func
from my_models import User, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Using AND and OR operators
users_and = db_session.query(User).filter(and_(User.id > 1, User.name == 'Alice')).all()
users_or = db_session.query(User).filter(or_(User.id < 5, User.name == 'Bob')).all()
# String matching (LIKE)
users_like = db_session.query(User).filter(User.name.like('A%')).all()
# Ordering results
ordered_users = db_session.query(User).order_by(User.name.desc()).all()
multi_ordered_users = db_session.query(User).order_by(User.name.desc(), User.id.asc()).all()
# Limiting results
limited_users = db_session.query(User)[0:5] # Slicing for LIMIT and OFFSET
# Grouping and aggregation
user_counts = db_session.query(User.name, func.count(User.id)).group_by(User.name).all()
# Subqueries
subquery_result = db_session.query(User).filter(User.id.in_(db_session.query(User.id).filter_by(name='Bob'))).all()
# Raw SQL query
raw_sql_users = db_session.query(User).from_statement(text("SELECT * FROM user WHERE name = :name")).params(name='Alice').all()
db_session.close()
2.6 Advanced Update Operations
Updates can also be performed based on existing values or using more complex expressions.
from sqlalchemy.orm import sessionmaker
from my_models import User, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Incrementing a value
db_session.query(User).filter(User.id > 0).update({User.name: User.name + " (Updated)"}, synchronize_session='evaluate')
db_session.commit()
db_session.close()
- One-to-Many Relationships
This involves setting up foreign keys and using SQLAlchemy's relationship to link tables.
3.1 Defining Models with ForeignKey and Relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
Base = declarative_base()
class Course(Base):
__tablename__ = 'course'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True)
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True)
# Foreign key linking to the 'course' table
course_id = Column(Integer, ForeignKey('course.id'))
# Define the relationship to Course
# 'backref' creates an inverse property 'students' on the Course model
course = relationship("Course", backref="students")
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:password@localhost:3306/mydatabase?charset=utf8")
Base.metadata.create_all(engine)
3.2 Inserting Data with Relationships
from sqlalchemy.orm import sessionmaker
from my_models import Student, Course, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Creating a course and adding students to it using the relationship
new_course = Course(name="Introduction to Python")
student1 = Student(name="Alice", course=new_course) # Assigning the course object directly
student2 = Student(name="Bob")
new_course.students.append(student2) # Using the backref 'students'
db_session.add_all([new_course, student1, student2])
db_session.commit()
db_session.close()
3.3 Querying with Relationships
from sqlalchemy.orm import sessionmaker
from my_models import Student, Course, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Querying students and accessing their course name
all_students = db_session.query(Student).all()
for student in all_students:
print(f"Student: {student.name}, Course: {student.course.name}")
# Querying courses and accessing their students
all_courses = db_session.query(Course).all()
for course in all_courses:
print(f"Course: {course.name}")
for student in course.students:
print(f" - {student.name}")
db_session.close()
3.4 Updating and Deleting with Relationships
Updates and deletes follow standard CRUD patterns, often involving filtering based on related objects.
from sqlalchemy.orm import sessionmaker
from my_models import Student, Course, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Updating a student's course
course_to_assign = db_session.query(Course).filter(Course.name == "Data Science").first()
student_to_update = db_session.query(Student).filter(Student.name == "Alice").first()
if student_to_update and course_to_assign:
student_to_update.course = course_to_assign
db_session.commit()
# Deleting students associated with a course
course_to_clear = db_session.query(Course).filter(Course.name == "Introduction to Python").first()
if course_to_clear:
db_session.query(Student).filter(Student.course_id == course_to_clear.id).delete()
db_session.commit()
db_session.close()
- Many-to-Many Relationships
This typically involves a third "association" table and relationships defined on both sides.
4.1 Defining Models for Many-to-Many
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
Base = declarative_base()
# Association table
class TeacherStudent(Base):
__tablename__ = 'teacher_student'
teacher_id = Column(Integer, ForeignKey('teacher.id'), primary_key=True)
student_id = Column(Integer, ForeignKey('student.id'), primary_key=True)
class Teacher(Base):
__tablename__ = 'teacher'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True)
# Define relationship to Student through the association table
students = relationship("Student", secondary="teacher_student", backref="teachers")
class Student(Base):
__tablename__ = 'student'
id = Column(Integer, primary_key=True)
name = Column(String(32), index=True)
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://root:password@localhost:3306/mydatabase?charset=utf8")
Base.metadata.create_all(engine)
4.2 Inserting Data with Many-to-Many
from sqlalchemy.orm import sessionmaker
from my_models import Teacher, Student, TeacherStudent, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Creating teachers and students, and linking them
teacher_a = Teacher(name="Mr. Smith")
student_x = Student(name="Alice")
student_y = Student(name="Bob")
teacher_a.students.append(student_x) # Using the relationship
student_y.teachers.append(teacher_a) # Using the backref
db_session.add_all([teacher_a, student_x, student_y])
db_session.commit()
db_session.close()
4.3 Querying Many-to-Many Data
from sqlalchemy.orm import sessionmaker
from my_models import Teacher, Student, TeacherStudent, engine
Session = sessionmaker(bind=engine)
db_session = Session()
# Querying teachers and their students
all_teachers = db_session.query(Teacher).all()
for teacher in all_teachers:
print(f"Teacher: {teacher.name}")
for student in teacher.students:
print(f" - {student.name}")
# Querying students and their teachers
all_students = db_session.query(Student).all()
for student in all_students:
print(f"Student: {student.name}")
for teacher in student.teachers:
print(f" - {teacher.name}")
db_session.close()