Reputation: 516
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]))
Upvotes: 0
Views: 3155
Reputation: 1
Try using '
instead of "
:
cursor.execute('''UPDATE pjStores SET %s=%s WHERE code=%s''', (store, result, code))
Upvotes: 0
Reputation: 132138
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]))
Upvotes: 2