如何在SQLite中存取Python datetime对象及类型丢失问题解析

我之前也踩过这个坑，折腾了好一阵子才搞明白问题所在，下面给你一步步讲清楚解决方法，以及为什么会出现类型丢失的情况。

一、正确存储和取回datetime对象的方法 #

要让SQLite和Python的datetime对象无缝对接，关键是在连接数据库时设置类型检测参数，下面是亲测有效的代码示例：

import sqlite3
from datetime import datetime

# 连接数据库时必须指定detect_types参数，开启类型自动转换
conn = sqlite3.connect('test.db', detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES)
cursor = conn.cursor()

# 创建带有TIMESTAMP类型字段的表（SQLite会映射为TEXT存储，但声明类型有助于自动转换）
cursor.execute('''CREATE TABLE IF NOT EXISTS events
                  (id INTEGER PRIMARY KEY, event_time TIMESTAMP)''')

# 直接插入Python datetime对象即可，无需手动转换
current_time = datetime.now()
cursor.execute("INSERT INTO events (event_time) VALUES (?)", (current_time,))
conn.commit()

# 查询时会自动将数据库中的字符串转换回datetime对象
cursor.execute("SELECT event_time FROM events WHERE id = ?", (1,))
result = cursor.fetchone()
print(type(result[0]))  # 输出: <class 'datetime.datetime'>
print(result[0])  # 输出类似: 2024-05-20 14:30:00.123456

# 记得关闭连接
conn.close()

这里的核心是detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES：

• PARSE_DECLTYPES会根据表字段声明的类型（比如这里的TIMESTAMP）自动转换查询结果
• PARSE_COLNAMES则支持通过列名注释来指定转换类型（比如查询时用SELECT event_time AS "event_time [timestamp]"）

二、为什么插入后会丢失类型？ #

这其实是SQLite的特性导致的：

1. SQLite是动态类型数据库，它没有专门的datetime数据类型，默认会把Python的datetime对象转换成ISO格式的字符串存储（也可以选择存为时间戳整数或浮点数）
2. 数据库本身不会记录这个值原本是Python的datetime类型，只是存成了字符串/数字
3. 如果连接数据库时没有设置类型检测参数，sqlite3模块就只会返回原始的存储类型（比如字符串），不会自动转换回datetime对象，这就造成了“类型丢失”的错觉

如果不想设置detect_types，也可以手动把查询到的字符串转成datetime对象，比如：

from datetime import datetime
# 假设result[0]是查询到的字符串
recovered_time = datetime.strptime(result[0], '%Y-%m-%d %H:%M:%S.%f')

但显然自动转换的方式更省心。

内容的提问来源于stack exchange，提问作者Basj