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]))

It is like you have a quote problem, if you look at the error (='Madness16") it is really that? — Jeremy D
– Jeremy D, Commented Mar 24, 2012 at 4:13
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. — Doa
– Doa, Commented Mar 24, 2012 at 4:18
@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
– xur17, Commented Mar 24, 2012 at 4:22

sberry · Accepted Answer · 2012-03-24 04:29:16Z

2

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]))

answered Mar 24, 2012 at 4:29

sberry

133k20 gold badges145 silver badges171 bronze badges

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

2 Comments

xur17 Over a year ago

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 Over a year ago

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?

addiedx44 · Accepted Answer · 2013-09-16 17:03:51Z

0

Try using ' instead of ":

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

edited Sep 16, 2013 at 17:03

addiedx44

2,7531 gold badge25 silver badges27 bronze badges

answered Sep 16, 2013 at 16:37

Balaji

1

Collectives™ on Stack Overflow

Python Mysql Update Execute Command

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related