Extracting image data to individual files with Python and MySQL

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[1])
 if data[5] is not None:
   outfile=open(doc_id + "-" + data[3],'wb')
   outfile.write(data[5])
   outfile.close()
   print("Wrote %s" % doc_id + "-" + data[3])
 else:
   print("No data for %s" % doc_id + data[3])

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/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s