CODEWITHSUNDEEP
pythonmysqlsqlpython-2.7
twigg
twigg

Reputation: 3993

python mysql error <class '_mysql_exceptions.ProgrammingError'>

This isn't the most helpful error I've ever seen but from a Google it seems to be a typing error which causes the error but I just cant figure it out. Am I missing the obvious?

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO spin_hdd(error_count, raw_read_error, spin_up_time, allo_sec, seek_error, spin_retry, gsense_error, power_on_hours, pending_sector, load_retry, spin_up_time, max_temp, sector_count, short_test_time, long_test_time, model, serial, firmware, ip, running) \
       VALUES ('%s','%s','%s','%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % \
       (error_count, raw_read_error, spin_up_time, allo_sec, seek_error, spin_retry, gsense, power_on, pend_sec, load_retry, spin_up_time, max_temp, sector_count, testShortTime, testLongTime, model_count, serial, firmware, ip, 1)
try:
   # Execute the SQL command
   cur.execute(sql)
   # Commit your changes in the database
   con.commit()
   # get last inserted id
   id = cur.lastrowid
except:
   # Rollback in case there is any error
   con.rollback()
   e = sys.exc_info()[0]
   print e

Upvotes: 0

Views: 1494

Answers (1)

unutbu
unutbu

Reputation: 879571

Well, it could be due to many things such as an error in the SQL or the table not being found. It's impossible for us to tell if there is an error in the SQL since the string formatting could be inserting some unexpected stuff (like quotation marks or semicolons or other improperly quoted values) into the SQL.

One thing which might fix the problem is using the 2-argument form of cur.execute, which will properly quote the arguments for you. This will correct the quoting error Prerak Sola points out (1 should not be quoted as '1' if the last column is of a numeric type.)

sql = """INSERT INTO spin_hdd(
            error_count, raw_read_error, spin_up_time, allo_sec, seek_error, 
            spin_retry, gsense_error, power_on_hours, pending_sector, load_retry, 
            spin_up_time, max_temp, sector_count, short_test_time,
            long_test_time, model, serial, firmware, ip, running) 
         VALUES ({})""".format(', '.join(['%s']*20))

args = (error_count, raw_read_error, spin_up_time, allo_sec, seek_error,
        spin_retry, gsense, power_on, pend_sec, load_retry, spin_up_time,
        max_temp, sector_count, testShortTime, testLongTime, model_count,
        serial, firmware, ip, 1)
try:
   # Execute the SQL command
   cur.execute(sql, args)
   # Commit your changes in the database
   con.commit()
   # get last inserted id
   id = cur.lastrowid
except:
   # Rollback in case there is any error
   con.rollback()
   e = sys.exc_info()[0]
   print e

By the way, you should always use the 2-argument form of cur.execute instead of manually quoting the arguments yourself as this helps prevent SQL injection.

Upvotes: 3

Related Questions