python - 如何使用 python 在 sqlite3 中存储时区感知时间戳？

Question

在混合原始日期时间和存储在 sqlite3 上的感知日期时间时遇到一些麻烦。

>>> import datetime, pytz
>>> dt = datetime.datetime.now()
>>> dt1 = pytz.utc.localize(dt)
>>> dt < dt1
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: can't compare offset-naive and offset-aware datetimes

然后在 SQLite3 中，tz 被去掉了:

>>> conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
>>> c = conn.cursor()
>>> c.execute("CREATE TABLE example (id INTEGER PRIMARY KEY, title text, updated timestamp)")
<sqlite3.Cursor object at 0x1058855e0>
>>> conn.commit()
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('naive', datetime.datetime.utcnow()))
<sqlite3.Cursor object at 0x1058855e0>
>>> conn.commit()
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('tz-aware', pytz.utc.localize(datetime.datetime.utcnow())))
<sqlite3.Cursor object at 0x1058855e0>
>>> conn.commit()
>>> c.execute("SELECT * FROM example")
<sqlite3.Cursor object at 0x1058855e0>
>>> c.fetchall()
[(1, u'naive', datetime.datetime(2016, 4, 19, 22, 26, 57, 337504)), (2, u'tz-aware', datetime.datetime(2016, 4, 19, 22, 27, 41, 664158))]
>>>

Answer 1

找到答案 - 虽然使用箭头。

>>> import arrow
>>> def convert_arrowdatetime(s):
...     return arrow.get(s)
...
>>> def adapt_arrowdatetime(adt):
...     return adt.isoformat()
...
>>> sqlite3.register_adapter(arrow.arrow.Arrow, adapt_arrowdatetime)
>>> sqlite3.register_converter("timestamp", convert_arrowdatetime)
>>> c.execute("INSERT INTO example VALUES (NULL, ?, ?)", ('tz-aware', pytz.utc.localize(datetime.datetime.utcnow())))
<sqlite3.Cursor object at 0x1058855e0>
>>> conn.commit()
>>> c.execute("SELECT * FROM example")
<sqlite3.Cursor object at 0x1058855e0>
>>> c.fetchall()
[(1, u'naive', <Arrow [2016-04-19T22:26:57.337504+00:00]>), (2, u'tz-aware', <Arrow [2016-04-19T22:27:41.664158+00:00]>), (3, u'tz-aware', <Arrow [2016-04-19T22:35:12.004054+00:00]>)]
>>>