2021-07-21Sqlalchemy关联查询

一、无外键关联查询

• 1.直接查询

class Test(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(32))
    
class TestItems(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    test_id = db.Column(db.Integer)
    
#应用，在浏览器访问http://localhost/test/
@app.route('/test/')
def test():
    query = db.session().query(models.TestItems, models.Test)
    query = query.join(models.Test, models.Test.id == models.TestItems.test_id)
    print(query)

query的返回结果为:[(TestItems01， Test01), (), ……]

参考：https://blog.csdn.net/weixin_34197488/article/details/92749456

• 2.关联表中字段过多，但是关联获取只取一个字段

class Test(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(32))
    
class TestItems(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    test_id = db.Column(db.Integer)
    
#应用，在浏览器访问http://localhost/test/
@app.route('/test/')
def test():
    query = db.session().query(models.TestItems, models.Test.name.label('TestName'))
    query = query.join(models.Test, models.Test.id == models.TestItems.test_id)
    print(query)

query的返回结果为:[(TestItems01， '测试1的名字'), (), ……]

参考：https://blog.csdn.net/weixin_32637935/article/details/112631663

• 3.同一个表查询多次
  不处理会报错，sql语句查询同一张表多次，并且没有别名，会报错

    query = db.session().query(TestItems, Test.name.label('TestName'), Test.name.label('TestName'))

正确的处理方式，先对model别名

from sqlalchemy.orm import aliased

 Create = aliased(Account)  前面定义过的model
 Update = aliased(Account)
 query = db.session().query(ArticleCategoryModel, Create.name.label('create'), Update.name.label('update'))
query = query.join(Create, Create.id == ArticleCategoryModel.create_id)
query = query.join(Update, Update.id == ArticleCategoryModel.update_id)
query = query.filter(ArticleCategoryModel.category_parents.is_(None)).all()

query的返回结果[(<ArticleCategoryModel 1>, '七七', '七七'), …………]

参考：https://blog.csdn.net/OrwellChen/article/details/90608858

• 4.多表查询特定字段

        from system_config.models.sysconfig_model import Account
        create = aliased(Account)
        update = aliased(Account)
        list_columns = [
            ChSenWordModel.wb_id,
            ChSenWordModel.origin,
            ChSenWordModel.knowledge_point,
            ChSenWordModel.knowledge_type,
            create.name.label('create_name'),
            update.name.label('update_name')
        ]
        self.LIST_COLUMNS = list_columns
        result = db.session.query(*list_columns).outerjoin(
            create, create.id == ChSenWordModel.create_id).outerjoin(
            update, update.id == ChSenWordModel.update_id).filter(
            *condition).order_by(ChSenWordModel.origin).paginate(
            page=self.page, per_page=self.page_size, error_out=False)