如何将Pandas Datatime列从Pandas datetime64 [ns]转换为Pyodbc SQL_Timestamp [英] How to convert Pandas DataFrame column from Pandas datetime64[ns] to Pyodbc SQL_Timestamp

问题描述

我正在尝试通过pyodbc将Pandas Dataframe填充到空 MS Access 2016 表中.当我尝试将数据帧插入Access中时，出现以下错误消息: pyodbc.dataerror:('22008'，[ODBC Microsoft Access Driver] Datetime字段溢出.

I am trying to populate Pandas Dataframe into empty MS Access 2016 table via pyodbc. I get the following error message when I try to insert Dataframes into Access: pyodbc.dataerror: ('22008', [ODBC Microsoft Access Driver]Datetime field overflow.

研究表明，MS Access 日期/时间数据类型对应于ODBC SQL_TIMESTAMP 数据类型.

Research showed that MS Access Date/Time datatypes correspond to ODBC SQL_TIMESTAMP datatypes.

我尝试了以下方法将datetime64 [ns]转换为SQL_TIMESTAMP:

I tried the following to convert datetime64[ns] to SQL_TIMESTAMP:

import datetime
cursor.execute("INSERT sql statement...VALUES(?)", datetime.datetime(order_date))  

但是，我收到此错误: TypeError:必须为整数(类型为Timestamp).

However, I get this error: TypeError: an integer is required (got type Timestamp).

要成功将Pandas/Numpy的datetime64 [ns]值填充到Access表中，我需要做些什么?我需要将它们转换为SQL_TIMESTAMP以及如何转换吗?

---

编辑: 我尝试在下面运行Gord Thompson的解决方案，但遇到此错误:

What do I need to do in order to successfully populate Pandas/Numpy's datetime64[ns] values into Access tables? Do I need to convert them into SQL_TIMESTAMP and how?

---

I tried running Gord Thompson's solution below and I am running into this error:

import datetime

dt = dt64_to_datetime(dt_ns)

>> AttributeError:'datetime' has no attribute 'utcfromtimestamp'

此错误背后的原因是什么? (在pyodbc 4.0.17，Python 3.6.2，MS Access 2016上测试)

What is the reason behind this error? (Tested on pyodbc 4.0.17, Python 3.6.2, MS Access 2016)

推荐答案

要成功将Pandas/Numpy的datetime64 [ns]值填充到Access表中，我需要做些什么?我需要将它们转换为SQL_TIMESTAMP以及如何转换吗?

What do I need to do in order to successfully populate Pandas/Numpy's datetime64[ns] values into Access tables? Do I need to convert them into SQL_TIMESTAMP and how?

如此绝佳答案所示，您可能需要将numpy.datetime64值转换为Python 值，也许像这样:

As illustrated in this excellent answer, you'll probably need to convert the numpy.datetime64 values to Python datetime values, perhaps like this:

def dt64_to_datetime(dt64):
    if np.isnat(dt64):
        return None
    else:
        unix_epoch = np.datetime64(0, 's')
        one_second = np.timedelta64(1, 's')
        seconds_since_epoch = (dt64 - unix_epoch) / one_second
        return datetime.utcfromtimestamp(seconds_since_epoch)

示例用法:

dt_ns = np.datetime64('2017-10-24 05:34:20.123456').astype('datetime64[ns]')
print(repr(dt_ns))  # numpy.datetime64('2017-10-24T05:34:20.123456000')
print(f'dt_ns.dtype: {dt_ns.dtype}')  # dt_ns.dtype: datetime64[ns]
dt = dt64_to_datetime(dt_ns)
print(repr(dt))  # datetime.datetime(2017, 10, 24, 5, 34, 20, 123456)

sql = "UPDATE tablename SET datetimefield = ? WHERE id=1"
params = (dt,)
crsr.execute(sql, params)

(已在pyodbc 4.0.21和Access 2010中进行了测试.)

(Tested with pyodbc 4.0.21 and Access 2010.)

这篇关于如何将Pandas Datatime列从Pandas datetime64 [ns]转换为Pyodbc SQL_Timestamp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！