Why do I get this 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 that "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?

Answer 1

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.

Answer 2

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'

Answer 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 ?.

Answer 4

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