Python SQLAlchemy Ultimate Guide: Secrets to Playing with Relational Databases

1. Encounter sqlAlchemy: Start the databasejourney

SQLAlchemy is a powerful python ORM (Object Relational Mapping) tool for connecting Python code to relationshipsDatabase, it can convert complex data operations into simple Python syntax, allowing developers to focus more on business logic while having complete control over the database structure.

1. First introduction to SQLAlchemy: installation and configuration

# 安装 SQLAlchemy
pip install sqlalchemy

# 导入 SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String, Table

# 创建数据库引擎
engine = create_engine("sqlite:///database.db")

2. Define the model: build the database structure

# 定义模型类，代表数据库中的表
class User(object):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
name = Column(String(50))
email = Column(String(50))

# 创建表
User.metadata.create_all(engine)

2. Controlling data: adding, deleting, checking and modifying

1. Insert data: populate the database

# 创建会话
session = Session(bind=engine)

# 插入数据
new_user = User(name="张三", email="zhangsan@example.com")
session.add(new_user)

# 提交事务，使数据持久化
session.commit()

# 关闭会话
session.close()

2. Query data: extract required

# 创建会话
session = Session(bind=engine)

# 查询所有数据
users = session.query(User).all()

# 查询特定数据
user = session.query(User).filter(User.name == "张三").first()

# 关闭会话
session.close()

# 打印查询结果
print(users)
print(user)

3. Update data: modify existing

# 创建会话
session = Session(bind=engine)

# 更新数据
user = session.query(User).filter(User.name == "张三").first()
user.email = "zhangsan@new_example.com"

# 提交事务，使数据持久化
session.commit()

# 关闭会话
session.close()

# 打印更新后的数据
print(user)

4. Delete data: Erase unnecessary

# 创建会话
session = Session(bind=engine)

# 删除数据
user = session.query(User).filter(User.name == "张三").first()
session.delete(user)

# 提交事务，使数据持久化
session.commit()

# 关闭会话
session.close()

# 打印删除后的数据
print(session.query(User).all())

3. Relationship Modeling: Outline Data Association

1. One-to-many relationship: building a hierarchy

# 定义父模型
class Parent(object):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
name = Column(String(50))

# 定义子模型
class Child(object):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
name = Column(String(50))
parent_id = Column(Integer, ForeignKey("parent.id"))

# 创建表
Parent.metadata.create_all(engine)
Child.metadata.create_all(engine)

# 创建数据
parent = Parent(name="王五")
child1 = Child(name="小红", parent_id=parent.id)
child2 = Child(name="小明", parent_id=parent.id)

# 提交事务，使数据持久化
session = Session(bind=engine)
session.add_all([parent, child1, child2])
session.commit()

# 关闭会话
session.close()

2. Many-to-many relationship: connecting complex associations

# 定义模型
class Student(object):
__tablename__ = "student"
id = Column(Integer, primary_key=True)
name = Column(String(50))

class Course(object):
__tablename__ = "course"
id = Column(Integer, primary_key=True)
name = Column(String(50))

# 定义关联表
student_course = Table("student_course",
Column("student_id", Integer, ForeignKey("student.id")),
Column("course_id", Integer, ForeignKey("course.id")))

# 创建表
Student.metadata.create_all(engine)
Course.metadata.create_all(engine)
student_course.create(engine)

# 创建数据
student1 = Student(name="李四")
student2 = Student(name="赵六")
course1 = Course(name="数学")
course2 = Course(name="语文")

# 提交事务，使数据持久化
session = Session(bind=engine)
session.add_all([student1, student2, course1, course2])
session.commit()

# 建立关联
student1.courses = [course1, course2]
student2.courses = [course1]

# 提交事务，使关联持久化
session.commit()

# 关闭会话
session.close()

4. Practice: ProjectApplication

1. Blog system: storing articles and comments

2. E-commerce system: manage products and orders

3. Social platform: building user relationship and messaging system

Conclusion

SQLAlchemy opens up a convenient road to relational databases for us. It not only simplifies data operations, but also provides flexible relationship modeling capabilities, making data management easier and more efficient. I hope this guide can inspire you on your SQLAlchemy journey and help you travel thousands of miles in the world of data.

The above is the detailed content of Python SQLAlchemy Ultimate Guide: Secrets to Playing with Relational Databases. For more information, please follow other related articles on the PHP Chinese website!