Python Parameterized Queries with Special Characters and UTF-8 Characters

Question

I'm new to Python, but haven't been able to find a good reference on how to do it. I think I might be confused about how to use of Unicode characters/strings.

I'm trying to create parameterized queries to work with Unicode characters, insert and select to/from a MySQL db for the following values:

valueUTF8 = u"Программирование - プログラミング"
valueSpecialCharacters = u"`~!@#$%^&*()_+[]\\;',./{}|:\"<>?"
valueSpecialCharacters2 = u"¢¥¦§©«¬®æƸɅɆɜʘɷɸӒӔӥӺӾسشصضطّ٦۝۞۩ᴚᴇᴈᵺḈᵯἃἮᾝᾸ₨₸∑∏∆∂℮ⅎ₲∩∫≈≠≡≤≥⌂℗⅝⅞⅓⅔⅛⅜⅍"
valueSpecialCharacters3 = u"░▒▓■□▪▫▬▲►▼◄◊○◌●◘◙◦☺☻☼♀♂♠♣♥♦♪♫♯ⱠⱡⱢⱣⱤⱥⱦⱧﭮצּרּﭓךּﺹﻏ﷼ﻪﻯﻴﻹ  ﻼ"

Once I get these working I plan to use them as part of some unit tests.

what is the problem? all of those are unicode currently you probably just can insert them .... what have you tried so far? and what error did you get? this may be helpful to you http://stackoverflow.com/questions/6202726/writing-utf-8-string-to-mysql-with-python — Joran Beasley, Sep 04 '14 at 23:47
I've trade a bunch of errors for others. One that I remember was it complaining about latin literals. I'm curious if I need to decode the string into utf8 or unicode then use it after that. I'm still playing with it, I'll look at the link you sent probably tomorrow too and see if some sleep helps me figure it out better tomorrow. — James Oravec, Sep 05 '14 at 00:07
specifically http://stackoverflow.com/a/6203001/541038 this answer — Joran Beasley, Sep 05 '14 at 00:08
@JoranBeasley thanks for the suggesions. I used them in making my post below. — James Oravec, Sep 05 '14 at 20:35

score 0 · Answer 1 · answered Sep 05 '14 at 20:16

So my main problem was that I was missing the charset="utf8" in my db connection string. After I had that, everything else was easy.

Below is some of my testing code, as a reference in case anyone else comes across the same issue. It does inserts of the special values into one db, then reads them from that db and inserts it into a different db. I used a 3rd party tool to query the db and verify the results. The last test I did was a delete based on a name:

#!/usr/bin/python    
# -*- coding: UTF-8 -*-
import MySQLdb

db1 = MySQLdb.connect(host="10.100.10.2",
                     user="root",
                     passwd="",
                     db="db1",
                     charset="utf8"
                     )
db1.autocommit(True)
cur1 = db1.cursor()

db2 = MySQLdb.connect(host="10.100.10.2",
                     user="root",
                     passwd="",
                     db="db2",
                     charset="utf8"
                     )
db2.autocommit(True)
cur2 = db2.cursor()

valueSpecialCharacters = u"`~!@#$%^&*()_+[]\\;',./{}|:\"<>?"
valueSpecialCharacters2 = u"¢¥¦§©«¬®æƸɅɆɜʘɷɸӒӔӥӺӾسشصضطّ٦۝۞"
valueSpecialCharacters3 = u"۩ᴚᴇᴈᵺḈᵯἃἮᾝᾸ₨₸∑∏∆∂℮ⅎ₲∩∫≈≠≡≤≥⌂℗⅝⅞"
valueSpecialCharacters4 = u"⅓⅔⅛⅜⅍░▒▓■□▪▫▬▲►▼◄◊○◌●◘◙◦☺☻☼♀♂♠♣"
valueSpecialCharacters5 = u"♥♦♪♫♯ⱠⱡⱢⱣⱤⱥⱦⱧﭮצּרּﭓךּﺹﻏ﷼ﻪﻯﻴﻹ  ﻼ"

sqlInsert = "INSERT INTO table (tableID, Name) VALUES (%s, %s);"

# Clean tables to make easier to see updates.
cur1.execute("DELETE FROM table")
cur2.execute("DELETE FROM table")

print "1: " + valueSpecialCharacters
args = "1", valueSpecialCharacters 
cur1.execute(sqlInsert, args)

print "2: " + valueSpecialCharacters2
args = "2", valueSpecialCharacters2 
cur1.execute(sqlInsert, args)

print "3: " + valueSpecialCharacters3
args = "3", valueSpecialCharacters3 
cur1.execute(sqlInsert, args)

print "4: " + valueSpecialCharacters4
args = "4", valueSpecialCharacters4 
cur1.execute(sqlInsert, args)

print "5: " + valueSpecialCharacters5
args = "5", valueSpecialCharacters5 
cur1.execute(sqlInsert, args)

sqlSelect = "SELECT tableID, Name FROM table;"
cur1.execute(sqlSelect)
results = cur1.fetchall()
for row in results:
    args = row[0], row[1]
    print "args: %s", args
    cur2.execute(sqlInsert, args)

# Test of deleting with utf-8
sqlDelete = "DELETE FROM table2 WHERE Name = %s;"
cur1.execute(sqlDelete, valueSpecialCharacters2)

db1.close()
db2.close()

Python Parameterized Queries with Special Characters and UTF-8 Characters

1 Answers1