Flask-sqlalchemy高级机制之多表查询
一、外键关联查询
1.数据关联
2.关联查询
- 对于一对多关系, 外键在多的一方, 即 从表 中
- sqalchemy 中也可以通过 外键字段 实现数据关联 及 关联查询
1.数据关联
数据关联步骤:
- 从表模型类中 定义外键字段
- 从表模型对象 的外键字段 记录 主表主键
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 相关配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False
# 创建组件对象
db = SQLAlchemy(app)
# 用户表 主表(一) 一个用户可以有多个地址
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
# 地址表 从表(多)
class Address(db.Model):
__tablename__ = 't_adr'
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20))
user_id = db.Column(db.Integer) # 定义外键
@app.route('/')
def index():
"""添加并关联数据"""
user1 = User(name='张三')
db.session.add(user1)
db.session.flush() # 需要手动执行flush操作, 让主表生成主键, 否则外键关联失败
# db.session.commit() # 有些场景下, 为了保证数据操作的原子性不能分成多个事务进行操作
adr1 = Address(detail='中关村3号', user_id=user1.id)
adr2 = Address(detail='华强北5号', user_id=user1.id)
db.session.add_all([adr1, adr2])
db.session.commit()
return "index"
if __name__ == '__main__':
db.drop_all()
db.create_all()
app.run(debug=True)
2.关联查询
关联查询步骤: (以主查从为例)
1.先查询主表数据
2.再通过外键字段查询关联的从表数据
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 相关配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test31'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False
# 创建组件对象
db = SQLAlchemy(app)
# 用户表 一 一个用户可以有多个地址
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
# 地址表 多
class Address(db.Model):
__tablename__ = 't_adr'
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20))
user_id = db.Column(db.Integer) # 定义外键
@app.route('/demo')
def demo():
"""查询多表数据 需求: 查询姓名为"张三"的所有地址信息"""
# 1.先根据姓名查找到主表主键
user1 = User.query.filter_by(name='张三').first()
# 2.再根据主键到从表查询关联地址
adrs = Address.query.filter_by(user_id=user1.id).all()
for adr in adrs:
print(adr.detail)
return "demo"
@app.route('/')
def index():
"""添加并关联数据"""
user1 = User(name='张三')
db.session.add(user1)
db.session.flush()
adr1 = Address(detail='中关村3号', user_id=user1.id)
adr2 = Address(detail='华强北5号', user_id=user1.id)
db.session.add_all([adr1, adr2])
db.session.commit()
return "index"
if __name__ == '__main__':
db.drop_all()
db.create_all()
app.run(debug=True)
注意点:
- 普通字段就可以作为外键使用, 只要其记录了对应的主表主键就可以关联查询
- 如果需要 主从表数据 在同一个事务中添加并关联, 则可能需要 先手动调用session.flush方法 来执行插入操作并生成主键, 否则无法关联
二、关系属性
- 关系属性是 sqlalchemy 封装的一套查询关联数据的语法, 其目的为 让开发者使用 面向对象的形式 方便快捷的获取关联数据
- 关系属性的 本质仍是外键
- 关系属性使用步骤:
- 定义关系属性
关系属性名 = db.relationship('关联数据所在的模型类')
- 外键字段设置外键参数
外键字段 = db.Column(字段类型, db.ForeignKey(主表名.主键名))
- 通过关系属性获取关联数据
模型对象.关系属性
代码示例:
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 相关配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test31'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False
# 创建组件对象
db = SQLAlchemy(app)
# 用户表 一 一个用户可以有多个地址
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
addresses = db.relationship('Address') # 1.定义关系属性 relationship("关联数据所在的模型类")
# 地址表 多
class Address(db.Model):
__tablename__ = 't_adr'
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20))
# 2. 外键字段设置外键参数 db.ForeignKey('主表名.主键')
user_id = db.Column(db.Integer, db.ForeignKey('t_user.id'))
@app.route('/')
def index():
"""添加数据"""
user1 = User(name='张三')
db.session.add(user1)
db.session.flush()
adr1 = Address(detail='中关村3号', user_id=user1.id)
adr2 = Address(detail='华强北5号', user_id=user1.id)
db.session.add_all([adr1, adr2])
db.session.commit()
"""查询多表数据 需求: 查询姓名为"张三"的所有地址信息"""
# 先根据姓名查找用户主键
user1 = User.query.filter_by(name='张三').first()
# 3.使用关系属性获取关系数据
for address in user1.addresses:
print(address.detail)
return "index"
if __name__ == '__main__':
# 重置所有继承自db.Model的表
db.drop_all()
db.create_all()
app.run(debug=True)
注意点:
- 关系属性的本质还是外键, 所以数据关联还是通过外键来完成
- 设置了外键参数的外键字段, 在创建表时会自动生成 外键约束
- 对多关系属性返回值为列表, 元素为关联的模型对象
- 关系属性和外键关联都可以查询关联数据, 但是 关系属性会取出关联表中的所有字段(效率较低), 所以实际开发中 推荐使用外键关联查询
三、连接查询
- 开发中有联表查询需求时, 一般会使用 join连接查询
- sqlalchemy 也提供了对应的查询语法
db.session.query(主表模型字段1, 主表模型字段2, 从表模型字段1, xx.. ).join(从表模型类, 主表模型类.主键 == 从表模型类.外键)
- join语句 属于查询过滤器, 返回值也是 BaseQuery 类型对象
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
# 相关配置
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:mysql@127.0.0.1:3306/test31'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_ECHO'] = False
# 创建组件对象
db = SQLAlchemy(app)
# 用户表 一
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20))
# 地址表 多
class Address(db.Model):
__tablename__ = 't_adr'
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20))
user_id = db.Column(db.Integer) # 定义外键
@app.route('/demo')
def demo():
"""查询多表数据 需求: 查询姓名为"张三"的用户id和地址信息"""
# sqlalchemy的join查询
data = db.session.query(User.id, Address.detail).join(Address, User.id == Address.user_id).filter(User.name == '张三').all()
for item in data:
print(item.detail, item.id)
return "demo"
@app.route('/')
def index():
"""添加数据"""
user1 = User(name='张三')
db.session.add(user1)
db.session.flush()
adr1 = Address(detail='中关村3号', user_id=user1.id)
adr2 = Address(detail='华强北5号', user_id=user1.id)
db.session.add_all([adr1, adr2, user1])
db.session.commit()
return 'index'
if __name__ == '__main__':
db.drop_all()
db.create_all()
app.run(debug=True)
关联查询的性能优化:
- 可以发现 无论使用 外键 还是 关系属性 查询关联数据, 都需要查询两次, 一次查询用户数据, 一次查询地址数据
- 两次查询就需要发送两次请求给数据库服务器, 如果数据库和web应用不在一台服务器中, 则 网络IO会对查询效率产生一定影响
- 我们可以考虑使用 连接查询 join 使用一条语句就完成关联数据的查询
例:
# 使用join语句优化关联查询
adrs = Address.query.join(User, Address.user_id == User.id).filter(User.name == '张三').all() # 列表中包含地址模型对象
关于拆分join语句:
- 使用 JOIN连接查询 还是 拆分join为多条简单语句 并不是一个可以一概而论的问题, 查询性能受 联表数量、索引设计、缓存处理、数据库拆分、SQL优化器等多方面因素影响, 每个场景不尽相同
- 一般可以先考虑使用 JOIN连接查询, 在发现性能问题后再考虑进一步的优化尝试