Index in pandas.to_sql, ValueError: duplicate name in index/columns: cannot insert id, already exists

Question

I am reading and writing MySQL table with pandas and I am pretty sure that the value I am trying to set as index during writing is unique. I checked the table without an index and count(distinct(id)) gives the same amount of rows as count(id). However, I still get an error

'ValueError: duplicate name in index/columns: cannot insert product_id, already exists'

if i set index=True, index_label="id"

I have tried reset_index, but it did not help.

df.to_sql(name=config.DB_TABLE, con=connection, schema=config.DB_SCHEMA, if_exists='fail', index=True, index_label="id")

What am I doing wrong?

Answer 1

I had the same problem. I fixed it by setting the index of the DataFrame before calling the to_sql() method:

df = df.set_index('your_index')

I believe the method wouldn't accept the index I specified as it was already trying to use the default pandas index (i.e. 0, 1, 2...). Setting the DataFrame's index to the one you want in your database will avoid this conflict