Extract Binary File & Images from MySQL to Filesystem with Ruby

Today i needed to extract some images that had (unwisely) been stored as BLOB in a mysql (imported from MS-SQL) database. Here is how i extracted the files to my filesystem using the console & ActiveRecord. You ARE using the console aren’t you?Assuming the column holding the binary data is called picture_bin and your Model is called Picture:

Picture.find(:all).each do |f|
  File.open(File.join(RAILS_ROOT, "photos", "#{f.id}.jpg"), "w") do |file|
    file.write(f.picture_bin)
  end
end

And if you’re using file_column, assigning the image back to the record is as easy as:

Picture.find(:all).each do |f|
  f.filename = File.open(File.join(RAILS_ROOT, "photos", "#{f.id}.jpg"), "r")
  f.save
end

About this entry

You’re currently reading “Extract Binary File & Images from MySQL to Filesystem with Ruby,” an entry on Indierockmedia

Published:
12.12.07 / 6pm
Category:
ActiveRecord, BLOB, Rails, mysql, ruby