0

I am trying to use Pandas to_sql method to input a dataframe into a SQL Server database, and I am getting the error:

'23000', "[23000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint ... Cannot insert duplicate key in object

Someone else posted a similar problem here: Pandas to_sql 'append' to an existing table causes Python crash

However, his solution was basically that he needed to drop duplicates for the primary key because he was submitting the primary key more than once. I am not even trying to submit the primary key, I want SQL Server to assign that. Here is the method I call:

df.to_sql('table_name', engine, if_exists='append', index=False, chunksize=1000)

I know the engine works to connect to the database because if I use the read_sql method it works fine returning the data from what's in the database. I'm just not able to write to the database, it apparently sees me re-using primary keys when I'm not even trying to touch the primary keys. Can anyone help?

Emac
  • 1,098
  • 3
  • 18
  • 37
  • what's the primary key for that table, also would help to show some sample data – gold_cy Mar 19 '19 at 13:59
  • The primary key is just an auto incremented number. I just want it to start at the next highest number and go up from there. I'm not intentionally trying to touch it at all. I'll work on getting some sample data in my question, but it's basically just a bunch of addresses (with NULL in for phone numbers and some other fields). – Emac Mar 19 '19 at 14:12
  • 2
    Learn to read the error message and **not** jump to conclusions. The error message said "unique constraint" not "primary key" and it gave you the constraint name and the violating tuple. – SMor Mar 19 '19 at 14:45

1 Answers1

1

I think I figured it out. The database was set up as not to allow the same name and address 1 field, and my data had some duplicates for address 1 but differences in address 2. Need to make some database changes and then try again. I'm going to leave my question up to help others who may encounter similar issues.

Emac
  • 1,098
  • 3
  • 18
  • 37