I have a data frame which looks as follows:
index staffmember title basedin yearsexperience skillsdomain product competency abbreviation
0 Arnold Schwartzenegger Terminator2 USA 23.0 Platform Spectrum Required R
I am trying to use the following python code to update the record if it exists in the table and append if not:
new_df.to_sql('STAFF_MEMBERS_PRODUCTS_SKILLS', con=engine, schema="my-python", if_exists = 'append', index=FALSE)
I get the following error (one of several, but this summarizes the gist of them all):
sqlalchemy.exc.ProgrammingError: (pymssql._pymssql.ProgrammingError) (207, b"Invalid column name 'index'.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages fromeneral SQL Server error: Check messages from the SQL Server\nDB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from
[SQL: INSERT INTO [STAFF_MEMBERS_PRODUCTS_SKILLS] ([index], staffmember, title, basedin, yearsexperience, skillsdomain, product, competency, abbrevi %(title)s, %(basedin)s, %(yearsexperience)s, %(skillsdomain)s, %(product)s, %(competency)s, %(abbreviation)s)] [parameters: {'index': 0, 'staffmember': 'Arnold Schwartzenegger', 'title': 'Terminator2', 'basedin': 'USA', 'yearsexperience': '23.0', 'skillsdomai (Background on this error at: https://sqlalche.me/e/14/f405)*
The df.to_sql function seems to try and insert the df index number into the sql table as a (non-existing) column. Can anyone help me to fix this so the index is excluded from this update ? Thanks