如何在cur.execute中使用python返回有效的时间戳值

Question

使用python datetime格式读取并返回sql查询结果，其中时间戳值中包含T。请您提出您的建议。

con = mysql.connector.connect(host=host, user=username, password=password, database=db_name,port=port)
cur = con.cursor()
sqlquery = "select * from testtable"
cur.execute(sqlquery)
result_output = [dict((cur.description[i][0], value) for i, value in 
enumerate(row)) for row in cur.fetchall()]
return JsonResponse({"result":result_output}, safe=False)
Print result : `[{'tabId': 1, 'tab_int': 100, 'tab_char': 'test5', 'tab_decimal': Decimal('99.54'), 'tab_date': datetime.date(2021, 8, 16), 'tab_timestamp': datetime.datetime(2021, 8, 16, 23, 30, 48)}]`

返回结果

{"result"(｛"tabId" 1、"tab_int" 100,"tab_char"test5"tab_decimal"99.54","tab_date"2021 - 08 - 16","tab_timestamp"2021 - 08 - 16 - t23:30:48"}]}

要求输出为"tab_timestamp": "2021-08-16 23:30:48"

Answer 1

您可以遍历响应数据并重新格式化所需的类型。要格式化data/datetime对象，您可以使用strftime(https://docs.python.org/3/library/datetime.html#strftime-and-strptime-format-codes):

from decimal import Decimal
import datetime
data = [{'tabId': 1, 'tab_int': 100, 'tab_char': 'test5',
'tab_decimal': Decimal('99.54'), 'tab_date': datetime.date(2021, 8, 16),
'tab_timestamp': datetime.datetime(2021, 8, 16, 23, 30, 48)}]
for key, value in data[0].items():
if isinstance(value, datetime.datetime):
data[0][key] = data[0][key].strftime("%Y-%m-%d %H:%M:%S")
elif isinstance(value, datetime.date):
data[0][key] = data[0][key].strftime("%Y-%m-%d")
print(data)