一、SQLAlchemy 2.0 入门
uv add sqlalchemy
from sqlalchemy import create_engine, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
# SQLite 数据库文件
engine = create_engine("sqlite:///todo.db")
class Base(DeclarativeBase):
pass
class Todo(Base):
__tablename__ = "todos"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(100))
done: Mapped[bool] = mapped_column(default=False)
# 创建表
Base.metadata.create_all(engine)
二、CRUD 操作
from sqlalchemy.orm import Session
# 添加
with Session(engine) as session:
todo = Todo(title="学习 SQLAlchemy")
session.add(todo)
session.commit()
print(f"添加成功,ID: {todo.id}")
# 查询所有
with Session(engine) as session:
todos = session.query(Todo).all()
for t in todos:
print(f"[{t.id}] {t.title} done={t.done}")
# 按 ID 查询
with Session(engine) as session:
todo = session.get(Todo, 1)
print(todo.title)
# 更新
with Session(engine) as session:
todo = session.get(Todo, 1)
todo.done = True
session.commit()
# 删除
with Session(engine) as session:
todo = session.get(Todo, 1)
session.delete(todo)
session.commit()
三、查询过滤
with Session(engine) as session:
# 等于
todos = session.query(Todo).filter(Todo.done == False).all()
# 模糊查询
todos = session.query(Todo).filter(Todo.title.like("%python%")).all()
# 排序
todos = session.query(Todo).order_by(Todo.id.desc()).all()
# 限制数量
todos = session.query(Todo).limit(10).all()
# 计数
count = session.query(Todo).filter(Todo.done == False).count()
动手练习
练习 1:User 模型
定义 User 表模型
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(50))
email: Mapped[str] = mapped_column(String(100), unique=True)
查看答案
参考答案
# unique=True 表示邮箱唯一
练习 2:查询
查询所有未完成的任务
todos = session.query(Todo).filter(Todo.done == False).all()
查看答案
参考答案
# 等价于 SQL: SELECT * FROM todos WHERE done = 0
练习 3:关联查询
定义 User 和 Todo 的一对多关系
from sqlalchemy.orm import relationship
class User(Base):
todos: Mapped[list["Todo"]] = relationship(back_populates="user")
class Todo(Base):
user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
user: Mapped["User"] = relationship(back_populates="todos")
查看答案
打卡验收
理解 Mapped 和 mapped_column
打卡方式: 全部勾选后,告诉我 "Day 17 打卡",并贴出你的代码。