471,339 Members | 1,461 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,339 software developers and data experts.

How to read a binary file into a mysql table

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

Dec 14 '07 #1
4 7020
En Fri, 14 Dec 2007 12:19:41 -0300, Hans Müller <He******@web.deescribió:
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

Dec 14 '07 #2
On Dec 14, 5:41 pm, "Gabriel Genellina" <gagsl-...@yahoo.com.ar>
wrote:
En Fri, 14 Dec 2007 12:19:41 -0300, Hans Müller <HeinT...@web.deescribió:
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.
Dec 15 '07 #3
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
Dec 18 '07 #4
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
Dec 18 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Gurk | last post: by
reply views Thread by Ivan Horvath | last post: by
reply views Thread by Jean Hagen | last post: by
reply views Thread by David List | last post: by
3 posts views Thread by Me Alone | last post: by
3 posts views Thread by =?Utf-8?B?ZGF2aWQ=?= | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.