While reading sql query pandas dataframe showing correct date and timestamp format. but while converting df to json using pd.to_json date and timestamp format showing wrong format.
import json
from ast import literal_eval
sql_data = pd.read_sql_query(''' select * from sample_table ''',con)
sql_data
tabId tab_int tab_char tab_decimal tab_date tab_timestamp
1 100 test5 99.54 2021-08-16 2021-08-16 23:30:48
2 20 test1 85.24 2021-08-16 2021-08-16 23:31:10
json_data = sql_data.to_json(orient="records", date_format='iso')
Output :
[{"tabId":1,"tab_int":100,"tab_char":"test5","tab_decimal":99.54,"tab_date":"2021-08-16T00:00:00.000Z","tab_timestamp":"2021-08-16T23:30:48.000Z"},{"tabId":2,"tab_int":20,"tab_char":"test1","tab_decimal":85.24,"tab_date":"2021-08-16T00:00:00.000Z","tab_timestamp":"2021-08-16T23:31:10.000Z"}]
Expected Output format like :
[{"tabId":1,"tab_int":100,"tab_char":"test5","tab_decimal":99.54,"tab_date":"2021-08-16","tab_timestamp":"2021-08-16 23:30:48"},{"tabId":2,"tab_int":20,"tab_char":"test1","tab_decimal":85.24,"tab_date":"2021-08-16","tab_timestamp":"2021-08-16 23:31:10"}]
If I know the columns name means I can able to achieve using below method before converting to json.
sql_data['tab_timestamp'] = sql_data['tab_timestamp'].dt.strftime('%Y-%m-%d %H:%M:%S')
But, I need to read data from random tables and wants to convert that. That time I don't know which is the right column. Request you to please give any suggestion for this.
df.assign(**df.select_dtypes(['datetime']).astype(str).to_dict('list') ).to_json(orient="records")more is here