SQLite日期存储和转换

Question

我在使用Python和SQLite进行日期存储/检索时遇到设计问题。

我了解SQLite日期列将日期存储为ISO格式的文本（即'2010-05-25'）。因此，当我显示英国日期（例如，在网页上）时，我使用

datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y')

但是，当涉及将数据写回表时，SQLite非常宽容并且非常乐意将其存储'25/06/2003'在日期字段中，但这并不理想，因为

• 我可能在同一列中混用了多种日期格式，

• SQLite的日期函数仅适用于ISO格式。

因此，在提交之前，我需要将日期字符串转换回ISO格式，但是随后，我将需要一个通用函数，该函数检查要在所有日期字段中写入的数据，并在必要时转换为ISO。对我来说这听起来有些乏味，但也许是不可避免的。

有更简单的解决方案吗？将日期字段更改为10个字符的字段并'dd/mm/yyyy'在整个表格中存储会更容易吗？这样，从表读取或写入数据时无需进行任何转换，并且如果需要执行任何日期算术，则可以使用datetime（）函数。

其他开发人员如何克服这个问题？任何帮助，将不胜感激。作为记录，我将SQLite3与Python 3.1结合使用。

Answer 1

如果设置detect_types=sqlite3.PARSE_DECLTYPES为sqlite3.connect，则当您从数据库中提取数据时，连接将尝试将sqlite数据类型转换为Python数据类型。

这是因为它更漂亮的工作比随机日期字符串一样，你再有解析datetime对象一件非常好的事情
datetime.datetime.strptime或dateutil.parser.parse。

不幸的是，使用detect_types并不能阻止sqlite将 字符串作为DATE数据 接受 ，但是当您尝试从数据库中提取数据时（如果以YYYY-
MM-DD以外的其他格式插入数据），将会收到错误消息，因为将无法将其转换为datetime.date对象：

conn=sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES) 
cur=conn.cursor()
cur.execute('CREATE TABLE foo(bar DATE)')
# Unfortunately, this is still accepted by sqlite
cur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',))

# But you won't be able to draw the data out later because parsing will fail
try:
    cur.execute("SELECT * FROM foo")
except ValueError as err:
    print(err)
    # invalid literal for int() with base 10: '25/06/2003'
    conn.rollback()

但是，至少该错误会提醒您，当您确实应该插入datetime.date对象时，已经为DATE插入了一个字符串：

cur.execute("INSERT INTO foo(bar) VALUES (?)",(datetime.date(2003,6,25),))
cur.execute("SELECT ALL * FROM foo")
data=cur.fetchall()
data=zip(*data)[0]
print(data)
# (datetime.date(2003, 6, 25),)

只要您使用YYYY-MM-DD格式，就可以将字符串作为DATE数据插入。请注意，尽管您插入了一个字符串，但它作为一个datetime.date对象返回：

cur.execute("INSERT INTO foo(bar) VALUES (?)",('2003-06-25',))
cur.execute("SELECT ALL * FROM foo")
data=cur.fetchall()
data=zip(*data)[0]
print(data)
# (datetime.date(2003, 6, 25), datetime.date(2003, 6, 25))

因此，如果您受训只将datetime.date对象插入DATE字段，那么以后将数据提取出来就不会有问题。

如果您的用户以各种格式输入日期数据，请检出dateutil.parser.parse。它可能能够帮助您将各种字符串转换为datetime.datetime对象。