Issue with inserting parameter values into MySQLdb.execute()

Question

My code executes a query and then for each row in the result set tries to execute another query using values from that row.

import MySQLdb as mdb
try:
    con = mdb.connect('localhost', 'root', '', 'cccorder_uk');

    with con:
        cur = con.cursor()
        cur.execute("SELECT code, name, box_size, commodity_code, country_of_origin FROM cccorder_uk.stocks")
        rows = cur.fetchall()
        for row in rows:
            # split the code and take colour and size

            code = row[0].split('-')
            product_code = code[0]

            sql = """SELECT stock_groups.name FROM stock_groups_styles_map, stock_groups WHERE stock_groups_styles_map.style='%s'""" % (product_code,)

            cur.execute(sql)
            results = cur.fetchall()
            print results

except mdb.Error, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

finally:    

    if con:    
        con.close()

When I print results I get an empty tuple, but if I hard code the product_code, for example sql = """SELECT stock_groups.name FROM stock_groups_styles_map, stock_groups WHERE stock_groups_styles_map.style='EP22'""", this returns the results I expect.

Why is my code printing an empty tuple?

Put print sql after before the inner cur.execute or print cur._executed after it and post it, please — tktk
– tktk, Commented Jun 21, 2014 at 21:16
` SELECT stock_groups.name FROM stock_groups_styles_map, stock_groups WHERE stock_groups_styles_map.style = 'EP01'` — khinester
– khinester, Commented Jun 21, 2014 at 21:21
When you hard code the product_code, where do you execute the query? In Python, using MySQLdb? Or in another environment, like Workbench? — Air
– Air, Commented Jun 23, 2014 at 17:50

Community · Accepted Answer · 2017-05-23 11:56:58Z

1

Python's string-format operator % isn't smart enough to quote args for MySQL -- pass args to the database execute function, which will pass the args to MySQL correctly.

Example:

cur.execute("SELECT stock_groups.name FROM stock_groups_styles_map, stock_groups WHERE stock_groups_styles_map.style=%s", product_code)

See: How can I format strings to query with mysqldb in Python?

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Jun 21, 2014 at 22:35

johntellsall

15.2k4 gold badges48 silver badges40 bronze badges

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

2 Comments

johntellsall Over a year ago

@Korem: yes, if the variable is a string, and if it doesn't have quotes. The database cursor is smart enough to quote anything, because it has additional information that Python doesn't have.

tktk Over a year ago

What I'm saying is that since in that particular case he already took care of quoting, your answer probably does not answer the question. Look at the query he posted in the comments - it's ok. Of course it's better to use execute, but that's not his problem.

Collectives™ on Stack Overflow

Issue with inserting parameter values into MySQLdb.execute()

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related