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?
user207421
312k45 gold badges324 silver badges493 bronze badges
-
1To python that SQL is just a string. This has been pointed out in the answers of course, but it's not going to replace the 'var' in your string unless you do tell it to.Cole– Cole2010年07月24日 22:43:10 +00:00Commented Jul 24, 2010 at 22:43
4 Answers 4
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'
answered Jul 24, 2010 at 22:40
mechanical_meat
170k25 gold badges238 silver badges231 bronze badges
Sign up to request clarification or add additional context in comments.
2 Comments
ars
cursor.execute is definitely better. Doesn't it take "?" for parameters though? or is the "%s" also acceptable?
mechanical_meat
@ars: added background info regarding
paramstyle.var = .3
sql = "INSERT INTO RandomInt (RAND) VALUES (%s)"
cursor.execute(sql, (var,))
answered Jul 24, 2010 at 22:43
John Machin
83.3k12 gold badges147 silver badges193 bronze badges
Comments
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 ?.
answered Jul 24, 2010 at 22:41
SiggyF
23.3k8 gold badges46 silver badges57 bronze badges
2 Comments
SiggyF
I think ? should be %s. Depends on the
paramstyle.John Machin
cursors.execute(sql, var) (1) multiple cursors ?-) (2) 2nd arg should be a tuple
(var, )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.
answered Jul 24, 2010 at 22:40
ars
124k23 gold badges151 silver badges135 bronze badges
4 Comments
mechanical_meat
+1; beat me. Nitpicks: 1) vulnerable to SQL-injection, and 2) the
%d should be %s.John Machin
-1 vulnerable to SQL-injection, SQL has to be retranslated each time if being done in a loop ... there's rarely a good reason to do it this way and pushing it at a beginner without any remarks at all is disgraceful.
ars
John: Huh? That's why I added the remark at the end and pointed to Adam's answer. The timestamp shows it was ~3 minutes before your comment.
John Machin
I type slowly / I got a cup of copy / whatever. So what? The fact that you changed your answer is irrelevant; you shouldn't have contemplated such an answer in the first place.
default