0

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)

CPU
  • 267
  • 1
  • 6
  • 16
  • Please [edit] your question to show what you get from `print(repr(order_date))` and `print(order_date.dtype)`. – Gord Thompson Dec 06 '17 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 Dec 06 '17 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 Dec 06 '17 at 20:17

1 Answers1

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.)

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • @ Gord Thompson - Do you know the difference between Pandas/Numpy datetime64 and **datetime64[ns]** values? – CPU Dec 06 '17 at 16:33
  • @Mook - I didn't, but it also doesn't seem to make a difference in this case. I have updated my answer. – Gord Thompson Dec 06 '17 at 17:06
  • @ 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. – CPU Dec 06 '17 at 17:13
  • @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`). – Gord Thompson Dec 06 '17 at 17:20
  • @ 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. – CPU Dec 06 '17 at 17:32
  • @Mook - UPDATE was just an example. INSERT works, too. – Gord Thompson Dec 06 '17 at 17:37
  • @ Gord Thompson - I followed your solution and I am getting an INVALID ARGUMENT error for the `return` statement. I believe this is because of `numpy.datetime64('NaT').` How can I convert `NaT` values into Python `datetime` values? – CPU Dec 06 '17 at 20:20
  • @Mook re: "How can I convert `NaT` values into Python `datetime` values?" - AFAIK Python `datetime` objects do not have an equivalent to the 'NaT' (not-a-time) value of a NumPy `datetime64` object, so a direct conversion is not possible. For the purposes of inserting into a database the most obvious choice would be have the conversion function use `np.isnat()` to test for a 'NaT' and return `None` so a NULL would be inserted into the column. If `None` is not an option (e.g., if the column is not NULLable) then some arbitrary past or future datetime value might have to do. – Gord Thompson Dec 06 '17 at 21:05
  • @ Gord Thompson - I tried your advice, but `isnat()` is throwing error for me. `Pd.isnull()` is working instead. I tried the following code to replace `NaT` with `None`, but there are no changes: `df.loc[df['Register_Date'].isnull(), 'Register_Date'] = None`. Do you know why? – CPU Dec 07 '17 at 00:18