Storing files in a BLOB field via SQL

Juergen Gerner J.Gerner at GernerOnline.de
Sun Jun 6 22:31:35 CEST 2004


Hello Python fans,
 
I'm trying and searching for many days for an acceptable solution...
without success. I want to store files in a database using BLOB
fields. The database table has an ID field (INT, auto_increment), an
ORDER field (INT, for knowing the right order) and a "normal" BLOB
field. It is planned to split large files in 64k-parts and sort these
parts by the ORDER field.

Here's some pseudo code how I wanted to implement this in my app:
 
file = file_open(myFileName, read_only)
order = 0
data = file.read(65535)
while (data):
	query = "INSERT INTO table (order,data) VALUES (%i,%s)", order, data
	mysql_exec(query)
	order = order + 1
	data = file.readBlock(65535)

The main problem is the handling of the binary data. There might be
errors in the SQL syntax if some special chars (quotas etc.) appear,
or the SQL statement is incorrect because of strange chars that can't
be encoded by the current codeset. Another problem is, you can't strip
these chars because you would change the binary data or make it bigger
than 64k.

Does anybody of you have an idea?
Any suggestions would be very helpful.

Additionally, I want to compress the data and store a checksum
somewhere. Any hint (links, sites, ...) is welcome...!

Thanks in advance,
Juergen



More information about the Python-list mailing list