0

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

Thom A
  • 88,727
  • 11
  • 45
  • 75
Jeff
  • 1
  • 2
  • Try `df=df.drop(columns=['index'])` – Charlieface May 09 '22 at 11:59
  • Use `index=False`, not `FALSE`. I'm surprised you don't get an error complaining about `FALSE`. Was this variable defined somewhere else? If it was (why?) does it contain a value that *isn't* [falsy](https://stackoverflow.com/questions/39983695/what-is-truthy-and-falsy-how-is-it-different-from-true-and-false)? `None` or an empty array are treated as `False`. A non-empty string is treated as True – Panagiotis Kanavos May 09 '22 at 12:04
  • Thanks for the feedback - unfortunately, using df=df.drop(columns=['index']) results in the following error: File "C:\Users\arnold.jeffrey\AppData\Roaming\Python\Python310\site-packages\pandas\core\indexes\base.py", line 6644, in drop raise KeyError(f"{list(labels[mask])} not found in axis") KeyError: "['index'] not found in axis" – Jeff May 09 '22 at 18:37

0 Answers0