使用.read_sql（）读取MS Access日期/时间值的“无效日期时间格式”

Question

ODBC到python类型绑定在尝试读取旧access.mdb时会导致问题

有一个类似的问题here，但我无法让它在python中工作

我用这个来读我的.mdb

import pandas as pd
import os
import pyodbc

##Source Conn
def get_source_conn():
    driver = '{Microsoft Access Driver (*.mdb)}'
    path = r'//SERVER/database.mdb'
    assert os.path.exists(path)

    return pyodbc.connect(driver=driver, dbq=path, CHARSET='UTF8')

def safeRead(sql):
    try:
        conn = get_source_conn()
        conn.setdecoding(pyodbc.SQL_CHAR, encoding='utf-8')
        conn.setdecoding(pyodbc.SQL_WCHAR, encoding='utf-8')
        # conn.setdecoding(pyodbc.SQL_TYPE_DATE, encoding='utf-8')
   
        df = pd.read_sql(sql, conn)
    finally:
        try:
            conn.close()
        except:
            pass
    return df
    
class InventoryTransaction():

    @classmethod
    def all(cls):
        return safeRead('SELECT * FROM [INVENTORY TRANSACTIONS]')

dfTransactionTarget = InventoryTransaction.all()

上述操作会产生此错误：

DataError: ('22007', '[22007] [Microsoft][ODBC Microsoft Access Driver]Invalid datetime format on column number 2 (TransactionDate) (35) (SQLGetData)')

如果我取消注释我的类型绑定以尝试将SQL_type_DATE转换为字符串，我会得到：

ValueError: Invalid sqltype 91.  Must be SQL_CHAR or SQL_WCHAR or SQL_WMETADATA

如果我可以完全以字符串的形式获得ODBC的结果，我也可以自己执行所有类型强制，因为所讨论的数据集并没有那么大

如何成功读取此数据

***基于@GordThompson建议的其他信息**

1. CStr似乎不处理空值

((
    "SELECT "
    "[TransactionID], "
    "CStr([TransactionDate]) AS someCol, "
    "[ProductID] "
    "FROM [INVENTORY TRANSACTIONS] "
    # "WHERE ISNULL([TransactionDate]) = 0 "
))

输出：

DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Invalid use of Null (-3030) (SQLGetData)')

1. 即使我过滤掉空值，我也没有得到可以使用的东西：

((
    "SELECT "
    "[TransactionID], "
    "CStr([TransactionDate]) AS someCol, "
    "[ProductID] "
    "FROM [INVENTORY TRANSACTIONS] "
    "WHERE [TransactionDate] IS NULL "
))

输出：

DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Invalid use of Null (-3030) (SQLGetData)')
#(same error)

1. 如果我尝试更复杂的东西：

((
    "SELECT "
    "[TransactionID], "
    "IIF(ISNULL([TransactionDate]), '', CStr([TransactionDate])) AS someCol, "
    "[ProductID] "
    "FROM [INVENTORY TRANSACTIONS] "
    "WHERE [TransactionDate] IS NULL "
))

输出：

DataError: ('22018', '[22018] [Microsoft][ODBC Microsoft Access Driver] Invalid procedure call (-3030) (SQLGetData)')