datetime64 columns not supported with sqlite fallback · Issue #7567 · pandas-dev/pandas (original) (raw)

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.