mysql查询结果转字典列表

pymysql

    def get_content_list(self,num):
        sql = """
        SELECT * FROM KTS.content_list WHERE contentType={}
        """.format(num)

        try:
            conn = self.conn
            cursor = conn.cursor()  # 创建游标
            cursor.execute(sql)
            data = cursor.fetchall()
            index_dict = dict()
            index = 0
            for desc in cursor.description:
                index_dict[desc[0]] = index
                index = index + 1

            res = []
            for datai in data:
                resi = dict()
                for indexi in index_dict:
                    resi[indexi] = datai[index_dict[indexi]]
                res.append(resi)
            return res
        except BaseException as e:
            print(e)
            raise e

sqlsqlalchemy

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey, BigInteger, Text
# from sqlalchemy.orm import relationship
import datetime

Base = declarative_base()


def to_dict(self):
    return {c.name: getattr(self, c.name, None) for c in self.__table__.columns}


Base.to_dict = to_dict



def getContentList(taskLevelList,typy_id):
    session=sessionmaker(ENGINE)()
    taskLevelList=[taskLevel.get('sys_id') for taskLevel in taskLevelList]
    contents=session.query(ContentList).filter(ContentList.taskLevel.in_(taskLevelList),ContentList.contentType==int(typy_id)).all()
    # contents=session.query(ContentList).filter(ContentList.taskLevel.in_(taskLevelList),ContentList.sys_id>1240).all()
    # contents=session.query(ContentList).filter(ContentList.taskLevel.in_(taskLevelList)).all()
    contentList=[content.to_dict() for content in contents]
    session.close()
    return contentList

 def __get_conn(self):
        try:
            conn = pymysql.connect(host=host, port=port, user=user, password=password,
                                db=db, cursorclass=pymysql.cursors.DictCursor)
        except Exception as e:
            print(e)
        finally:
            return conn