SQL query is causing problem while inserting into table

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') 

Answer

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.