<p>如评论中所述,您可以在选择和/或让postgres为您创建JSON时更改数据类型。<br/>
仅显示了3个选项,无需在Python中转换数据即可进行选择:</p>
<pre><code>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()))
</code></pre>
<p>输出:</p>
<pre><code>[(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]]
</code></pre>