不使用外键实现 Flask-SQLAlchemy 中多表链接查询

# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer, Float, JSON, DATE, Text, ForeignKey
from sqlalchemy.ext.declarative import declarative_base

# 创建基础的元数据
base_one = declarative_base()

class AnalysisModule(base_one):
    """
    模型，将映射到数据库表中
    """
    __tablename__ = 'analysis_module'

    # 主键ID
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 模块id
    module_key = Column(Text, nullable=False)
    # 模块名称
    module_name = Column(Text, nullable=False)
    # 功能id
    function_key = Column(Text, nullable=False)
    # 功能名称
    function_name = Column(Text, nullable=False)
    # 备注
    bz = Column(Text, nullable=False)
    # 状态
    state = Column(Integer, nullable=False)

    def __iter__(self):
        return self


class AnalysisModuleAlgorithmRelation(base_one):
    """
    模型，将映射到数据库表中
    """
    __tablename__ = 'analysis_module_algorithm_relation'

    # 主键ID
    id = Column(Integer, primary_key=True, autoincrement=True)
    # 功能id
    function_key = Column(Text, nullable=False)
    # 功能名称
    algorithm_key = Column(Text, nullable=False)
    # 状态
    use_state = Column(Integer, nullable=False)

    def __iter__(self):
        return self

def class_to_dict(obj):
    '''把对象(支持单个对象、list、set)转换成字典'''
    is_list = obj.__class__ == [].__class__
    is_set = obj.__class__ == set().__class__

    if is_list or is_set:
        obj_arr = []
        for o in obj:
            # 把Object对象转换成Dict对象
            dict = {}
            dict.update(o.__dict__)
            obj_arr.append(dict)
        return obj_arr
    else:
        dict = {}
        dict.update(obj.__dict__)
        dict.pop('_sa_instance_state', None)
        return dict

from server.dbs.db import session_zs
from sqlalchemy import text

with session_zs() as session:
    #单表，指定字段查询
    for row in session.query(AnalysisModule.module_key ,AnalysisModule.module_name).all():
        print(row)
        print(row.module_key, row.module_name)
    #单表,sql查询
    for row in session.query(AnalysisModule).from_statement(text("SELECT * FROM analysis_module where module_key=:module_key")).params(module_key='01').all():
        print(row)
        print(row.module_key, row.module_name)
    # 子查询关联查询
    stmt = session.query(AnalysisModuleAlgorithmRelation).filter(AnalysisModuleAlgorithmRelation.use_state==1).subquery()
    for u, count in session.query(AnalysisModule, stmt.c.algorithm_key).outerjoin(stmt, AnalysisModule.function_key == stmt.c.function_key):
        print(u)
        print(u.function_key, count)
    #单表简单查询
    stu_obj = session.query(AnalysisModule).filter(AnalysisModule.module_key=="01").all()
    for s in stu_obj:
        print(s.module_key, s.module_name)
        print(class_to_dict(s))
    # 关联查询，直接加条件
    stu_obj = session.query(AnalysisModule,AnalysisModuleAlgorithmRelation).filter(AnalysisModule.module_key=="01").filter(AnalysisModuleAlgorithmRelation.use_state==1).filter(AnalysisModule.function_key == AnalysisModuleAlgorithmRelation.function_key).all()
    for s in stu_obj:
        print(s)
        print({**class_to_dict(s[0]), **class_to_dict(s[1])})

    #关联查询 筛选字段 它是一个 AbstractKeyedTuple 对象，拥有一个 keys() 方法，这样可以很容易将其转换成 dict
    stu_obj = session.query(AnalysisModule.module_name, AnalysisModule.function_name, AnalysisModuleAlgorithmRelation.algorithm_key).join(AnalysisModuleAlgorithmRelation, AnalysisModule.function_key == AnalysisModuleAlgorithmRelation.function_key).filter(
        AnalysisModule.module_key == "01").filter(AnalysisModuleAlgorithmRelation.use_state == 1).all()
    for s in stu_obj:
        print(s)
        print(s.keys())
        print([dict(zip(s.keys(), s))])

    #关联查询 获得多个 Model 的记录
    stu_obj = session.query(AnalysisModule, AnalysisModuleAlgorithmRelation).join(AnalysisModuleAlgorithmRelation, AnalysisModule.function_key == AnalysisModuleAlgorithmRelation.function_key).filter(
        AnalysisModule.module_key == "01").filter(AnalysisModuleAlgorithmRelation.use_state == 1).all()
    for s in stu_obj:
        print(s)
        print({**class_to_dict(s[0]), **class_to_dict(s[1])})
    #关联查询 筛选字段 它是一个 AbstractKeyedTuple 对象，拥有一个 keys() 方法，这样可以很容易将其转换成 dict
    stu_obj = session.query(AnalysisModule, AnalysisModuleAlgorithmRelation).join(AnalysisModuleAlgorithmRelation, AnalysisModule.function_key == AnalysisModuleAlgorithmRelation.function_key).filter(
        AnalysisModule.module_key == "01").filter(AnalysisModuleAlgorithmRelation.use_state == 1).with_entities(AnalysisModule.module_name, AnalysisModule.function_name, AnalysisModuleAlgorithmRelation.algorithm_key).all()
    for s in stu_obj:
        print(s)
        print([dict(zip(s.keys(), s))])

参考：
https://www.cnblogs.com/shengs/p/10473524.html
https://blog.csdn.net/qq_43355223/article/details/83542739