sqlalchemy查询的结果querySet序列化为dict或者list

最新推荐文章于 2025-03-09 22:26:42 发布

zy_whynot

最新推荐文章于 2025-03-09 22:26:42 发布

阅读量3.4k

点赞数 3

分类专栏： sqlalchemy

本文链接：https://blog.youkuaiyun.com/zy_whynot/article/details/119734267

版权

sqlalchemy 专栏收录该内容

3 篇文章

订阅专栏

参考：SQLAlchemy将查询结果对象转为dict/list

连接数据库

mysql = "mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8".format(mysql_user, mysql_password, mysql_host, mysql_port, mysql_database)

# 创建对象的基类:
Base = declarative_base()
engine = create_engine(mysql)
Session = sessionmaker(bind=engine)
session = Session()

创建模型

from sqlalchemy import Column, DateTime, String, BigInteger

class Student(Base):
	id = Column(BigInteger, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    create_time = Column(DateTime, nullable=False, default=datetime.now)

把查询的querySet转化为dict

def query_set_to_dict(obj, conv=False):
    """
    Serialize SQLAlchemy Query Set to Dict
    conv: 是否将datetime转换成google.protobuf的Timestamp, true:是，false:否
    """
    obj_dict = {}
    for column in obj.__table__.columns.keys():
        val = getattr(obj, column)
        if isinstance(val, Decimal):
            val = float(val)
        if isinstance(val, datetime):
            if conv:
                val = Timestamp(seconds=int(val.timestamp()))
            else:
                val = val.strftime("%Y-%m-%d %H:%M:%S")
        obj_dict[column] = val
    return obj_dict

把查询的querySet转化为list

def query_set_to_list(querySet, conv=False):
    """
    Serialize SQLAlchemy Query Set to list
    conv: 是否将datetime转换成google.protobuf的Timestamp, true:是，false:否
    """
    ret_list = []
    for obj in querySet:
        ret_dict = query_set_to_dict(obj, conv=conv)
        ret_list.append(ret_dict)
    return ret_list

把查询的querySet转化为json
参考：sqlalchemy查询结果转为json并通过restapi接口返回的解决方案

def to_json(obj):
    dict = obj.__dict__
    if "_sa_instance_state" in dict:
        del dict["_sa_instance_state"]
        return dict