Python 2.7, MySQL, JSON, & DateTime - How can I remove the T between the Date and Time in the timestamp field?

Question

I have a MySQL database with a few tables. I wrote a python script to dump some of the tables data into a JSON file. I am a bit confused with dumping the date and time stamp.

Here is the code sample, conversion.py:

import MySQLdb
import json
import collections
from datetime import date, datetime

#connect to database
conn = MySQLdb.connect(host= "localhost", user="root", passwd="root", db="testdb")

#Fetch rows
sql  = "SELECT * from offices"
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()

data = []

def json_serial(obj):
    """JSON serializer for objects not serializable by default json code"""

    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    raise TypeError ("Type %s not serializable" % type(obj))


for row in rows:
    d = collections.OrderedDict()
    d['officeCode'] = row[0]
    d['city'] = row[1]
    d['phone'] = row[2]
    d['eff_date'] = row[3]
    d['lastupdatedby'] = row[4]
    d['state'] = row[5]
    d['country'] = row[6]
    d['postalcode'] = row[7]
    d['territory'] = row[8]
    data.append(d)


with open('data.json', 'w') as outfile:

    json.dump(data, outfile, default=json_serial)

conn.close()

When I execute this code, a JSON file is created which is fine. I have a problem with two fields, eff_date which is a date type in database and lastupdatedby is a timestamp type in the database.

"eff_date": "2015-09-23" 

"lastupdatedby": "2016-08019T08:13:53"

So, in my JSON file, eff_time is created fine but lastupdatedby is getting a T in middle of date and time as shown above. But, in my actual database there is no T between the date and time. I would like to get rid of that T because I am planning to dump this file into a different database and I don't think it will accept that format.

Any help will be much appreciated.

spencer7593 spencer7593 · Accepted Answer · 2017-11-16T00:19:42

The T between the date and time is per the ISO 8601 format.

And that's format returned by the datetime.isoformat function, found in the code here:

return obj.isoformat()

(That happens to be the format that Javascript is expecting.)

If we want to return a string different format, we probably need to use a different function, e.g. strftime function in place of isoformat.

If isoformat is working for the date objects, leave that alone. Just do the strftime for a datetime object.

The format string "%Y-%m-%d %H:%M:%S" might suit your needs.

https://docs.python.org/2/library/datetime.html#strftime-strptime-behavior

Python 2.7, MySQL, JSON, & DateTime - How can I remove the T between the Date and Time in the timestamp field?

1 Answers