Tutorial :Python: Error in MySQL



Question:

import MySQLdb      import random    db = MySQLdb.connect (host = "localhost", user = "python-test", passwd = "python", db = "python-test")    cursor = db.cursor()    var = .3    sql = "INSERT INTO RandomInt            (RAND)          VALUES            (var)" # RandomInt is the name of the table and Rand is the Column Name    cursor.execute(sql)    db.commit()  db.close()  

I get an error saying Operational Error: (1054, "Unknown column 'var' in 'field list'") Why do I get this error and how do I fix this although I have already defined var?


Solution:1

As written, var is being sent to MySQL as a string.
Give this a shot instead:

sql = "INSERT INTO RandomInt (RAND) VALUES (%s)"  cursor.execute(sql, (var,))  

Edit:

>>> import MySQLdb  >>> MySQLdb.paramstyle  'format'  

MySQLdb's paramstyle is format; which, according to the DB-API is %s:

            'format'        ANSI C printf format codes,                               e.g. '...WHERE name=%s'  


Solution:2

var = .3  sql = "INSERT INTO RandomInt (RAND) VALUES (%s)"   cursor.execute(sql, (var,))  


Solution:3

This will fix one issue:

sql = "INSERT INTO RandomInt (RAND) VALUES (%s)"   cursors.execute(sql, (var,))  

What remains is the name of the table where you write into, 0.3 is not an int.

Edit: paramstyle of mysqldb is %s not ?.


Solution:4

Your sql appears to MySQL as:

INSERT INTO RandomInt (RAND) VALUES (var)  

To actually have the string substitute var, try this:

sql = "INSERT INTO RandomInt (RAND) VALUES (%d)"  % (var,)  

Now, MySQL should see:

INSERT INTO RandomInt (RAND) VALUES (0.3)  

NOTE: Adam Bernier is right about sql injection. See the cursor.execute doc for parameter substitution as well as his answer.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »