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
> 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.
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
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