Insert python binary string object in MySQL blob
mysql, python
Solution
Do it this way, instead:
query = '''INSERT INTO cheese (data) VALUES (%s)'''
cur.execute(query, (bd,))
Instead of doing Python-level string formatting, this uses MySQL-specific formatting, including escaping characters that have special meaning to MySQL in strings that are to be embedded in queries.
Problem
I'd like to insert a string object containing binary data into a MySQL blob column. However, I keep getting MySQL syntax errors. I made a small script for debugging purposes: ``` import MySQLdb import array import random conn = MySQLdb.connect(host="localhost", user="u", passwd="p", db="cheese") cur = conn.cursor() a = array.array('f') for n in range(1,5): a.append(random.random()) bd = a.tostring() print type(bd) # gives str query = '''INSERT INTO cheese (data) VALUES (%s)''' % bd cur.execute(query) ``` Result (but not every time...) ``` 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 '?fJ\x95<= \xad9>\xf3\xec\xe5>)' at line 1") ``` The problem apparently boils down to certain characters in the binary data which MySQL doesn't like. Is there a fail-safe way of putting binary data into the MySQL database?