使用pymysql查询数据库数据,由于数据库表的字段里有datatime类型字段,返回的结果形式如下:
datetime.datetime(2019, 3, 19, 10, 6, 6)
那么我们对结果进行json.dumps时,会报错:
TypeError: Object of type datetime is not JSON serializable
此时有三种处理方案
一、重写json函数
import datetime
import json
class DateEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj,datetime.datetime):
return obj.strftime("%Y-%m-%d %H:%M:%S")
else:
return json.JSONEncoder.default(self,obj)
dic={'name':'jack', 'create_time': datetime.datetime(2019, 3, 19, 10, 6, 6)}
print(json.dumps(dic,cls=DateEncoder))
此方法来自:
https://blog.csdn.net/t8116189520/article/details/88657533
二、遍历返回数据,如果是 datetime 则转化为字符串
# 先获取执行结果
data = db_cursor.fetchall()
log.info("### Formatting SQL results ###")
x = 1
for list in data:
y = 1
for value in list:
key = "row" + "-" + str(x) + "-" + str(y)
# 如果返回值是 datetime.datetime 格式,则将其转换成str,否则 json.dumps会报错
if isinstance(value, datetime.datetime):
value = value.strftime('%Y-%m-%d %H:%M:%S')
execute_case[key] = value
y = y+1
x = x+1
execute_case = json.dumps(execute_case, ensure_ascii=False)
核心代码是
if isinstance(value, datetime.datetime):
value = value.strftime('%Y-%m-%d %H:%M:%S')
三、从MySQL下手,让MySQL返回string的数据类型
MySQL做数据类型转换的函数是CAST。于是将SQL语句变成:
SELECT country, CAST(date AS CHAR) AS date from mytable