转换
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()
class TypeCast:
def to_dict(self): # 方法一,该方法直接获取数据库原始数值,对于一些特殊字符如时间戳无法转换
return {c.name: getattr(self, c.name, None) for c in self.__table__.columns} # 记得加None(网上一些教程没有加None是无法使用的)
Base.to_dict = to_dict # 如果使用的是flask-sqlalchemy,就使用对应的基类
def to_dict(self): # 方法二,该方法可以将获取结果进行定制,例如如下是将所有非空值输出成str类型
result = {}
for key in self.__mapper__.c.keys():
if getattr(self, key) is not None:
result[key] = str(getattr(self, key))
else:
result[key] = getattr(self, key)
return result
# def to_dict(self): # 方法二定制,将时间戳值转为str类型,其他直接输出
# result = {}
# for key in self.__mapper__.c.keys():
# if type(getattr(self, key)) == datetime.datetime:
# result[key] = str(getattr(self, key))
# else:
# result[key] = getattr(self, key)
# return result
# 配合to_dict一起使用
def to_json(self, all_vendors): # 多条结果时转为list(json)
v = [ven.to_dict() for ven in all_vendors]
return v
使用
- 单条(整行)数据查询
t = session.query(Mock).filter(Mock.id == 1).first()
session.close()
return t.to_dict()
- 多条(整行)数据查询
t = session.query(Mock).filter(Mock.deleted_at == None).all()
session.close()
return self.to_json(t)
其他转换
- 对于查询具体字段的,直接提取即可,例如
t = session.query(Mock.value).filter(Mock.id == 1).first()
session.close()
return t
# value = t.value
# print(value) # aaa
t = session.query(Mock.value).all()
session.close()
return t
# a = []
# for b in t:
# a.append(b.value)
# print(a) # ["xx", "xx", "xx", "xx"]
# print(type(a)) # <class 'list'>
参考: https://www.cnblogs.com/sanduzxcvbnm/p/10220718.html