BUG: add support for writing datetime.date and datetime.time columns using to_sql · Issue #6932 · pandas-dev/pandas (original) (raw)

Hi-
the following commands throw a DataError --

con = sqlalchemy.create_engine("mssql+pyodbc://server?driver=SQL Server Native Client 11.0") df = pd.DataFrame([datetime.time(7,10), datetime.time(7,20)], columns="a") sql.to_sql(df, "TBL_TEMP", con, index=False)

throws the following error:

Traceback (most recent call last):

File "", line 1, in sql.to_sql(df, "TBL_TEMP3", con, index=False)

File "N:\Python\sql.py", line 399, in to_sql index_label=index_label)

File "N:\Python\sql.py", line 774, in to_sql table.insert()

File "N:\Python\sql.py", line 538, in insert self.pd_sql.execute(ins, data_list)

File "N:\Python\sql.py", line 734, in execute return self.engine.execute(*args, **kwargs)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1598, in execute return connection.execute(statement, *multiparams, **params)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 664, in execute return meth(self, multiparams, params)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\sql\elements.py", line 282, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 761, in _execute_clauseelement compiled_sql, distilled_params

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 874, in _execute_context context)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 1023, in _handle_dbapi_exception exc_info

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\util\compat.py", line 174, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=exc_value)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\util\compat.py", line 167, in reraise raise value.with_traceback(tb)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\base.py", line 856, in _execute_context context)

File "C:\WinPython3.3.3.2\python-3.3.3.amd64\lib\site-packages\sqlalchemy\engine\default.py", line 385, in do_executemany cursor.executemany(statement, parameters)

DataError: (DataError) ('22018', '[22018] [Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: time is incompatible with text (206) (SQLExecDirectW)') 'INSERT INTO [TBL_TEMP3] (a) VALUES (?)' ((datetime.time(7, 10),), (datetime.time(7, 20),))

I have two columns, one with datetime.date and one with datetime.time, which both exhibited this problem. I force-converted the datetime.date column via pd.to_datetime into a datetimeindex, which to_sql/sqlalchemy correctly formats into an SQL-acceptable date format. However, to_datetime does not work on datetime.date, leaving the pandas datatype as "object" instead of datetime64ns.

Thanks,