How to convert Pandas DataFrame column from Pandas datetime64[ns] to Pyodbc SQL_Timestamp

Question

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.

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

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

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

However, I get this error: TypeError: an integer is required (got type 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?

EDIT: 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'

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

Please edit your question to show what you get from print(repr(order_date)) and print(order_date.dtype). — Gord Thompson
– Gord Thompson, Commented Dec 6, 2017 at 17:34
I used from datetime import datetime. If you just import datetime then you'll probably need to use datetime.datetime.utcfromtimestamp. — Gord Thompson
– Gord Thompson, Commented Dec 6, 2017 at 17:56
@ Gord Thompson - I followed your solution, but I get the following error: OSError: [Errno 22] Invalid argument. I believe this is because an input value is numpy.datetime64('NaT'). What can I do about NaT values? — CPU
– CPU, Commented Dec 6, 2017 at 20:17

Gord Thompson · Accepted Answer · 2017-12-06 21:11:38Z

1

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?

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)

Example usage:

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)

(Tested with pyodbc 4.0.21 and Access 2010.)

edited Dec 6, 2017 at 21:11

answered Dec 6, 2017 at 16:24

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

CPU Over a year ago

@ Gord Thompson - Do you know the difference between Pandas/Numpy datetime64 and datetime64[ns] values?

Gord Thompson Over a year ago

@Mook - I didn't, but it also doesn't seem to make a difference in this case. I have updated my answer.

CPU Over a year ago

@ Gord Thompson - I am confused with your solution. When I print the datetime64[ns] values, it looks like this: 2017-01-01. Also, I am trying to insert the Order_date column values into Access by using SQL INSERT statement.

Gord Thompson Over a year ago

@Mook - I'm confused, too, because np.datetime64('2017-01-01').astype('datetime64[ns]') prints as numpy.datetime64('2017-01-01T00:00:00.000000000'), and it also works for the UPDATE (after being converted to datetime).

CPU Over a year ago

@ Gord Thompson - I updated my question to try to clear up some confusion. I am trying to populate this Pandas column into an empty Access table. So SQL UPDATE statement may not work.

|

Collectives™ on Stack Overflow

How to convert Pandas DataFrame column from Pandas datetime64[ns] to Pyodbc SQL_Timestamp

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related