Python Mysql Update Execute Command

Question

I am trying to execute the following command, setting "store"="result", for the row where the code column equals "code".

cursor.execute("""UPDATE pjStores SET %s=%s WHERE code=%s""", (store, result, code))

I keep receiving the following error though:

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1087'=1 WHERE code='Madness16'' at line 1")

Where the variables from the command were:

store=1087
result=1
code=Madness16

This is my first time really using mysql, so I am new to this. I've been stuck on this line for ~2 hours now, and cannot figure out what I am doing wrong. I tried the following command in mysql, and it ran correctly:

UPDATE pjStores SET `1087`=1 WHERE code='Madness16'

More code as requested:

# Step through stores
cursor.execute("SHOW COLUMNS FROM pjStores")
stores = cursor.fetchall()
cursor.execute("SELECT code FROM pjStores")
codes = cursor.fetchall()
for store in stores[1:]: # Hack to skip first entry
 pj.setStore(store[0])
 for code in codes:
 result = pj.checkCode(code[0])
 cursor.execute ("""UPDATE pjStores SET %d=%s WHERE code=%s""", (store[0], result, code[0]))

• Jeremy D: It is like you have a quote problem, if you look at the error (='Madness16") it is really that?
• Doa: Why are you using integer for field names? 1087 should not be a field name, but an ID. The problem is probably caused by mixing up strings and ints, but one can't be sure unless we see more code.
• xur17: @JeremyD The quotes issue was just in this post, the actual error showed two single quotes instead of a double quote. I updated the post so it is formatted correctly.
• xur17: @Dow I added more code as requested.
• garnertb: Can you post your table schema?

Answer 1

You may want to try something like this instead, (assuming this is not prone to sql injection attacks - meaning the data is trusted and not user provided)

...
for code in code:
 result = pj.checkCode(code[0])
 query = """UPDATE pjStores SET `%s` = %%s WHERE `code` = %%s""" % store[0]
 cursor.execute(query, (result, code[0]))

• xur17: This won't be prone to sql injection attacks. It's a local script run by me to scrape a website. I'm giving this a try right now, I'll report back.
• xur17: Thanks, this solved the problem for me! Out of curiosity, was this problem occurring because the columns are titled with integers? Is it a problem for me to do this, or just not usual for integers to be used for the column names?

Answer 2

Try using ' instead of ":

cursor.execute('''UPDATE pjStores SET %s=%s WHERE code=%s''', (store, result, code))