Insert python binary string object in MySQL blob

Question

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?

djc · Accepted Answer · 2013-03-20 10:23:14Z

13

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.

edited Mar 20, 2013 at 10:23

answered Mar 19, 2013 at 15:55

djc

11.8k5 gold badges44 silver badges55 bronze badges

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

2 Comments

jw_p Over a year ago

Yes, this seems to work! I really don't understand the underlying principle though. Thanks!

djc Over a year ago

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.

Mahmoud Aladdin · Accepted Answer · 2013-03-19 16:07:25Z

1

The error is not related to the formating of the string, but the an SQL syntax. So I guess the problem is with the SQL query itself, not the characters. Use query = '''INSERT INTO cheese (data) VALUES ('%s')''' % bd Where you enclose the string in question in a single quote.

answered Mar 19, 2013 at 16:07

Mahmoud Aladdin

5461 gold badge3 silver badges14 bronze badges

1 Comment

jw_p Over a year ago

I already tried different type of quotes in different combinations, so that wasn't the issue.

Collectives™ on Stack Overflow

Insert python binary string object in MySQL blob

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related