CODEWITHSUNDEEP
pythonmysqlmysql-python
khinester
khinester

Reputation: 3520

Issue with inserting parameter values into MySQLdb.execute()

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?

Upvotes: 0

Views: 73

Answers (1)

johntellsall
johntellsall

Reputation: 15160

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?

Upvotes: 1

Related Questions