CODEWITHSUNDEEP
pythonsqlsql-serverpandas
Joop
Joop

Reputation: 8108

Writing datetime.date with to_sql throws OperationalError

I am using latest pandas 14.1 and using the to_sql method to write to a MS SQL Server 2008 v2 server. Using SQLalchemy as engine. The following dataframe with datetime objects works as expected.

#test DataFrame
df1 = pd.DataFrame(index=range(10))
df1['A'] = 'Text'
df1['date_test'] = datetime.datetime(2014,1,1)

code used to write to database:

import sqlalchemy
engine = sqlalchemy.create_engine('mssql+pymssql://XXXXXXX')
df1.to_sql('test', engine, if_exists='replace')

For business reasons the data in the database need to be date objects and not datetime. If I use:

#test DataFrame
df2 = pd.DataFrame(index=range(10))
df2['A'] = 'Text'
df2['date_test'] = datetime.date(2014,1,1)  # date not datetime

the to_sql method gives a very long error message:

OperationalError: (OperationalError) (206, 'Operand type clash: datetime is incompatible 
with textDB-Lib error message 206, severity 16:\nGeneral SQL Server error:
Check messages from the SQL Server.......

My first suspicion is that this might be a bug with the new created functionality in Pandas 14.1 if dates are used in method. Not sure though.

Upvotes: 3

Views: 3000

Answers (1)

joris
joris

Reputation: 139242

UPDATE: starting from pandas 0.15, to_sql supports writing columns of datetime.date and datetime.time (https://github.com/pydata/pandas/pull/8090, now in development version).

Support for datetime.date and datetime.time types is at the moment (0.14.1) not yet implemented (only for the datetime64 type, and datetime.datetime will be converted to that), but it should be easy to add this (there is an issue for it: https://github.com/pydata/pandas/issues/6932).

The problem is that at the moment, to_sql creates a column of text type in the database for the datetime.date column (as is done for all columns of object dtype). For this reason you get the above error message.
A possible solution for now would be to create the database yourself, and then append the dataframe to it.

Upvotes: 1

Related Questions