关联查询
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'
# 配置是否监听数据库变化, 性能较差, 不属于sqlalchemy本体
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 一旦开启, 可以显示底层执行的SQL语句
app.config['SQLALCHEMY_ECHO'] = True
# 创建数据库连接
db = SQLAlchemy(app)
# 用户表 一
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
# 地址表 多
class Address(db.Model):
__tablename__ = 't_address'
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='zs')
db.session.add(user1)
db.session.flush() # 主动发送sql
# 关联数据
adr1 = Address(detail='上海中心', user_id=user1.id)
adr2 = Address(detail='陆家嘴1号', user_id=user1.id)
db.session.add_all([adr1, adr2])
db.session.commit()
# 关联查询
user1 = User.query.filter(User.name=='zs').first()
adrs = Address.query.filter(Address.user_id==user1.id).all()
for adr in adrs:
print(adr.detail)
return 'index'
if __name__ == '__main__'
db.drop_all()
db.create_all()
app.run(debug=True, host='0.0.0.0')
# 用户表 一
class User(db.Model):
__tablename__ = 't_user'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(20), unique=True)
# 2. 定义关系属性 relationship('关联的表对应的类名')
addresses = db.relationship('Address')
# 地址表 多
class Address(db.Model)
__tablename__ = 't_address'
id = db.Column(db.Integer, primary_key=True)
detail = db.Column(db.String(20)
# 1. 设置外键参数
user_id = db.Column(db.Integer, db.ForeignKey('t_user') # 逻辑外键, 测试用
@app.route('/')
def index():
# 添加数据
user1 = User(name='zs')
db.session.add(user1)
db.session.flush() # 主动发sql
# 关联数据
adr1 = Address(detail='上海中心', user_id=user1.id)
adr2 = Address(detail='陆家嘴1号', user_id=user.id)
db.session.add_all([adr1, adr2])
db.session.commit()
# 使用关系属性来关联查询 1> 定义外键参数 2> 定义关系属性 本质还是通过外键查询
user1 = User.query.filter(User.name == 'zs').first()
# adrs = Address.query.filter(Address.user_id==user1.id).all()
for adr in user1.addresses:
print(adr.detail)
return 'index'
if __name__ == '__main__':
db.drop_all()
db.create_all()
app.run(debug=True, host='0.0.0.