datetime64 columns not supported with sqlite fallback

[jorisvandenbossche]

From http://stackoverflow.com/questions/24389580/pandas-to-sql-method-gives-error-with-date-column

import sqlite3
con = sqlite3.connect(':memory:')
df = pd.DataFrame({'date':pd.date_range('2014-01-01', periods=10)})
df.to_sql('test_datetime', con, index=False, if_exists='append')

gives an InterfaceError: Error binding parameter 0 - probably unsupported type.. While it does work if you have datetime.datetime/date objects (instead of datetime64):

df2 = df.copy()
df2['date'] = pd.DatetimeIndex(df2['date']).date
df2.to_sql('test_datetime', con, index=False, if_exists='append')

and it does also work with a sqlalchemy engine (sqlalchemy converts it to a string):

import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///memory')
df.to_sql('test_datetime', engine, index=False, if_exists='append')

I think this should also work in the fallback mode, options are to convert it to datetime.datetime objects and let sqlite3 do the work to convert it to a string, or to convert it to a string directly ourselves.

[jorisvandenbossche]

Or another option would maybe to register an adapter/converter for Timestamp with sqlite3.register_adapter.

Further, other date/time related issues with SQLite fallback:

• datetime.time does also not work (ENH: add support for datetime.date/time in to_sql (GH6932) #8090)
• datetimes are read as strings (not converted automatically, could probably be done with sqlite3.PARSE_DECLTYPES)

[jorisvandenbossche]

This was also closed by #8208, only the datetime.time is not resolved, but opened a seperate issue about that (as it now fails silently on master)