Saving Binary Data To MySQLdb - Python

Question

I have googled and searched this site, but nothing specific appears and cannot get this working. Here is the code:

    binData = ''.join(map(lambda x: chr(x % 256), attach.attcoll))

    sql_stmt = """INSERT INTO attachments (attno,filename,fileextension,projNo,procNo,wpattachment) \
    VALUES ('%s','%s','%s','%s','%s','%s') ON DUPLICATE KEY UPDATE filename='%s',fileextension='%s'""", attach.attno,\
attach.filename,attach.fileextension,attach.projNo,attach.procNo,binData,attach.filename,attach.fileextension

    try:
        cursor.execute(MySQLdb.escape_string(sql_stmt))
        conn.commit()

The attach.attcoll is data that comes in over JSON from an SQLite blob and java code on the client side. I then want to write binData to MySQL with MySQLdb, but keep getting a TypeError, and a return code of 500. Any ideas on how to fix this. I want to store binData into a MySQL blob.

Jesse Cohen · Accepted Answer · 2011-02-25 00:07:04Z

1

Bunch of things here:

binData = ''.join(map(lambda x: chr(x % 256), attach.attcoll))
sql_stmt = """INSERT INTO attachments (attno,filename,fileextension,projNo,procNo,wpattachment)
VALUES (%s,%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE filename=%s,fileextension=%s""" 

params = (attach.attno,attach.filename,attach.fileextension,attach.projNo,attach.procNo,binData,attach.filename,attach.fileextension)

cursor.execute(sql_stmt, params)
conn.commit()

A summary:

Make sure you are connected to the db first (if you don't know how to do this, read the MySQLdb documentation)
You don't want to escape your entire query, only the data parts, passing the statement and data separately to cursor.execute() means the cursor will escape it for you.
Dont quote your '%s', MySQLdb does that for you as well.
Is your database transactional (i.e. InnoDB), is that why you are using conn.commit(), it is not necessary if you aren't using a transactional engine.
Without seeing how you've structured your db table I can't guarantee this will work. As practice, have you tried creating a really simple database table and practiced inserting into it with MySQLdb, just so you familiarize yourself with the API?

answered Feb 25, 2011 at 0:07

Jesse Cohen

4,07025 silver badges25 bronze badges

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

7 Comments

kirtcathey Over a year ago

1. I am connected... 2. Interesting... 3. Doesn't execute without those single quotes... at all. Will try again. 4. Well beyond that... the question is very specific to 'how to best store blob/binary data'. I am connected, if I eliminate binData from the script, all works fine - even with single quotes. The question stands alone because all other factors are accounted for. Any help greatly appreciated.

Jesse Cohen Over a year ago

ok was just checking. there were a lot of things wrong with your query so wanted to make sure you were familiar with MySQLdb and cover all the bases i could think of

Jesse Cohen Over a year ago

i dont understand how it works fine when you are escaping your entire query, furthermore, according to your code sql_stmt isnt a string, its a tuple, and MySQLdb.escape_string() raises an exception when given a tuple.

kirtcathey Over a year ago

Thanks Jesse! Close! No TypeError any more, but still returning a 500. #2 in your checklist above was the key. Instead of inserts, I concatenated the strings around binData.

Jesse Cohen Over a year ago

no quotes around %s here is a line from a working app of mine that stores jpgs in a mysql database: c.execute("""UPDATE items SET image=%s WHERE id=%s""",(img, id)) img is a binary string, the raw contents of a jpg file

|

Collectives™ on Stack Overflow

Saving Binary Data To MySQLdb - Python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related