Here’s an example of how easy it is to extract a set of BLOB’s (in this case, images) from a MySQL database using Python. This will make it easier for our content manager to move the images into our new content management system.
#!/usr/bin/env python import MySQLdb db=MySQLdb.connect(host="host.law.emory.edu",user="dbuser",passwd="dbpass",db="dbname") c=db.cursor() c.execute("""SELECT * FROM dbname.HS_KB_Documents""") myresults = c.fetchall() for data in myresults: doc_id = str(data) if data is not None: outfile=open(doc_id + "-" + data,'wb') outfile.write(data) outfile.close() print("Wrote %s" % doc_id + "-" + data) else: print("No data for %s" % doc_id + data)
In this case, we pull out each row of an image table. There is an id number in the first column, along with a filename in column 3. The last column (column 5) contains the actual blob data. We do some very primitive error checking – if there is no data in the BLOB column, the file is not written.
The script created 189 files in a very short period and helped us with our migration to a new content system. Thanks to Zetcode for the tutorial that helped me do this: http://zetcode.com/databases/mysqlpythontutorial/