Python MySQL INSERT unicode

Question

I'm trying to insert JSON data into an MySQL database:

def mapClients():
 for d in devices:
 clientMap = d['dot11.device']['dot11.device.associated_client_map'].keys()
 for item in clientMap:
 clientList = kr.device_by_mac(item)
 times = kr.device_summary_since()
 for c in clientList:
 sqlMac = c['kismet.device.base.macaddr'],
 sqlType = c['kismet.device.base.type'],
 sqlManuf = c['kismet.device.base.manuf'],
 ktime = c['kismet.device.base.last_time'],
 for t in ktime:
 sqlTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(t)) 
 cur.execute("INSERT INTO devices(apID,mac,type,manuf,last_seen) VALUES(1,'" + str(sqlMac) + "','" + str(sqlType) + "','" + str(sqlManuf) + "','" + sqlTime + "');")
 conn.commit()
mapClients()

This returns the following error:

pymysql.err.ProgrammingError: (1064, u"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 'VALUES(1,'(u'58:E2:8F:CF:20:B3',)','(u'Wi-Fi Client',)','(u'Apple',)','20-10-201' at line 1")

I can see from the error that the various values are being suffixed with a 'u'. I understand through a lot of searching and learning that (I think) this means the data is unicode.

What I want to do is find a way of converting/decoding the data so the INSERT statements work. Some of the variables are tuples, some strings. Any help much appreciated.

• Abdullah Razzaki: for the columns who have (u'value') use variable[0]. like replace str(sqlMac) with str(sqlMac[0])
• user4691770: @AbdullahRazzaki Thank you this got it working! I can't close this as answered unless you post that as an answer :) if you do I'll close it off. Great thinking and thanks again :)
• Abdullah Razzaki: You're welcome but my approach is incorrect because I overlooked the commas and tought you got tuples from the functions use @Martjin's approach. It's the right way
• user4691770: @AbdullahRazzaki Thank you. I'm having a look at this now with fresh eyes and a coffee :)

Answer 1

You are inserting tuples, not strings; remove the trailing commas:

sqlMac = c['kismet.device.base.macaddr']
sqlType = c['kismet.device.base.type']
sqlManuf = c['kismet.device.base.manuf']
ktime = c['kismet.device.base.last_time']
sqlTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(ktime))

It’s the trailing comma that turns those expressions into tuples, and str() on a tuple gives you the container Unicode string as the u'....' representation that then clashes with the ' quoting you are adding.

Note that removes the need to loop over ktime!

Next, you really want to use SQL parameters, not string concatenation. Use placeholders instead of '" + str(...) + "', and leave handling of quoting to the database adapter:

cur.execute("""
 INSERT INTO devices (apID, mac, type, manuf, last_seen)
 VALUES (1, %s, %s, %s, %s)
""", (sqlMac, sqlType, sqlManuf, sqlTime))

The %s are the placeholders; depending on your exact MySQL Python library, you may need to use ? questionmarks instead.

Not only would this let you avoid having to think about quoting, it also removes a serious security issue: the JSON you load could contain a SQL injection attack and SQL parameters are the best way to neutralise that attack vector.

• user4691770: Martjin thank you for your response, it's a really good answer with a lot of detail. If I remove the trailing commas (sqlMac = c['kismet.device.base.macaddr']) then I'll get a TypeError as you can't iterate over 'int' objects. The SQl parameters - fair enough, I'm using PyMySql. I'll look into that. For now I got this working by following the advice of @AbdullahRazzaki in the comment below my question.
• Martijn Pieters: @JamesPy PyMySQL uses %s placeholders, yes.
• Abdullah Razzaki: @JamesPy I suppose you're getting the error because ktime is an int now so you can just remove the for loop too and it should work. Remove the commas, I didn't notice the commas. @Martjin's right
• Martijn Pieters: @AbdullahRazzaki aha! That’s where there is an attempt at iteration.
• Martijn Pieters: @JamesPy with the latest updates the iteration issue should be resolved too. Sorry about that, my attention is a bit divided at the moment, I should have spotted that earlier.

Answer 2

For the error message you posted, you have forgotten to place the closing parentheses before the VALUES in your SQL Query. The query should be like:

cur.execute("INSERT INTO devices(apID,mac,type,manuf,last_seen) VALUES(1,'" + str(sqlMac) + "','" + str(sqlType) + "','" + str(sqlManuf) + "','" + str(sqlTime) + "');")

• Martijn Pieters: This doesn’t address the real problem with the quoting.
• shahsani: Yeah, but the error message must have changed with editing and correcting the parentheses, though! :-)
• shahsani: But not the one posted in OP.
• user4691770: @shahsani thank you for this. I posted in OP comments that the sql was incorrect, as I'd being chopping the code up throughout the day and had accidentally copied in some old code there!