SQL query is causing problem while inserting into table

Question

I tried to insert dataframe using pymysql library. It's giving this error:

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''bestwebsites' ('Rank','Score','Category','Audience','URL','Links','Desc') VALUE' at line 1")

import pandas as pd 
import re
# import sqlalchemy
import pymysql


df = pd.read_csv('final_data.csv')
df = df.drop(['blank','Unnamed: 0'], axis=1)
df['Desc']
df['Desc'] = df['Desc'].str.replace("[-',’]",'').astype(str)
df.columns

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='root',
                             db='pythondb')

cursor = connection.cursor()

cols = "','".join([str(i) for i in df.columns.tolist()])

for i,row in df.iterrows():
    sql = "INSERT INTO 'bestwebsites' ('" +cols+ "') VALUES (" + "%s,"*(len(row)-1) + "%s)"
    cursor.execute(sql,list(row))

    connection.commit()
connection.close()

I tried using sqlalchemy It's working

engine = sqlalchemy.create_engine('mysql+pymysql://root:root@localhost:3306/pythondb')

df.to_sql(name='bestwebsites',
        con=engine,
        index=False,
        if_exists='replace')

GMB · Accepted Answer · 2020-05-15 14:24:11Z

1

Column names (and table names) should not be surrounded with single quotes. You can use unqote them if they conform to MySQL rules for unquoted identifiers, or use backticks for quoting:

cols = "`,`".join([str(i) for i in df.columns.tolist()])

for i,row in df.iterrows():
    sql = "INSERT INTO `bestwebsites` (`" +cols+ "`) VALUES (" + "%s,"*(len(row)-1) + "%s)"
    cursor.execute(sql,list(row))   

connection.commit()
connection.close()

Side note: it is more efficient to perform all inserts first, and then commit - rather than committing after each and every insert.

answered May 15, 2020 at 14:24

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

Hemant Sah Over a year ago

Thankyou, You solved the problem and thankyou for some extra information.

Collectives™ on Stack Overflow

SQL query is causing problem while inserting into table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related