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。听起来是 对我来说有点单调乏味，但也许这是不可避免的。

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

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

Answer 1

请注意，SQLite本身没有本机日期/时间类型。正如@unutbu所回答的，您可以让pysqlite/sqlite3模块尝试猜测（并注意，这实际上是猜测）哪些列/值是日期/时间。SQL表达式很容易混淆它。

SQLite有多种日期时间函数，可以处理unixepoch和julian格式的各种字符串和数字，并且可以进行转换。请参阅文档：

http://www.sqlite.org/lang_datefunc.html

您可能会发现让SQLite完成所需的日期/时间工作比将值导入Python并使用Python库来完成更方便。注意，您可以在SQL表定义中设置约束，例如要求字符串值存在、长度一定等

Answer 2

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

这是一件非常好的事情，因为使用datetime对象比 随机日期字符串，然后必须用它来解析 datetime.datetime.strptime或dateutil.parser.parse。

不幸的是，使用detect_types并不能阻止sqlite接受 字符串作为日期数据，但是当您尝试 从数据库中提取数据（如果插入的格式不是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对象时，日期的字符串：

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格式，也可以将字符串作为日期数据插入。注意，虽然插入了一个字符串，但它返回为一个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))

因此，如果您严格要求只在DATE字段中插入datetime.date对象，那么在以后提取数据时就不会有问题。

如果您的用户正在以各种格式输入日期数据，请签出dateutil.parser.parse。它可以帮助您将这些不同的字符串转换为datetime.datetime对象。