Create MySQL Table from pandas dataframe - error 1054 (42S22)

Question

I have a dataframe df created as follow :

df = pd.DataFrame(list(zip(product_urlList, nameList, priceList, picList)), 
               columns =['URL','NomProduit', 'Prix', "LienPic"])

df['IdUnique'] = df['NomProduit'] + df['Prix']

My target is to import it into a MySQL database.

I created an SQL Database (called "Sezane") and its table called "Robes" via Python with MySQL.connector.

import mysql.connector as mysql

db = mysql.connect(
    host = "localhost",
    user = "root",
    passwd = "password",
    database = "sezane"
)

cursor = db.cursor()
cursor.execute('CREATE TABLE Robes (id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, Nom_Robes VARCHAR(255), Prix_Robes VARCHAR(255), liens_Robes VARCHAR(300), Images_robes VARCHAR (300), Id_Robes VARCHAR (255))'

Then, I try to insert this dataframe in the table :

from sqlalchemy import create_engine

engine = create_engine('mysql+mysqlconnector://root:password@Localhost:3306/sezane', echo=True)
df.to_sql(name='Robes', con=engine, if_exists = 'append')

I have the following error :

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1054 (42S22): Unknown column 'index' in 'field list'

I made some researches about this error and found that it could become a problem of quote bracket "/' interversion.

However, after many hours on it, I still don't understand where it comes from. Why is the error message about "Index" ?

My target is to be able to make my df as a table.

Could you give an example of the actual df you're trying to stuff into a database? — Hampus Larsson
– Hampus Larsson, Commented Apr 14, 2020 at 20:04

Caleb Courtney · Accepted Answer · 2020-04-14 20:13:38Z

2

By default to_sql tries to export the dataframe index as a column. You should be able to change this: df.to_sql(name='Robes', con=engine, if_exists = 'append')

To this: df.to_sql(name='Robes', con=engine, if_exists = 'append', index = False) and you will no longer get the same error.

answered Apr 14, 2020 at 20:13

Caleb Courtney

3462 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create MySQL Table from pandas dataframe - error 1054 (42S22)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related