how to store binary file data to a database blob

[David Stockwell]

Hi,

I'd like to read the contents of a file into memory. The problem is that
this file is binary. I then want to store the whole thing in memory to a
database as a blob.
I have no problem reading from a file: I did this:

import os
f = open('/bin/ls' , 'r+b')
data = f.read()
f.close()

How do I place that in a blob? If I concatenate this to a sql string, how
will the database sql parser know where my data string ends, knowing that my
data string could contain lots of ' and " and other little things in it. ???

storeString = "insert into mytable (filedata) values ( %s ) " % data

Does that look right? I don't think it would execute correctly.

Thanks


David Stockwell
-------
Cell: http://cellphone.duneram.com/index.html
Cam: http://www.duneram.com/cam/index.html
Tax: http://www.duneram.com/index.html

_________________________________________________________________
MSN Toolbar provides one-click access to Hotmail from any Web page – FREE
download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/

 

[Benjamin Niemann]

I'd like to read the contents of a file into memory. The problem is

that this file is binary. I then want to store the whole thing in
memory to a database as a blob.
I have no problem reading from a file: I did this:

import os
f = open('/bin/ls' , 'r+b')
data = f.read()
f.close()

How do I place that in a blob? If I concatenate this to a sql string,
how will the database sql parser know where my data string ends, knowing
that my data string could contain lots of ' and " and other little
things in it. ???

storeString = "insert into mytable (filedata) values ( %s ) " % data

Does that look right? I don't think it would execute correctly.

This probably won't work... Try this (assuming you are working with a
Python DB API compatible database class):

cursor.execute("insert into mytable (filedata) values ( %s ) ", data)

This will let the database class replace %s by your data, including all
required escaping stuff - in contrast to a 1:1 string replacment when
using the % operator. This works at least for arbitrary strings (and
that's the way to handle strings, if you don't like SQL injections
But I never tried this with blobs - perhaps they need some special
treatment (thinking e.g. of laaarge files)...

 

[Ivan Voras]

using the % operator. This works at least for arbitrary strings (and
that's the way to handle strings, if you don't like SQL injections
But I never tried this with blobs - perhaps they need some special
treatment (thinking e.g. of laaarge files)...

It is generally NOT a good idea to write large binary strings to a (sql
relational) database, even if it has blobs. A much more robust (and faster)
solution is to save general data about the blob in the database (its size,
name, whatever, etc.) and the actual data in a separate file in the
filesystem (or maybe in a "db" database (BDB, GDBM...), if you really like
them).

YMMV, mostly depending on the size of blobs.

 

[Greg Gaughan]

Your DB-API driver should allow you to pass binary data easily via a typed
parameter to execute (or executemany for multiple rows). For example, in
ThinkSQL, you can say:

blobdata=open('image.jpg', 'rb').read()
s1.execute("INSERT INTO picture_table (image) VALUES (?)",
(ThinkSQL.Binary(blobdata),))

Regards,
Greg Gaughan
www.thinksql.co.uk