MySQL protocol is basically encoded text, but it may contain arbitrary (escaped) binary.
Here is simplified example constructing real SQL from SQL format and arguments. (Works only on Python 2.7)
def escape_string(s):
return s.replace("'", "''")
def convert(x):
if isinstance(x, unicode):
x = x.encode('utf-8') # Use encoding assigned to connection in real.
if isinstance(x, str):
x = "'" + escape_string(x) + "'" # 'quoted and '' escaped string'
else:
x = str(x) # like 42
return x
def build_query(query, *args):
if isinstance(query, unicode):
query = query.encode('utf-8')
return query % tuple(map(convert, args))
textdata = b"hello"
bindata = b"abc\xff\x00"
query = "UPDATE table SET textcol=%s bincol=%s"
print build_query(query, textdata, bindata)I can't port this to Python 3.
Fortunately, MySQL supports hex string like x'616263ff00'
So I use it and PyMySQL supports binary data on Python 3.
But hex string consumes double space than normal (escaped) bytes.
This is why I don't use hexstring on Python 2.