SQLite 日期存储与转换

13 投票

2 回答

22022 浏览

提问于 2025-04-16 07:35

我在用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。

sqlite database design iso format data integrity date formatting date storage date conversion datetime functions

2 个回答

要注意，SQLite本身并没有专门的日期/时间类型。正如@unutbu所说，你可以让pysqlite/sqlite3模块尝试猜测（实际上这只是个猜测）哪些列或值是日期或时间。不过，SQL表达式可能会让它感到困惑。

SQLite确实有很多日期时间的函数，可以处理不同格式的字符串和数字，包括unixepoch和julian格式，还可以进行一些转换。具体可以查看文档：

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

你可能会发现，让SQLite来处理你需要的日期/时间操作会比把值导入Python后再用Python库来处理更方便。值得注意的是，你可以在SQL表的定义中设置一些限制，比如要求某个字符串值必须存在，或者长度要达到一定标准等等。

回答于 2025-04-16 由 Python大师

分享举报

如果你在使用 sqlite3.connect 时设置了 detect_types=sqlite3.PARSE_DECLTYPES，那么在从数据库中提取数据时，连接会尝试把 SQLite 的数据类型转换成 Python 的数据类型。

这样做非常好，因为用日期时间对象来处理数据比用一些随机的日期字符串要方便得多。后者你还得用 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))

所以，如果你严格要求只将 datetime.date 对象插入到 DATE 字段中，那么在提取数据时就不会遇到问题。

如果你的用户输入的日期格式各不相同，可以看看 dateutil.parser.parse。它可能会帮助你把这些不同的字符串转换成 datetime.datetime 对象。

回答于 2025-04-16 由 Python大师

分享举报

SQLite 日期存储与转换

2 个回答

撰写回答