error in sql syntax when inserting records from dataframe

Question

i'm trying to insert my some data into mysql table from a csv file

this is an example of my csv file:

year type, count
2006,Nursing Homes,62
2006,Inpatient Hospices,2
2007,Nursing Homes,62
2007,Inpatient Hospices,2
2008,Nursing Homes,63
2008,Inpatient Hospices,2
2009,Nursing Homes,60
2009,Inpatient Hospices,2
2010,Nursing Homes,62
2010,Inpatient Hospices,2

table created using

import mysql.connector
from mysql.connector import errorcode
import sys

user,pw, host,db = 'root', 'Svt13btob7!!','127.0.0.1','ymdatabase'

cnx = mysql.connector.connect(user=user, password=pw, host=host, database=db)
cursor = cnx.cursor()

query_for_creating_table = ("CREATE TABLE `long_term_care_facilities` ("
   "`Year` int(4) NOT NULL,"
   "`type` varchar(50),"
   "`count` int(3),"
   "PRIMARY KEY (`Year`)"
   ") ENGINE=InnoDB")

try:
   cursor.execute(query_for_creating_table)
   cnx.commit()
   print("Table created!")

except:
   print("Unexpected error:", sys.exc_info()[0])
   exit()

finally:
  cursor.close()
  cnx.close()

inserting values using

import mysql.connector,sys, pandas as pd

df_resi = pd.read_csv("number-of-residential-long-term-care-facilities.csv")
user,pw, host,db = 'root', 'Svt13btob7!!','127.0.0.1','ymdatabase'

cnx = mysql.connector.connect(user=user, password=pw, host=host, database=db)
cursor = cnx.cursor()

for index, col in df_resi.iterrows():
    data = {
        'year':col[0],
        'type':col[1],
        'count':col[2]
        }
    
    query = insert_stmt = ("INSERT INTO long_term_care_facilities ('year', 'type', 'count')"
                            "VALUES (%(year)s, %(type)s,  %(count)s,)")
    
    cursor.execute(query, data) # Insert new cea_salesperson
    print("Adding row " + str(index))
    cnx.commit() # Make sure data is committed to the database

print("All data inserted!")
cursor.close()
cnx.close()

error received


raise get_mysql_exception(
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 ''year', 'type', 'count')VALUES (2006, 'Nursing Homes',  62,)' at line 1

these codes came from an example for my assignment so i can't seem to figure out what's wrong despite comparing with similar problems that others faced

error in sql syntax when inserting records from dataframe

0 Answers0