BUG: add support for writing datetime.date and datetime.time columns using to_sql

[JohnSmizz]

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 "<ipython-input-275-80a6d739629c>", line 1, in <module>
    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,

[jreback]

datetime.date and datetime.time are always object formats; these are not force converted, unless you explicty pass a dtype (e.g. datetime64[ns]). This is sort of a compatibility issue as people want to use them, so they are represented as object dtype.

[jorisvandenbossche]

@jreback Is there any way to detect the 'real' type of a column when it has dtype object? (I suppose just looking at the type of eg the first value, but that is not really robust)
Because, in principle, sqlalchemy has datetime.date/time types that map to sql types, and mysql has a DATE and TIME type, so they could actually be written to the database correctly.

BTW, this line now in the codebase: https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L655 I suppose is useless, as the dtype of a dataframe column can never be datetime.date?

[jreback]

yes that line would never be true and is useless

lib.infer_dtype(com._ensure_object(arr))

would return 'date' or 'time' if all elements are date or time
else would return 'mixed'

[jorisvandenbossche]

I updated this issue to also remember that datetime.date should be added (there is a line of code for that, but it is not working, and it was never tested).

So adding datetime.time and datetime.date:

• so if dtype=object, check if it is date or time (BUG: add support for writing datetime.date and datetime.time columns using to_sql #6932 (comment))
• return the appropriate SQLAlchemy type

[jorisvandenbossche]

@JohnSmizz I created a PR to fix this issue, see #8090.
I have tested it for postgresql and mysql, but would you be able to test this with mssql?

[JohnSmizz]

Sure can do but will not be able to until mid september

On 22 Aug 2014, at 11:43, Joris Van den Bossche [email protected] wrote:

@JohnSmizz I created a PR to fix this issue, see #8090.
I have tested it for postgresql and mysql, but would you be able to test this with mssql?

—
Reply to this email directly or view it on GitHub.

[jorisvandenbossche]

OK, that would be very welcome