CODEWITHSUNDEEP
pythonmysqlpandassqlalchemymysql-python
Alex Dana
Alex Dana

Reputation: 1307

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

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.

Upvotes: 0

Views: 435

Answers (1)

Caleb Courtney
Caleb Courtney

Reputation: 346

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.

Upvotes: 2

Related Questions