JSON and MySQL UPDATE query

Question

I'm trying to get info from a JSON msg out of ZeroMQ into MySQL by Python. This is the piece of code I'm attempting to run:

for i in json_msg["PropertyInfoMsg"]:
    db2 = MySQLdb.connect(host="localhost", user="user", passwd="pass", db="db")
    cursor2 = db2.cursor()
    sql = """UPDATE settings SET value=%s WHERE name=%s""" % (i["PropertyType"].lower(), i["PropertyValue"].lower())
    cursor2.execute(sql)
    db2.commit()
    cursor2.close()

But it's comming back as:

1064, 'You have an error in your SQL syntax

I could really use a second pair of eyes at this at this point in time, I feel like I'm completely missing it.

Priting out the sql variable returns:

UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()
UPDATE settings SET value=i["PropertyType"].lower() WHERE name=i["PropertyValue"].lower()

What does sql print out to when you add in the 2 string values? — Carlos
– Carlos, Commented Jul 20, 2016 at 8:35
Updated the original post to include your awnser's question. — user5740843
– user5740843, Commented Jul 20, 2016 at 8:38

Blank · Accepted Answer · 2016-07-20 08:47:49Z

2

You should wrap your parameters with single quote:

sql = """UPDATE settings SET value='%s' WHERE name='%s'""" % (i["PropertyType"].lower(), i["PropertyValue"].lower())

Also you must confirm your json data is correct.

edited Jul 20, 2016 at 8:47

answered Jul 20, 2016 at 8:41

Blank

12.4k1 gold badge16 silver badges34 bronze badges

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

Comments

pwnsauce · Accepted Answer · 2016-07-20 08:40:31Z

2

I would do the query this way :

cursor2.execute("UPDATE settings SET value = %s WHERE name = %s",(i["PropertyType"].lower(), i["PropertyValue"].lower()))

answered Jul 20, 2016 at 8:40

pwnsauce

4144 silver badges14 bronze badges

Collectives™ on Stack Overflow

JSON and MySQL UPDATE query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related