MYSQL and use of blobs

[Mark Hertel]

On Thu, 29 Jul 2004 08:48:28 -0500, Raaijmakers, Vincent (GE
Infrastructure) <Vincent.Raaijmakers at ge.com> wrote:

> It is the first time that I use blobs in mysql Please help me out    .
> here....                                                             .
>
> Using MSSQLdb and python 2.3.4 I was surprised to see how my
> information was stored in the blob. My goal is to store JPG
> images in the blob. Well, it stores it but as a string of bytes:
> 'x0dx0fxffxa3......'
>
> So, when reading back that information into my python environment, it
> seems that I need to translate the value back to the original binary
> format?
>
> Sniffed for an hour in the asciibin, binhex and other modules...
>
> Somebody with experience to help me out here? Is there something
> fundamentally that I do wrong here? In storage, retreiving...
>
> Any help would be very appreciated. Vincent


I didn't have to do anything more fancy than read the information
from the database and write it as a binary file.

SQL table:

create table if not exists audioImage(id int auto_increment primary key,
problemID int, realName varchar(250), shortName varchar(250), data
longblob


Storing the images/audio files:
  fileData = open(fileName, 'rb').read()
  fileData = zlib.compress(fileData,9)
  fileData = db.escape_string(fileData)
  query = "insert into audioImage(problemID, data,
           shortName) values(%d, '%s', '%s')" % (problemID,
           fileData, file)

Retrieving the stored images or audio files:
self.db.execute('select shortName,data from audioImage where id=%d' % int(id))
result = self.db.getAll()
fileName = result[0][0]
retResult = result[0][0]
realData = zlib.decompress(result[0][1])
open(fileName,'wb').write(realData)


This deposits the audio/image files in the current directory where
the program picks them up and uses them. There wasn't anything special
to do, other than retrieving and decompressing the data.


--Mark Hertel