Unable to iteratively insert values in Python-MySQL tables

Question

I am facing error while trying to iteratively insert values in MySQL table. I am not sure if the insert statement is correct? or do we have any better way of inserting values in my table.

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax;

 import mysql.connector
 from collections import defaultdict

 cnx = mysql.connector.connect(user='root', password='hadoop',
                          host='localhost',
                          database='test')


 lines = defaultdict(dict)
 with open("test.txt") as f:
  for line in f:
    parts = line.split()
    key = tuple(parts[1:3]) # unique key with servername and datetime
    lines[key][parts[0]] = parts[3]
    lines[key]["servername"] = parts[2]
    lines[key]["datetime"] = parts[1]

 res = list(lines.values())

try:
  cursor = cnx.cursor()
  for index in range(len(res)):

       cursor.execute("""
           insert into cpu_util_all  (servername,date_time,cpu_number,cpu_user,cpu_nice,cpu_system,cpu_wait,cpu_idle)                values ('%s', '%s', '%s', '%s','%s', '%s', '%s', '%s') %    (res[index]["servername"],res[index]["datetime"],res[index]["cpunumber"],res[index]["cpuuser"],res[index]["cpunice"],res[index]["cpusystem"],res[index]["cpuwait"],res[index]["cpudile"])
 """)

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

print("Inserted successfully")

mechanical_meat · Accepted Answer · 2015-07-29 18:24:46Z

1

The problem is that you're trying to do string substitution in the query itself. Further, you should allow MySQLdb to parse the parameter values for you, e.g.:

cursor.execute("""
        insert into cpu_util_all 
            (servername,date_time,cpu_number,cpu_user,
             cpu_nice,cpu_system,cpu_wait,cpu_idle) 
        values (%s, %s, %s, %s, %s, %s, %s, %s)""", 
        (res[index]["servername"],res[index]["datetime"],
         res[index]["cpunumber"],res[index]["cpuuser"],
         res[index]["cpunice"],res[index]["cpusystem"],
         res[index]["cpuwait"],res[index]["cpudile"]
        )
    )

edited Jul 29, 2015 at 18:24

answered Jul 29, 2015 at 17:43

mechanical_meat

170k25 gold badges238 silver badges231 bronze badges

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

1 Comment

Goutam Over a year ago

I just corrected it, anyways thanks for your solution. Its a perfect one.

Collectives™ on Stack Overflow

Unable to iteratively insert values in Python-MySQL tables

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