how to store binary file data to a database blob

Discussion in 'Python' started by David Stockwell, May 27, 2004.

  1. 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/
    David Stockwell, May 27, 2004
    #1
    1. Advertising

  2. > 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)...
    Benjamin Niemann, May 27, 2004
    #2
    1. Advertising

  3. David Stockwell

    Ivan Voras Guest

    Benjamin Niemann wrote:

    > 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. :)
    Ivan Voras, May 27, 2004
    #3
  4. David Stockwell

    Greg Gaughan Guest

    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


    "David Stockwell" <> wrote in message
    news:...
    > 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
    Greg Gaughan, May 28, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Selen

    blob or store server

    Selen, Feb 20, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    297
    S. Justin Gengo
    Feb 20, 2004
  2. Universal
    Replies:
    1
    Views:
    593
    Lionel B
    Apr 8, 2005
  3. Universal
    Replies:
    0
    Views:
    451
    Universal
    Apr 8, 2005
  4. David Stockwell
    Replies:
    0
    Views:
    400
    David Stockwell
    May 27, 2004
  5. Rune Hansen

    MySQLdb blob and binary data

    Rune Hansen, Dec 10, 2004, in forum: Python
    Replies:
    1
    Views:
    1,621
    Denis S. Otkidach
    Dec 10, 2004
Loading...

Share This Page