how to return valid timestamp value using python in cur.execute

Question

While reading and returning sql query result using python datetime format including T in timestamp value. Request you to please give your suggestion.

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)}]`

return result

{ "result": [ { "tabId": 1, "tab_int": 100, "tab_char": "test5", "tab_decimal": "99.54", "tab_date": "2021-08-16", "tab_timestamp": "2021-08-16T23:30:48" }]}

Required output is "tab_timestamp": "2021-08-16 23:30:48"

Answer 1

You could iterate over the response data and reformat the types you want. To format data/datetime object any way you want use 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)