python pandas : how to control the constraints and indices automatically created by to_sql?
Question: I am using pandas 0.16 and sqlalchemy to export data to a Microsft SQL Server
2014 database. The dataframe to_sql method automatically creates certain
constraints on the table, e.g. it creates a constraint that a boolean column
must be either 0 or 1.

I suspect these constraints are slowing down the export process. Is there a
way to disable them, at least temporarily (i.e. re-enabling them only after
all the data is in SQL)?

Also, is this documented anywhere? I couldn't find any mention of this,
neither in the pandas docs nor in the sqlalchemy.


Answer: 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 Boolean data in sql server
like mysql?](http://stackoverflow.com/questions/3138029/is-there-boolean-data-
in-sql-server-like-mysql)).  
In such cases, SQLAlchemy by default adds those constraints to the column, as
is documented in the SQLAlchemy docs:
<http://docs.sqlalchemy.org/en/latest/core/type_basics.html#sqlalchemy.types.Boolean>

Using the possibility of overriding the default type with the `dtype` argument
in `to_sql` (documented [here](http://pandas.pydata.org/pandas-
docs/stable/io.html#sql-data-types)), you can specify to not create this
constraint:

    
    
    from sqlalchemy.types import Boolean
    df.to_sql('name', engine, dtype={'my_bool_col': Boolean(create_constraint=False)})
    

