Flask-SQLAlchemy新旧查询接口问题：新接口结果无法多次遍历

You may see uses of Model.query or session.query to build queries. That query interface is considered legacy in SQLAlchemy. Prefer using the session.execute(select(...)) instead.

原旧查询代码：

subnets = db.session.query(Subnet).order_by(Subnet.id).all()

对应的SQL语句：

SELECT subnet.id AS subnet_id, subnet.name AS subnet_name, subnet.network AS subnet_network, subnet.access AS subnet_access, subnet.date_created AS subnet_date_created
FROM subnet ORDER BY subnet.id

旧代码返回的subnets是列表，支持在视图中多次循环遍历。

改用新接口后：

subnets = db.session.execute(db.select(Subnet).order_by(Subnet.id)).scalars()

subnets变成了结果迭代器，迭代器的特性是只能遍历一次，遍历完成后就没有剩余数据，导致第二次遍历无结果。

解决办法 #

• 将迭代器转换为列表，和旧接口返回的类型保持一致，支持多次遍历：

subnets = list(db.session.execute(db.select(Subnet).order_by(Subnet.id)).scalars())

• 若数据量较大，不想一次性加载所有数据，可以在每次需要遍历的时候重新执行查询（注意：此方法会增加数据库查询次数，需根据业务场景选择）：

# 第一次遍历
for subnet in db.session.execute(db.select(Subnet).order_by(Subnet.id)).scalars():
    # 处理逻辑

# 第二次遍历
for subnet in db.session.execute(db.select(Subnet).order_by(Subnet.id)).scalars():
    # 处理逻辑

内容的提问来源于stack exchange，提问作者Tes3awy