如何从 sqlite 读取 datetime 作为 datetime 而不是 Python 中的 string？

我使用 Python 2.6.4中的 sqlite3模块在 SQLite 数据库中存储一个 datetime。插入它非常容易，因为 sqlite 会自动将日期转换为字符串。问题是，当读取它时，它返回为一个字符串，但是我需要重新构造原始的 datetime 对象。我该怎么做？

60704 次浏览
小开

It turns out that sqlite3 can do this and it's even documented, kind of - but it's pretty easy to miss or misunderstand.

What I had to do is:

  • Pass the sqlite3.PARSE_COLNAMES option in the .connect() call, eg.
conn = sqlite3.connect(dbFilePath, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)

  • Put the type I wanted into the query - and for datetime, it's not actually "datetime", but "timestamp":

    sql = 'SELECT jobid, startedTime as "[timestamp]" FROM job'


cursor = conn.cursor()
try:
cursor.execute(sql)
return cursor.fetchall()
finally:
cursor.close()

If I pass in "datetime" instead it's silently ignored and I still get a string back. Same if I omit the quotes.

小开
最佳答案

If you declare your column with a type of timestamp, you're in clover:

>>> db = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
>>> c = db.cursor()
>>> c.execute('create table foo (bar integer, baz timestamp)')
<sqlite3.Cursor object at 0x40fc50>
>>> c.execute('insert into foo values(?, ?)', (23, datetime.datetime.now()))
<sqlite3.Cursor object at 0x40fc50>
>>> c.execute('select * from foo')
<sqlite3.Cursor object at 0x40fc50>
>>> c.fetchall()
[(23, datetime.datetime(2009, 12, 1, 19, 31, 1, 40113))]

See? both int (for a column declared integer) and datetime (for a column declared timestamp) survive the round-trip with the type intact.

小开

Note: In Python3, I had to change the SQL to something like:

SELECT jobid, startedTime as "st [timestamp]" FROM job

(I had to explicitly name the column.)


提交答案