Appending to a sql table using python panda module

Question

I am attempting to append to a SQL table using to_sql command in python. Everything works fine but if I run my command more tahn once it will obviously append the same data. I know this could be solved with if_exists='replace' but when i do that i get the following error.

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'AccountName' in table 'table' is of a type that is invalid for use as a key column in an index. (1919) (SQLExecDirectW)") [SQL: u'CREATE INDEX [ix_table_AccountName] ON table ([AccountName])']

I need to figure out how to either,only append data that is not already in the table, delete the data I am about to append if it is already in the table or how to fix my index problem so i can use if_exists='replace'

engine = create_engine('mssql+pyodbc://UN:PW@DB')
df.to_sql("table", engine,if_exists='replace')

Could you post some of the data you're working with and give an example of what counts as "already in the table"? I'm not sure if that includes single values or entire rows. — snapcrack, Jun 17 '17 at 04:57

score 0 · Answer 1 · answered Nov 24 '21 at 17:55

Your column 'AccountName' is probably too long. For MSSQL the index can only be 900 bytes, so if you use utf-8 this is 450 chars.

Try setting the dtype for your column in the to_sql() statement with a shorter length if that column is used as index.

from sqlalchemy.types import String
[...]
df.to_sql(
[...]
    dtype={
       [...]
       'AccountName': String(255),

Appending to a sql table using python panda module

1 Answers1