SQLite 日期存储和转换 [英] SQLite date storage and conversion

问题描述

我在使用 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() 函数.

其他开发者是如何克服这个问题的?任何帮助将不胜感激.作为记录，我在 Python 3.1 中使用 SQLite3.

解决方案

如果你在 sqlite3.connect 中设置了 detect_types=sqlite3.PARSE_DECLTYPES，然后连接将尝试将 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)')# 不幸的是，这仍然被sqlite接受cur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',))# 但是你以后将无法绘制数据，因为解析会失败尝试:cur.execute("SELECT * FROM foo")除了 ValueError 作为错误:打印(错误)# 以 10 为基数的 int() 的无效文字:'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")数据=cur.fetchall()数据=zip(*数据)[0]打印(数据)# (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")数据=cur.fetchall()数据=zip(*数据)[0]打印(数据)# (datetime.date(2003, 6, 25), datetime.date(2003, 6, 25))

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

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

I am having design problems with date storage/retrieval using Python and 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

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

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

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

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.

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.

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

解决方案

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

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.strptime or dateutil.parser.parse.

Unfortunately, using detect_types does not stop sqlite from accepting strings 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:

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:

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.date object:

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.date objects into the DATE field, then you'll have no problems later when drawing the data out.

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.datetime objects.

这篇关于SQLite 日期存储和转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！