problem with to_sql with NA · Issue #8778 · pandas-dev/pandas (original) (raw)

i am experiencing issues with writing NA values for a column of boolean with missing values.

data = [True, None, False] df = pd.DataFrame(a, columns=['test'])

df.info()

<class 'pandas.core.frame.DataFrame'> Int64Index: 3 entries, 0 to 2 Data columns (total 1 columns): test 2 non-null object dtypes: object(1) memory usage: 48.0 bytes

I am reading data from a mssql server and am trying to write it unchanged in another table.
As the data contains NULL values, pandas changes the dtype to object. when calling to_sql, object fields seem to be mapped to a "text" column type, and the export then fails:

df.to_sql('test_table', engine_local, if_exists='append', index=True)

DataError: (DataError) ('22018', '[22018] [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: bit is incompatible with text (206) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') u'INSERT INTO test_table ([index], test) VALUES (?, ?)' ((0L, True), (1L, None), (2L, False))

'object' dtypes are tricky to handle systematically I guess, but maybe we could add the ability to tell pandas how to handle it on a column by column basis?.

something like:

df.to_sql('test_table', engine_local, if_exists='append', index=True, col_types={'test': bool})

could do the trick?