python pandas: export structure only (no rows) of a dataframe to SQL

Question

I am using pandas 0.16 and sqlalchemy. Is it possible to export just the structure, i.e. column names and data types but no rows, of a dataframe to SQL?

The closest I managed to get to was to export the first row only:

df.ix[[0],:].to_sql( tablename, myconnection )

And then I'd have to do a truncate table. However, there are inconsistencies between the to_csv and the to_sql methods: to_csv writes boolean fields as the strings 'TRUE' or 'FALSE' , whereas to_sql writes them as 0 or 1. This means that importing files creates with dataframe.to_csv is more complicated than it should be.

If I run

df.ix[[],:].to_sql( tablename, myconnection )

that doesn't work because all columns are exported as text.

Answer 1

You can use the get_schema function:

from pandas.io.sql import get_schema

engine = ...
df = ..
get_schema(df, 'table_name', con=engine)

This will give you the schema that would otherwise be created in string form, which you could execute with engine.execute

Further, the reason to_sql writes your boolean data as 0 and 1's, is because SQL Server has no boolean data type (see eg Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?)

Answer 2

.to_sql() supports a dict= argument that lets you specify the column types as SQLAlchemy types.

df.ix[[], :].to_sql(tablename, myconnection, dtype={
    'column1': sqlalchemy.types.Float,
    'column2': sqlalchemy.types.BigInt,
    'column3': sqlalchemy.types.Date,
})

... will let you map the columns to their respective SQLAlchemy types.