python <--> MySQL w.r.t. binary files (bitmap images like .jpg's)

[richard at insight20.freeserve.co.uk]

Hi

fairly new to python - hope this a helpful post

set context - here's a message I sent - 

"Hi all

 Got the ans to the issue of loading binaries, which includes bitmap
 image files like .jpg's and .png's, into the MySQL database.

 Proves that the Python/MySQL pairing makes it simple (touch wood).

 For those of you less familiar, MySQL is a relational database.  Ok -
 slight qualification - the relational database is the data, organised
 in "tables" which relate to eachother, all regarded as a single entity
 known as "the database".  (on a unix file system (and other current
 platforms) the "database" is represented by a directory and the
 "tables" by individual files within that directory - there are also
 other files which are maintained by the database *program* to
 facilitate indexing etc)
 Have slightly "jumped the gun" in making the last distinction - MySQL
 is a *database program* which manages and operates upon databases and
 provides access into the databases for the user (the program can
 oversee many databases).

 So, loading binaries to the database... given that there is a datatype
 "BLOB" (TINYBLOB (256Bytes?) up to and including LONGBLOB for binary
 data up to 4GB)...

 The interface to the MySQL database program is the "mysql" monitor
 (that's what you start-up when you type "mysql" at the command line) -
 it starts-up and you identify yourself to the database program.  You
 can also reach into the database from programming languages via
 modules written for the language to interface with database program
 through "calls" provided for this purpose by the database program.

 The problem I ran into was that binary files can "accidentally appear
 to have" control characters which trigger events (unwanted) in the db
 interface.  For instance in a .jpg, you are going to at some point
 have a sequence of bits at some multiple of 8bit (1byte) offset from
 the beginning of the file which reads 00001010, which has no relation
 at all to the ASCII "newline" character, which in the ASCII character
 set has this bit pattern (it's represented by the hexadecimal number
 0x0A, which if you convert that to binary is the bit sequence I have
 just quoted).  There are other bit patterns which "appear" the same as
 other ASCII control characters.  The single-quote and double-quote
 characters also cause problems to the "mysql" MySQL monitor and/or the
 interface.

 What you need to do is to "escape" these bit patterns in the binary
 file by inserting the slash character before these "problem"
 bit-patterns (they are not seen when the binary file is recovered from
 MySQL).

 Jumping straight to the answer - (correct me if I am wrong on the
 location of this function) the MySQL API (Application Programming
 Interface) provides the function "mysql_escape_string()" which
 performs the "escaping" of the characters which would otherwise cause
 the interface problems during reading-in.  This has been "wrappered"
 in the MySQLdb-0.2.1 module which abstracts the API as a set of Python
 objects.  It is in the _mysql module, which you would not normally use
 directly because it presents a fundamental API which is in general
 abstracted into higher level API's so that your SQL is transportable
 between databases.  However, this is an application-specific problem,
 and the answer is application specific.  So, let's rock...

 In patchy pieces of Python (complete listing of an example follows)

 import _mysql
 import MySQLdb
 (connect to database)
 (create a cursor object)
 ("open" your binary (eg. (bitmap) image) file and get a file handle
 object)
 (build a string of the SQL you want to "execute" which looks like this
 sqlstr="update ... set ...='" + _mysql.escape_string(imgfile.read())
 +"' where ...")
 cursor_obj.execute(sqlstr)

 That's it - the binary is in the database.

 When you "select" the thing, you get it back as the binary pre- the
 "escaping" operation.

 Have fun.

 Here's the example code listing...

 Richard Smith

 ------------------------------------------------------------------------

 #!/usr/local/bin/python

 import _mysql
 # MySQL (ie. implementation) specific - but fairly
 # implementation-specific problem

 import MySQLdb
 db=MySQLdb.connect(db="cheeses",user="a_user",passwd="a_passwd")
 c=db.cursor()

 imgfile=open("a_cheese.png",'rb')
 ## imgvar=imgfile.read() # avoid this

 # put SQL strings in place
 # "examine_str"
 sql_db_view="select * from cheese_name where cheese_name_pk=1"
 # reset
 sql_reset_img1="update cheese_name set picture_img=NULL where cheese_name_pk=1"
 # specifically retrieve the image field
 sql_db_image_view="select picture_img from cheese_name where cheese_name_pk=1"
 # "do it" SQL command - the "update" loading of the image file into the database
 sqlstr="update cheese_name set picture_img='" + _mysql.escape_string(imgfile.read()) +"' where cheese_name_pk=1"

 # do reset...
 c.execute(sql_reset_img1)

 # do the "update" load...
 c.execute(sqlstr)

 # look at what the result is...
 c.execute(sql_db_image_view)

 # do file write
 f_out=open('tmp.img.png','wb')
 f_out.write(c.fetchone()[0])
 # flush the cache / buffer whatever now
 f_out.close()

 # other tidy-ups
 imgfile.close()"

To which I got one answer...

"> import _mysql
 > import MySQLdb
 > (connect to database)
 > (create a cursor object)
 > ("open" your binary (eg. (bitmap) image) file and get a file handle
 > object)
 > (build a string of the SQL you want to "execute" which looks like this
 > sqlstr="update ... set ...='" + _mysql.escape_string(imgfile.read())
 > +"' where ...")
 > cursor_obj.execute(sqlstr)

 If you were using Oracle and Pro*C, you would do something like:

 EXEC SQL UPDATE ... set column=:value

 where ':value' represents a C variable containing a value.  The value in the
 C variable isn't parsed by the SQL parser and needn't be escaped.

 Perl's DBI and DBD interface has something similar, though I can't remember
 exactly how it's done.

 I would be amazed if there wasn't a similar binding step in Python's
 database interface (though, I admit, it does sound less well thought out
 than the Perl one, which has a standard front-end and a variety of back-ends
 for different databases).  You should probably use this rather than taking a
 large binary file, converting it (effectively) to text, forcing the parser
 to parse it and convert it back to binary."

(from a very helpful much appreciated character)

So - is there a neater way of doing this - such as in line with the
hint of the (very much appreciated) respondent?

Richard Smith