How to pass variable into MySQLdb query with Python?

Question

I'm trying to loop over an MySQL query, however I can't get the variable to work. What am I doing wrong? The loop starts at line 10.

cur = db.cursor()
query = '''
Select user_id, solution_id 
From user_concepts
Where user_id IN 
  (Select user_id FROM fields);
'''    
cur.execute(query)
numrows = cur.rowcount
for i in xrange(0,numrows):
    row = cur.fetchone()
# find all item_oid where task_id = solution_id for first gallery and      sort by influence.
    cur.execute('''
        SELECT task_id, item_oid, influence
        FROM solution_oids 
        WHERE task_id = row[%d]
        ORDER BY influence DESC;
        ''', (i))
    cur.fetchall()

error message:

File "james_test.py", line 114, in ''', (i)) File "/usr/lib64/python2.7/site-packages/MySQLdb/cursors.py", line 187, in execute query = query % tuple([db.literal(item) for item in args]) TypeError: 'int' object is not iterable

Assem · Accepted Answer · 2015-12-11 07:26:11Z

2

cur.execute expect a tuple o dict for params but you gave (i) which is an int not a tuple. To make it a tuple add a comma (i,)

answered Dec 11, 2015 at 7:26

Assem

12.2k5 gold badges63 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

panofish · Accepted Answer · 2015-12-12 04:56:46Z

Here's how I would do this. You may not need to declare 2 cursors, but it won't hurt anything. Sometimes a second cursor is necessary because there could be a conflict. Notice how I demonstrate 2 different methods for looping the cursor data. One with the fetchall and one by looping the cursor. A third method could use fetch, but is not shown. Using a dictionary cursor is really nice, but sometimes you may want to use a standard non-dict cursor where values are retrieved only by their number in the row array. Also note the need to use a trailing comma in the parameter list when you have only 1 parameter. Because it expects a tuple. If you have more than 1 parameter, you won't need a trailing comma because more than 1 parm will be a tuple.

cursor1 = db.cursor(MySQLdb.cursors.DictCursor)  # a dictcursor enables a named hash
cursor2 = db.cursor(MySQLdb.cursors.DictCursor)  # a dictcursor enables a named hash

cursor1.execute("""
    Select user_id, solution_id 
      From user_concepts
     Where user_id IN (Select user_id FROM fields);
"""    

for row in cursor1.fetchall():
    user_id = row["user_id"]
    solution_id = row["solution_id"]

    cursor2.execute("""
        SELECT task_id, item_oid, influence
        FROM solution_oids 
        WHERE task_id = %s
        ORDER BY influence DESC;
    """, (solution_id,))

    for data in cursor2:
        task_id = data["task_id"]
        item_oid = data["item_oid"]
        influence = data["influence"]

That helped me with much more than my question. Thanks so much.

rocksteady · Accepted Answer · 2015-12-11 07:44:34Z

0

Maybe try this:

a = '''this is the {try_}. try'''
i= 1    
b = a.format(try_=i)
print b

You could even do:

data = {'try_':i}
b = a.format(**data)

sources:

python's ".format" function

Python string formatting: % vs. .format

edited Dec 11, 2015 at 7:44

answered Dec 11, 2015 at 7:30

rocksteady

2,5905 gold badges27 silver badges42 bronze badges

Collectives™ on Stack Overflow

How to pass variable into MySQLdb query with Python?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related