python - 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.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对象。