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?

To 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
– Cole, Commented Jul 24, 2010 at 22:43

mechanical_meat · Accepted Answer · 2010-07-24 22:49:15Z

5

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'

edited Jul 24, 2010 at 22:49

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 Over a year ago

cursor.execute is definitely better. Doesn't it take "?" for parameters though? or is the "%s" also acceptable?

mechanical_meat Over a year ago

@ars: added background info regarding paramstyle.

John Machin · Accepted Answer · 2010-07-24 22:43:12Z

1

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

SiggyF · Accepted Answer · 2010-07-25 08:47:30Z

1

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

edited Jul 25, 2010 at 8:47

answered Jul 24, 2010 at 22:41

SiggyF

23.3k8 gold badges46 silver badges57 bronze badges

2 Comments

SiggyF Over a year ago

I think ? should be %s. Depends on the paramstyle.

John Machin Over a year ago

cursors.execute(sql, var) (1) multiple cursors ?-) (2) 2nd arg should be a tuple (var, )

ars · Accepted Answer · 2010-07-24 22:40:35Z

0

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 Over a year ago

+1; beat me. Nitpicks: 1) vulnerable to SQL-injection, and 2) the %d should be %s.

John Machin Over a year ago

-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 Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Python: Error in MySQL

4 Answers 4

2 Comments

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related