How to insert / retrieve a file stored as a BLOB in a MySQL db using python

Question

I want to write a python script that populates a database with some information. One of the columns in my table is a BLOB that I would like to save a file to for each entry.

How can I read the file (binary) and insert it into the DB using python? Likewise, how can I retrieve it and write that file back to some arbitrary location on the hard drive?

Answer 1

thedata = open('thefile', 'rb').read()
sql = "INSERT INTO sometable (theblobcolumn) VALUES (%s)"
cursor.execute(sql, (thedata,))

That code of course works as written only if your table has just the BLOB column and what you want to do is INSERT, but of course you could easily tweak it to add more columns, use UPDATE instead of INSERT, or whatever it is that you exactly need to do.

I'm also assuming your file is binary rather than text, etc; again, if my guesses are incorrect it's easy for you to tweak the above code accordingly.

Some kind of SELECT on cursor.execute, then some kind of fetching from the cursor, is how you retrieve BLOB data, exactly like you retrieve any other kind of data.