Flask SQLAlchemy执行原生SQL关联查询如何获取列名生成JSON响应

你调用session.execute返回的结果对象本身就携带列名元数据，可通过以下两种方式直接组装为字典：

方案1：使用内置mappings()方法（SQLAlchemy 1.4及以上版本推荐） #

结果对象的mappings()方法会直接返回由{列名: 列值}组成的可迭代序列，无需手动组装，修改后的代码如下：

def get(self, pow_id):
    try:
        results = db.session.execute(''
                                    'SELECT * FROM POW p JOIN completed_course cc1 on p.student_id = cc1.student_id '
                                    'JOIN term_course tc on cc1.term_course_id = tc.id '
                                    'JOIN completed_course cc on tc.id = cc.term_course_id '
                                    'LEFT JOIN specialization_course sc on tc.course_id = sc.course_id '
                                    'AND sc.specialization_id = p.specialization_id '
                                    'WHERE p.id = :val;', {'val': pow_id})
        # 直接转为字典列表
        res_list = [dict(row) for row in results.mappings()]
        # 后续可直接对res_list做JSON序列化
        return res_list, 200
    except Exception as e:
        raise Exception("")

方案2：手动通过列名组装（兼容SQLAlchemy历史版本） #

结果对象的keys()方法可以返回所有查询列的名称列表，手动和每行的数值做映射即可：

def get(self, pow_id):
    try:
        results = db.session.execute(''
                                    'SELECT * FROM POW p JOIN completed_course cc1 on p.student_id = cc1.student_id '
                                    'JOIN term_course tc on cc1.term_course_id = tc.id '
                                    'JOIN completed_course cc on tc.id = cc.term_course_id '
                                    'LEFT JOIN specialization_course sc on tc.course_id = sc.course_id '
                                    'AND sc.specialization_id = p.specialization_id '
                                    'WHERE p.id = :val;', {'val': pow_id})
        # 获取列名列表
        columns = results.keys()
        # 手动组装字典
        res_list = [dict(zip(columns, row)) for row in results]
        return res_list, 200
    except Exception as e:
        raise Exception("")

注意事项 #

多表关联查询如果用SELECT *，不同表如果有同名字段（比如多张表都有id字段），返回的字典中后面的列会覆盖前面的同名字段，建议给重名字段显式指定别名，例如：

SELECT p.id as pow_id, cc1.id as completed_course_id, tc.id as term_course_id ... 其余字段

内容的提问来源于stack exchange，提问作者RuSs