How to dynamically modify my datetime object inside of json with python?

user_15529874 0
Viewed 171

I am trying to convert my JSON output datetime attribute from something like this: "path": "2021-03-30 19:35:51" to something like this -> "path": "FA\21\03\30\project".

My approach:

  1. Query the MYSQL database to fetch the desired attributes
  2. Save results as dict
  3. Convert dict to JSON
  4. Customize JSON datetime (path) object

I tried accessing the JSON object for conversion, but I keep running into this Error: AttributeError: 'str' object has no attribute

Input:

pid = 1 
mycursor = db.cursor(dictionary=True)
mycursor.execute("SELECT o.id as id_room, l.id as id_layer, po.id as id_point, c_x_axis, c_y_axis, c_z_axis, p.id_project as pid, p.c_date_created as path FROM optimization.tbl_room o INNER JOIN platform.tbl_project p ON o.fk_project = p.id_project INNER JOIN optimization.tbl_layer l ON l.fk_room = o.id INNER JOIN optimization.tbl_point po ON po.id = l.fk_center_point WHERE id_project = " + str(pid))
results = mycursor.fetchall()
to_json = json.dumps(results, default=str)
print(to_json)

Output results:

[{'id_room': 1, 'id_layer': 1, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 2, 'id_layer': 2, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 3, 'id_layer': 3, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 4, 'id_layer': 4, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 5, 'id_layer': 5, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 6, 'id_layer': 6, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 7, 'id_layer': 7, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 8, 'id_layer': 8, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 9, 'id_layer': 9, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 10, 'id_layer': 10, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 11, 'id_layer': 11, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 12, 'id_layer': 12, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 13, 'id_layer': 13, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}, {'id_room': 14, 'id_layer': 14, 'id_point': 1, 'c_x_axis': -3.38823, 'c_y_axis': 2.41626, 'c_z_axis': 0.0, 'pid': 1, 'path': datetime.datetime(2021, 3, 30, 19, 35, 51)}]

Output to_json:

{"id_room": 1, "id_layer": 1, "id_point": 1, "c_x_axis": -3.38823, "c_y_axis": 2.41626, "c_z_axis": 0.0, "pid": 1, "path": "2021-03-30 19:35:51"}

Any help on how to progress is much appreciated.

mysql python json
1 Answers

just modify your sql query and use DATE_FORMAT to get the desired output:

"SELECT o.id as id_room, l.id as id_layer, po.id as id_point, c_x_axis, c_y_axis, c_z_axis, p.id_project as pid, 
DATE_FORMAT(p.c_date_created,'FA\\%Y\\%m\\%d\\project') as path 
FROM optimization.tbl_room o INNER JOIN platform.tbl_project p ON o.fk_project = p.id_project INNER JOIN optimization.tbl_layer l ON l.fk_room = o.id INNER JOIN optimization.tbl_point po ON po.id = l.fk_center_point
 WHERE id_project = " + str(pid))

as well, you should use %s to identify variables, in your execute command. So your code would look like this, depending of which library you use to access MySQL:

pid = 1 
sql_query = """
SELECT o.id as id_room, l.id as id_layer, po.id as id_point, c_x_axis, c_y_axis, c_z_axis, p.id_project as pid, 

DATE_FORMAT(p.c_date_created,'FA\\%Y\\%m\\%d\\project') as path 

FROM optimization.tbl_room o INNER JOIN platform.tbl_project p ON o.fk_project = p.id_project INNER JOIN optimization.tbl_layer l ON l.fk_room = o.id INNER JOIN optimization.tbl_point po ON po.id = l.fk_center_point 

WHERE id_project = %s

"""
mycursor = db.cursor(dictionary=True) # you should use "_" to separate words eg:"my_cursor"
variables:tuple = (pid,) 
# be aware of the ","
# if you had n variables you would write 
# variables:tuple = (variable_1, variable_2,..., variable_n)

mycursor.execute(sql_query, variables) 
results = mycursor.fetchall()
to_json = json.dumps(results, default=str) # avoid using function alike wording and prefer: "result_to_json"
print(to_json)

refs:

Get mySQL MONTH() to use leading zeros?

https://github.com/pyeve/eve/issues/267

And in a general manner you may not want to twick json results, as when it fails it gives you information/raise error. The way I usually handle specific cases (when i know a pattern of information will be followed), is by providing json.dump a class to handle specific python classes conversion to string):

json_dump = json.dumps(list_of_dicts, cls=UUIDEncoder)

using:

from uuid import UUID # which could have been datetime
import json

class UUIDEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, UUID):
            # if the obj is uuid, we simply return the value of uuid
            return obj.hex
        return json.JSONEncoder.default(self, obj)

json_dump = json.dumps(list_of_dicts, cls=UUIDEncoder)


Last, and depending of the flexibility you have and since you are flagged as a "New Contributor", you may want to use PostGresSQL, rather than MySQL. 
It gives you much  more functions that you may want to use at a later stage (only a Nota Bene).

I hope this kicks you off.
Best
user_15363430
Related