Pandas to_sql Trying to Index Nullable Column

Question

I want to set up a job that dumps data into a SQL table every day, overwriting the existing data.

df.to_sql(table_name, engine, schema='dbo', 
          index=True, index_label='IdColumn', 
          if_exists='replace')

However behind the scenes SQLAlchemy is trying to create the table with IdColumn VARCHAR(max), and being nullable. So SQL throws an error when it tries to create the index.

It's pretty trivial to truncate the table before I write the data to it, but I feel like there should be a more elegant solution to this problem.

joris · Accepted Answer · 2015-12-30 09:39:09Z

2

If you want the write the index to the sql table as a normal column, you can do a reset_index before the to_sql call:

df.reset_index().to_sql(table_name, engine, schema='dbo', index=False, if_exists='replace')

The only problem is the name of that column, if you want a custom one you first have to set the index name (df.index.name = 'IdColumn') or rename after the reset_index.

answered Dec 30, 2015 at 9:39

joris

140k37 gold badges257 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parfait · Accepted Answer · 2015-12-30 04:12:14Z

0

Consider using the dtype argument which takes a dictionary mapping data frame column names to specified sqlalchemy data types. You can try Varchar:

import sqlalchemy

df.to_sql(table_name, engine, schema='dbo', 
          index=True, index_label='IdColumn', 
          if_exists='replace',
          dtype={'IdColumn': sqlalchemy.types.VARCHAR(length=255)})

or generic String type, specifying a length:

from sqlalchemy.types import String

df.to_sql(table_name, engine, schema='dbo', 
          index=True, index_label='IdColumn', 
          if_exists='replace',
          dtype={'IdColumn': String(length=255)})

answered Dec 30, 2015 at 4:12

Parfait

108k19 gold badges102 silver badges138 bronze badges

1 Comment

Gord Thompson Over a year ago

I just tried this type of approach for a similar question here but I found that dtype does not seem to affect the index column (even if we give it an explicit index_label), only the "real" data columns.

Collectives™ on Stack Overflow

Pandas to_sql Trying to Index Nullable Column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related