How to read a binary file into a mysql table

Discussion in 'Python' started by Hans Müller, Dec 14, 2007.

  1. Hans Müller

    Hans Müller Guest

    Good morning folks,

    I cannot read a binary file into a mysql database. Everything I tried did not succeed.

    What I tried (found from various google lookups...) is this:

    con = MySQLdb.connect(to server)
    cur = con.cursor()

    cur.execute("insert into data values('file1', %s)", (open("test.jpg", "rb").read(), ))

    also this doesn't work:

    execute("insert into data values('file1', %s), (MySQLdb.escape_string(open("test.jpg", "rb").read()), ))

    I always get this:

    Warning: Data truncated for column 'file' at row 1

    The blob data is actually chopped.

    The Table has two columns, char(100), blob

    Has someone a working idea how to get binary file into a blob using MySQLdb and python ?!

    System is SuSE 10.0 Linux with python 2.5.1, current MySQLdb version, MySQL is: 5.0.26

    Thanks a lot!

    Greetings

    Hans
     
    Hans Müller, Dec 14, 2007
    #1
    1. Advertising

  2. En Fri, 14 Dec 2007 12:19:41 -0300, Hans Müller <> escribió:

    > I cannot read a binary file into a mysql database. Everything I tried
    > did not succeed.
    >
    > What I tried (found from various google lookups...) is this:
    >
    > con = MySQLdb.connect(to server)
    > cur = con.cursor()
    >
    > cur.execute("insert into data values('file1', %s)", (open("test.jpg",
    > "rb").read(), ))


    Try wrapping the file contents with a Binary object (untested):

    data = MySQLdb.Binary(open("test.jpg","rb").read())
    cur.execute("insert into data values('file1', %s)", (data,))

    --
    Gabriel Genellina
     
    Gabriel Genellina, Dec 14, 2007
    #2
    1. Advertising

  3. Hans Müller

    Benoit Guest

    On Dec 14, 5:41 pm, "Gabriel Genellina" <>
    wrote:
    > En Fri, 14 Dec 2007 12:19:41 -0300, Hans Müller <> escribió:
    >
    > > I cannot read a binary file into a mysql database. Everything I tried
    > > did not succeed.

    >
    > > What I tried (found from various google lookups...) is this:

    >
    > > con = MySQLdb.connect(to server)
    > > cur = con.cursor()

    >
    > > cur.execute("insert into data values('file1', %s)", (open("test.jpg",
    > > "rb").read(), ))

    >
    > Try wrapping the file contents with a Binary object (untested):
    >
    > data = MySQLdb.Binary(open("test.jpg","rb").read())
    > cur.execute("insert into data values('file1', %s)", (data,))
    >
    > --
    > Gabriel Genellina


    I was suprised at what I could stick into a MySQL database. Also, you
    might wanna compress the binary for database performance.
     
    Benoit, Dec 15, 2007
    #3
  4. Hans Müller

    Hans Müller Guest

    Hello,

    thanks a lot for the Binary(). This does the trick, now I can import all my binary data.
    But now I found the next problem:
    The Blob is now limited to 65535 Bytes. I love all these stone age (16bit) limits on my 64bit machines...
    All bigger files a truncated.

    Has someone an idea how to solve this ?


    Greetings

    Hans
     
    Hans Müller, Dec 18, 2007
    #4
  5. Hans Müller

    Hans Müller Guest

    Sorry, I found the mistake:

    There is more than one blob type, blob as a default stores only 64k of data.
    LONGBLOB has a 4G limit which is Ok for my purposes.

    Happy Christmas to all,

    Hans
     
    Hans Müller, Dec 18, 2007
    #5
    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. JL
    Replies:
    0
    Views:
    1,178
  2. Jeffrey H. Coffield
    Replies:
    1
    Views:
    1,934
  3. Roedy Green
    Replies:
    0
    Views:
    1,521
    Roedy Green
    Jul 23, 2009
  4. julian
    Replies:
    8
    Views:
    509
    Avatar
    Apr 6, 2006
  5. Sam
    Replies:
    4
    Views:
    89
    Mark Lawrence
    Jan 17, 2014
Loading...

Share This Page