How to make df.to_sql() create varchar2 object

Question

I have a DataFrame which consists of a column of strings. If I do df.to_sql() to save it as a table into an Oracle database, the column is of CLOB type and I need to convert it. I wonder if I can specify the type (say varchar2) when I create the table?

Answer 1

You can specify SQLAlchemy Type explicitly:

import cx_Oracle
from sqlalchemy import types, create_engine
engine = create_engine('oracle://user:password@host_or_scan_address:1521/ORACLE_SERVICE_NAME')

df.to_sql('table_name', engine, if_exists='replace',
          dtype={'str_column': types.VARCHAR(df.str_column.str.len().max())})

df.str_column.str.len().max() - will calculate the maximum string length

NOTE: types.VARCHAR will be mapped to VARCHAR2 for Oracle (see working example here)

Answer 2

You have to options, the first is to create the table manually and then use the if_exists parameter to tell pandas to append to the table rather than to drop and recreate

Option two is to use the dtype pass a dictionary of column names so that the table can be created appropriately. These are SQL Alchemy types so you should

from sqlalchemy.dialects.oracle import VARCHAR2

and pass that in the dictionary as

{'mycolumn': VARCHAR2(256) }

or suitable length.

Ref: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html