Python SQLite 日期存储和转换

SQLite date storage and conversion

提问by Alan Harris-Reid

I am having design problems with date storage/retrieval using Python and SQLite.

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

I understand that a SQLite date column stores dates as text in ISO format (ie. '2010-05-25'). So when I display a British date (eg. on a web-page) I convert the date using

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

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

However, when it comes to writing-back data to the table, SQLite is very forgiving and is quite happy to store '25/06/2003'in a date field, but this is not ideal because

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

• I could be left with a mixture of date formats in the same column,

• SQLite's date functions only work with ISO format.

• 我可能会在同一列中留下多种日期格式，

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

Therefore I need to convert the date string back to ISO format before committing, but then I would need a generic function which checks data about to be written in all date fields and converts to ISO if necessary. That sounds a bit tedious to me, but maybe it is inevitable.

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

Are there simpler solutions? Would it be easier to change the date field to a 10-character field and store 'dd/mm/yyyy'throughout the table? This way no conversion is required when reading or writing from the table, and I could use datetime() functions if I needed to perform any date-arithmetic.

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

How have other developers overcome this problem? Any help would be appreciated. For the record, I am using SQLite3 with Python 3.1.

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

采纳答案by unutbu

If you set detect_types=sqlite3.PARSE_DECLTYPESin sqlite3.connect, then the connection will try to convert sqlite data types to Python data types when you draw data out of the database.

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

This is a very good thing since its much nicer to work with datetime objects than random date-like strings which you then have to parse with datetime.datetime.strptimeor dateutil.parser.parse.

这是一件非常好的事情，因为它更适合使用 datetime 对象而不是随机的类似日期的字符串，然后您必须使用datetime.datetime.strptimeor进行解析 dateutil.parser.parse。

Unfortunately, using detect_typesdoes not stop sqlite from acceptingstrings as DATE data, but you will get an error when you try to draw the data out of the database (if it was inserted in some format other than YYYY-MM-DD) because the connection will fail to convert it to a datetime.date object:

不幸的是， usingdetect_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()

But at least the error will alert you to the fact that you've inserted a string for a DATE when you really should be inserting datetime.date objects:

但至少该错误会提醒您，当您确实应该插入 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),)

You may also insert strings as DATE data as long as you use the YYYY-MM-DD format. Notice that although you inserted a string, it comes back out as a datetime.dateobject:

只要您使用 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))

So if you are disciplined about inserting only datetime.dateobjects into the DATEfield, then you'll have no problems later when drawing the data out.

因此，如果您遵守只将datetime.date对象插入DATE字段的纪律，那么以后在绘制数据时就不会出现问题。

If your users are input-ing date data in various formats, check out dateutil.parser.parse. It may be able to help you convert those various strings into datetime.datetimeobjects.

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

回答by Roger Binns

Note that SQLite itself does not have a native date/time type. As @unutbu answered, you can make the pysqlite/sqlite3 module try to guess (and note that it really is a guess) which columns/values are dates/times. SQL expressions will easily confuse it.

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

SQLite does have a variety of date time functions and can work with various strings, numbers in both unixepoch and julian format, and can do transformations. See the documentation:

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

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

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

You may find it more convenient to get SQLite to do the date/time work you need instead of importing the values into Python and using Python libraries to do it. Note that you can put constraints in the SQL table definition for example requiring that string value be present, be a certain length etc.

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