Writing datetime.date with to_sql throws OperationalError
I am using the latest pandas 14.1 and using to_sql method to write to MS SQL Server 2008 v2. Using SQLalchemy as an engine. The following datafile 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 the 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 should be date objects, not datetime objects. 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 it might be a bug with newly created functionality in pandas 14.1 if dates are used in a method. Not sure though.
source to share
UPDATE: since pandas 0.15, to_sql
supports writing columns datetime.date
and datetime.time
( https://github.com/pydata/pandas/pull/8090 , now in development version).
Type support datetime.date
and datetime.time
at the moment (0.14.1) is not yet implemented (just for type datetime64
and datetime.datetime
will be converted to this), but it should be easy to add this (there is a problem for it: https://github.com/pydata/pandas / issues / 6932 ).
The problem is that it is currently to_sql
creating a text type column in the database for the column datetime.date
(as it does for all object
dtype columns ). For this reason, you will receive the above error message.
A possible solution for this would be to create a database yourself and then add a framework to it.
source to share