我要评论本篇图解：数据从哪里来、到哪里去
图中最重要的边界是事务：只有成功路径才提交数据，失败路径不能留下半条业务记录。
一、上一篇课后练习讲解
多对多关系需要中间表：
create table authors (
id integer primary key autoincrement,
name text not null unique
);
create table book_authors (
book_id integer not null,
author_id integer not null,
primary key (book_id, author_id),
foreign key (book_id) references books(id) on delete cascade,
foreign key (author_id) references authors(id) on delete cascade
);参数化关联函数：
def add_author_to_book(book_id, author_id):
with connect() as connection:
connection.execute(
"insert into book_authors (book_id, author_id) values (?, ?)",
(book_id, author_id),
)联合主键禁止重复关联，外键保证两个编号真实存在。
可执行验收答案
把下面 sql 保存为 check_book_tags.sql，sqlite 中执行：
pragma foreign_keys = on;
create table if not exists books(id integer primary key, title text not null);
create table if not exists authors(id integer primary key, name text not null unique);
create table if not exists book_authors(
book_id integer not null references books(id) on delete cascade,
author_id integer not null references authors(id) on delete cascade,
primary key(book_id, author_id)
);
insert into books values (1, 'python入门');
insert into authors values (1, '小李');
insert into book_authors values (1, 1);
-- 下面一行重复执行时应失败：unique constraint failed
-- insert into book_authors values (1, 1);
select b.title, a.name from books b
join book_authors ba on ba.book_id=b.id
join authors a on a.id=ba.author_id;预期查询返回一行 python入门 | 小李。若重复关联没有失败，检查是否漏写复合主键；若删除 book 后关联仍在，确认开启 pragma foreign_keys=on，sqlite 默认可能关闭外键检查。
二、本篇成果
建立用户、商品、订单和订单项四张表，使用join读取关联数据，group by统计消费，索引优化查询，并用事务保证“创建订单、写明细、扣库存”全部成功或全部失败。
三、订单项为什么是独立实体
create table order_items (
id integer primary key,
order_id integer not null,
product_id integer not null,
quantity integer not null check (quantity > 0),
price numeric not null check (price > 0),
foreign key (order_id) references orders(id) on delete cascade,
foreign key (product_id) references products(id)
);成交价格必须复制到订单项，不能以后总读取商品当前价格，否则商品涨价会改变历史订单。
四、join和聚合
select o.id as order_id, u.name, p.name as product_name,
oi.quantity, oi.price, oi.quantity * oi.price as line_total
from orders o
join users u on u.id = o.user_id
join order_items oi on oi.order_id = o.id
join products p on p.id = oi.product_id
where o.id = ?;
select u.id, u.name, count(o.id) as order_count,
coalesce(sum(o.total_amount), 0) as spent
from users u left join orders o on o.user_id = u.id
group by u.id, u.name
order by spent desc;inner join只保留匹配行；left join保留左表，即使没有订单。count计数，sum求和，coalesce把null转为0。
五、索引
create index idx_orders_user_created on orders (user_id, created_at desc);
索引加快读取，但增加写入成本和存储空间。先观察真实查询，再创建索引。
六、完整下单事务
from datetime import datetime, timezone
def create_order(connection, user_id, items):
try:
connection.execute("begin")
cursor = connection.execute(
"insert into orders (user_id,total_amount,created_at) values (?,0,?)",
(user_id, datetime.now(timezone.utc).isoformat()),
)
order_id = cursor.lastrowid
total = 0
for product_id, quantity in items:
product = connection.execute(
"select name,price,stock from products where id = ?",
(product_id,),
).fetchone()
if product is none:
raise valueerror("商品不存在")
if product["stock"] < quantity:
raise valueerror(f"{product['name']}库存不足")
connection.execute(
"insert into order_items(order_id,product_id,quantity,price) values (?,?,?,?)",
(order_id, product_id, quantity, product["price"]),
)
connection.execute(
"update products set stock=stock-? where id=?",
(quantity, product_id),
)
total += product["price"] * quantity
connection.execute(
"update orders set total_amount=? where id=?",
(total, order_id),
)
connection.commit()
return order_id
except exception:
connection.rollback()
raise任何一步失败都rollback。捕获后重新raise，让调用者知道失败原因；不能回滚后假装成功。
七、验证回滚
准备一个库存充足和一个库存不足的商品，同时购买。预期orders、order_items没有新增，充足商品库存也没有减少。只检查异常不够，还要查询三张表证明回滚完整。
八、本篇验收
- 能判断一对多和多对多；
- 订单项保存成交价格快照；
- 会写join、group by和聚合；
- 理解索引读写权衡；
- 下单失败后所有相关写操作回滚。
九、课后练习
实现cancel_order(connection, order_id)：只能取消未取消订单；把订单项数量加回库存，再修改状态；任一步失败全部回滚。下一篇会把sqlite环境迁移到mysql。
实战补充：用 sql 解释更新结果
update 和 delete 都要检查影响行数；0 行可能表示资源不存在，不能盲目返回成功。
cursor = db.execute('update tasks set done = ? where id = ?', (1, task_id))
if cursor.rowcount == 0:
raise notfound('任务不存在')
db.commit()课后练习：把完成任务和审计放进同一事务，并写成功、不存在和审计失败三组测试；下一篇比较数据库类型和连接配置。
本篇结束：完整模块文件
本节不是代码片段，而是本篇结束时该模块的完整版本。请先备份旧文件，再整体替换；替换后重新运行本篇命令和测试。阅读时重点看本篇新增的函数、事务边界和错误处理，未涉及的代码先不要自行删减。
本篇完整示例
select u.name, count(o.id) as order_count from users u left join orders o on u.id=o.user_id group by u.id, u.name order by order_count desc;
到此这篇关于python关联、聚合、索引与事务:订单数据库(零基础热门)的文章就介绍到这了,更多相关python关联、聚合、索引与事务内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论