How to choose my primary key column in saving pandas dataframe to sql

user_12283181 0
Viewed 985

I am trying to save a dataframe to mysql with the following:

df.to_sql('dedupe__df', con=to_conn, if_exists='replace')

This adds the index as the primary key. However, if I add the actual primary key, which is called path, I get the following error:

>>> df.to_sql('dedupe__df', con=to_conn, if_exists='replace', index_label='path')

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

What does this mean exactly? It may be related to pandas trying to save all string fields as TEXT instead of varchar, as when I try and index on that field (as pandas as saved it, I get):

> [MySQL] BLOB/TEXT column 'path' used in key specification without a key length

It seems like pandas doesn't too well with string fields on defaults...What would be the suggested way to fix this, outside of supplying a full schema (which would be overkill).

pandas sql python
1 Answers

Unfortunately, it is not possible with df.to_sql(). But you can do it in below given way:

df.to_sql('dedupe__df', engine, if_exists="replace", index=False)
with engine.connect() as con:
    con.execute('ALTER TABLE dedupe__df ADD PRIMARY KEY (path);')

Table name & index column is referenced from your code.

user_83176720
Related