pyodbc查询Access时间字段技巧

理解Access时间数据类型的内部机制

在使用pyodbc连接MS Access数据库并查询其时间（TIME）数据类型字段时，开发者可能会观察到返回的结果并非预期的HH:MM:SS格式字符串，而是一个完整的datetime.datetime对象，其中日期部分固定为1899-12-30。例如，数据库中存储的12:14:29可能会被pyodbc解析为datetime.datetime(1899, 12, 30, 12, 14, 29)。

这种行为并非pyodbc的错误，而是MS Access数据库内部数据类型处理方式的体现。Access数据库并没有一个独立的TIME数据类型。所有日期和时间信息都存储在DateTime数据类型中。当用户在Access中定义一个字段为TIME类型时，Access实际上将其视为一个DateTime字段，并且如果只指定了时间部分而没有明确指定日期，Access会默认使用1899-12-30作为日期部分。这个日期是Access内部用于表示“只有时间”或“零日期”的参考点。因此，pyodbc忠实地读取并返回了Access底层存储的完整datetime对象。

示例：查询与初始结果

考虑以下MS Access数据库结构，其中包含一个Insersion表，其time_inserted字段被定义为TIME类型：

CREATE TABLE Insersion (
insersionID COUNTER PRIMARY KEY, 
date_inserted DATE, 
time_inserted TIME, 
floaterID INT, 
wholeID INT, 
FOREIGN KEY (floaterID) REFERENCES [Float] (floaterID), 
FOREIGN KEY (wholeID) REFERENCES [whole] (wholeID), 
conversionType VARCHAR(30));

当使用pyodbc执行如下SQL查询时：

SELECT [F].float, [I].time_inserted
FROM [Float] AS F, Insersion AS I
WHERE F.floaterID = I.floaterID;

Python代码片段：

import pyodbc
import datetime # 导入datetime模块以便处理datetime对象

# 假设conn已经是一个有效的pyodbc连接对象
# conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=your_database.accdb;')

cursor = conn.cursor()
query = """
SELECT [F].float, [I].time_inserted
FROM [Float] AS F, Insersion AS I
WHERE F.floaterID = I.floaterID;
"""
cursor.execute(query)

for row in cursor.fetchall():
     print(row)

输出结果将是类似以下格式的datetime.datetime对象：

(some_float_value, datetime.datetime(1899, 12, 30, 12, 14, 29))
(another_float_value, datetime.datetime(1899, 12, 30, 12, 16, 39))

解决方案：提取纯粹的时间信息

既然我们知道pyodbc返回的是一个标准的datetime.datetime对象，并且日期部分1899-12-30可以被安全地忽略，那么解决方案就非常简单：利用datetime对象的.time()方法来提取纯粹的时间部分。这个方法会返回一个datetime.time对象，其中只包含小时、分钟、秒、微秒等时间信息。

修改后的Python代码示例如下：

import pyodbc
import datetime

# 假设conn已经是一个有效的pyodbc连接对象
# conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=your_database.accdb;')

cursor = conn.cursor()
query = """
SELECT [F].float, [I].time_inserted
FROM [Float] AS F, Insersion AS I
WHERE F.floaterID = I.floaterID;
"""
cursor.execute(query)

for row in cursor.fetchall():
    float_value = row[0]
    time_datetime_obj = row[1]

    # 从datetime对象中提取纯粹的时间部分
    pure_time = time_datetime_obj.time()

    print(f"Float Value: {float_value}, Pure Time: {pure_time}")
    # 如果需要格式化为字符串，可以使用strftime
    # print(f"Float Value: {float_value}, Formatted Time: {pure_time.strftime('%H:%M:%S')}")

通过上述修改，pure_time变量将只包含时间信息，例如12:14:29。如果需要将其表示为特定格式的字符串，可以进一步使用strftime('%H:%M:%S')方法。

注意事项

• 数据类型认知： 始终理解底层数据库的数据类型如何映射到编程语言的数据类型。对于Access的TIME字段，它在Python中表现为datetime.datetime是其内部机制的正常反映。
• 兼容性： 这种处理方式对于所有通过pyodbc从Access数据库获取时间字段的情况都适用。
• 性能考量： 提取时间部分是一个轻量级操作，对性能影响微乎其微。
• 其他数据库： 不同的数据库管理系统（如SQL Server, MySQL, PostgreSQL）对时间数据类型有不同的实现和映射规则。在处理其他数据库的时间类型时，需要查阅其相应的驱动程序文档。
• 日期处理： 如果你查询的是Access的DATE字段（同样是DateTime类型），它通常会以YYYY-MM-DD 00:00:00的形式返回。你可以使用datetime对象的.date()方法来提取纯粹的日期部分。

通过理解Access数据库处理时间数据的独特方式，并结合Python datetime模块的强大功能，开发者可以有效地处理pyodbc从Access获取的时间数据，确保应用程序的逻辑正确性和数据准确性。