在dict列表中，将所有的datetime对象转换为字符串？

Question

我想将所有datetime对象转换为字符串，以便JSON文件输出。我得到一个数据列表，我使用编码器和输出：

class DateTimeEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, (datetime.datetime, datetime.date, datetime.time)):
            return obj.isoformat()
        elif isinstance(obj, datetime.timedelta):
            return (datetime.datetime.min + obj).time().isoformat()
    
        return super(DateTimeEncoder, self).default(obj)

def write_to_file(data_list):
    encoder = datetime_encoder.DateTimeEncoder()
    for record in data_list:
        encoder.encode(record)
    with open('data.json', 'w') as fout:
        json.dumps(data_list, fout)

示例数据列表：

[
    {
        'field1': 'foo', 
        'field2': 'fang', 
        'fielda': 0, 
        'fieldb': 2020-10-10 12:00:00
    }, 
    {
        'field1': 'ab', 
        'field2': 'cd', 
        'fielda': 1, 
        'fieldb': 2020-10-10 12:00:00
    },
    ...
]

问题是，这个编码器没有进行适当的编码，而且我还遍历了每个记录，如果数据列表特别大，那么这些记录是不可伸缩的。我通过psycopg2接受数据，所以也许我可以对传入的任何数据执行编码，而不是事后解析

Answer 1

您可以尝试以下方法：

def v(i):
    if isinstance(i, (datetime.datetime, datetime.date, datetime.time)):
        return i.isoformat()
    elif isinstance(i, datetime.timedelta):
        return (datetime.datetime.min + i).time().isoformat()

    return i

res = json.dumps(data,
                 default=v)

编辑：保存到文件可以通过以下方式完成：

with open('output.json', 'w') as f:
    json.dump(data, f, default=v)

Answer 2

如评论中所述，您可以在选择和/或让postgres为您创建JSON时更改数据类型。
仅显示了3个选项，无需在Python中转换数据即可进行选择：

import datetime as dt 
import psycopg2
import json

conn = psycopg2.connect("dbname=mf port=5959 host=localhost user=mf_usr")
cur = conn.cursor()


sql = """CREATE TEMP TABLE test(num int, creation date)"""
cur.execute(sql)

# insert some dummy data
for num in range(5):
    vals = (num, dt.date.today())
    cur.execute("INSERT INTO test (num, creation) VALUES (%s, %s)", vals)

# Regular select, psycopg2 uses datetime
cur.execute("SELECT * from test")
print(cur.fetchall())

# Select date column as text:
cur.execute("SELECT num, creation::text from test")
print(json.dumps(cur.fetchall()))

# Postgres select directly as JSON output:
cur.execute("SELECT row_to_json(test) from test")
print(json.dumps(cur.fetchall()))

# Select date column as unix timestamp:
cur.execute("SELECT num, extract(epoch from creation) from test")
print(json.dumps(cur.fetchall()))

输出：

[(0, datetime.date(2020, 9, 18)), (1, datetime.date(2020, 9, 18)), (2, datetime.date(2020, 9, 18)), (3, datetime.date(2020, 9, 18)), (4, datetime.date(2020, 9, 18))]
[[0, "2020-09-18"], [1, "2020-09-18"], [2, "2020-09-18"], [3, "2020-09-18"], [4, "2020-09-18"]]
[[{"num": 0, "creation": "2020-09-18"}], [{"num": 1, "creation": "2020-09-18"}], [{"num": 2, "creation": "2020-09-18"}], [{"num": 3, "creation": "2020-09-18"}], [{"num": 4, "creation": "2020-09-18"}]]
[[0, 1600387200.0], [1, 1600387200.0], [2, 1600387200.0], [3, 1600387200.0], [4, 1600387200.0]]