python - 如何使用 python 在 json 中动态修改我的 datetime 对象？

Question

我正在尝试将我的 JSON 输出日期时间属性从以下内容转换为:"path": "2021-03-30 19:35:51"到这样的东西 -> "path": "FA\21\03\30\project".

我的方法:

1. 查询 MYSQL 数据库以获取所需的属性
2. 将结果保存为字典
3. 字典转JSON
4. 自定义 JSON 日期时间(路径)对象

我尝试访问 JSON 对象进行转换，但我一直遇到此错误:AttributeError: 'str' 对象没有属性

输入:

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)

输出结果:

[{'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)}]

输出到_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"}

非常感谢任何有关如何取得进展的帮助。

Answer 1

只需修改您的 sql 查询并使用 DATE_FORMAT 即可获得所需的输出:

"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))

同样，您应该在执行命令中使用 %s 来标识变量。所以你的代码看起来像这样，这取决于你用来访问 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)

引用:

Get mySQL MONTH() to use leading zeros?

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

并且在一般情况下，您可能不希望 twick json 结果，因为当它失败时它会为您提供信息/引发错误。我通常处理特定情况的方式(当我知道将遵循某种信息模式时)是通过提供 json.dump 一个类来处理特定的 python 类到字符串的转换:

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

使用:

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