python|sqlalchemy|联表查询

2020-04-20|待完善，给出可以直接运行的py文件

建立表的类

class Account(db.Model):
    __tablename__ = 'account'
    id = db.Column(db.INT, primary_key=True, nullable=False, autoincrement=True)
    nickname = db.Column(db.VARCHAR(64), nullable=False, unique=True)
    def __repr__(self):
        return '' % (self.gameuid)

class Bind(db.Model):
    __tablename__ = 'bind'
    id = db.Column(db.BIGINT, primary_key=True, autoincrement=True)
    # 绑定者和被绑定者的 gameuid； 建立了外键关联； 记录两个用户之间的关系； 再不添加外键，也实现联表查询（已经确认）
    fromid = db.Column(db.BIGINT, db.ForeignKey('account.id'), nullable=False)
    toid = db.Column(db.BIGINT, db.ForeignKey('account.id'), nullable=False)
    def __repr__(self):
        return '' % (self.fromid, self.toid)

联表查询: 基础

# 查询id=1000的账号下绑定的所有账号
# first： 不使用join
res = db.session.query(Bind.id, Account.id).filter(Bind.fromid == Account.id)filter(Bind.id==1000).all()

# 查看对应的sql语句的方法： print( db.session.query(Bind.id, Account.id).filter(Bind.fromid = Account.id))
# 此时使用的是where语句

# second：使用join
db.session.query(Bind.id, Account.nickname).join(Account, Account.id==Bind.fromid).filter(Bind.toid==1000).all()
# ps: join语句中的表名称，必须是在query中后出现的表的名称，否则会报错；query中的表的顺序决定了链接表的顺序
# ps: 链接超过两张表，可以在join得到的结果后面继续使用join，也可以在filter后面继续使用join；无限链接下去。

联表查询: 筛选特定字段

res = Account.query.join(Bind, Bind.fromid == Account.gameuid). 
    filter(Bind.toid == 1000). 
    with_entities(Bind.bindid, Account.nickname).all()

# 返回的结果: 不是python的truple；实际上是<class 'sqlalchemy.util._collections.result'>，它是 AbstractKeyedTuple 对象，拥有一个 keys() 方法，可以转化为字典
[(2, '玩家10001'), (3, '玩家10002')]

[dict(zip(item.keys(), item)) for item in res]
# 结果
[{'bindid': 2, 'nickname': '玩家10001'}, {'bindid': 3, 'nickname': '玩家10002'}]

实战案例

# 表 ClueInfo， ClueTypeInfo， BelongsToRelation
# 还使用到子查询; in_ or_ and_ ，子查询不能使用first，all等获取结果
# filter_by()只允许输入一个参数；当使用or_ and_ 时候，需要使用filter
from sqlalchemy.testing import in_, and_

space_list = db.session.query(ClueInfo, ClueTypeInfo).join(ClueTypeInfo,
                                                             ClueTypeInfo.id == ClueInfo.clueObjectTypeId) 
    .filter(or_(ClueTypeInfo.typeTitle == 'project_space', ClueTypeInfo.typeTitle == 'private_space')) 
    .filter(ClueInfo.id.in_(db.session.query(BelongsToRelation.objectId) 
                            .filter(BelongsToRelation.belongsToClueId == user.id) 
                            .filter(
    or_(BelongsToRelation.objectTypeId == 'edeb2efc8a4cc60abe50fe5c42f940da',
        BelongsToRelation.objectTypeId == 'ff7dc456c24a3662a55b23f36166e71e')) 
                            .filter(BelongsToRelation.softDeleted == 0))).all()
# 返回的结果是列表

[(<ClueInfo benny>, <ClueTypeInfo project_space>), (<ClueInfo 大市场一楼>, <ClueTypeInfo project_space>)]

参考文章链接：

https://pdf-lib.org/Home/Details/7409