[Tutor] Mysql BLOB strangeness?

Brian Gustin brian at daviesinc.com
Sat Mar 18 17:32:14 CET 2006


you'd grab a blob the same way you'd grab a text data type- the only 
difference being the data you insert. for example, make a string of 
letters only, insert it into both a blob and a text data type- you can 
get the data out exactly as is either way , however if you attempt to 
put binary data into a text data type, it gets corrupted (char types 
will pad data with spaces to fill out the length specifier, varchar will 
not pad, but *will* truncate trailing spaces, as will text , and in 
binary data- trailing spaces are critical - they are a binary character, 
and the alteration of a single byte in a binary file will render it 
useless)  - it's much like taking a compiled C binary and trying to read 
it in a text editor - you get a long string of incomprehensible 
characters - putting such data in a text field in mysql would render the 
code un-executable, however Blob fields dont care about spaces either 
leading or trailing- it just stores *precisely* what you give it to store.

Ideally, if you think your data will exceed 255 characters in any given 
data type and it will be alphanumeric, non-binary (say, a text file) 
you would use TEXT field type, if you were gonna do something like store 
a JPG image , or even a compiled binary (cant imagine why) you would use 
BLOB field.

Most of this information is readily available at the mysql manual, and 
as far as I know, Python could care less what database type you are 
using, it can get data from mysql as binary or integer or string or 
float, whatever you specify it to be.. I know that with php (my primary 
language, still learning python) data types are automatically converted, 
and with my limited work to date with python/mysql (using both 
python-mysqldb and the ADOdb abstraction layer for python) I have had no 
issues with handling data - when I get an integer from mysql that is of 
type integer, the value I get in python is also integer..

One thing I have been trying to research/find out is a de-cryption 
algorithm using our known key, to reverse mysql's own des_encrypt 
function - we have data stored in a mysql 4.0 table that uses that 
function, which is not supported in mysql 4.1 and 5 , preventing us from 
upgrading that one machine, and Ive been trying to find a way to match 
the DES3 encryption algorithm that mysql uses, (with our known key/seed 
of course) with little luck so far .. but in all my work, Ive never 
noticed any problem in handling data types- but then I read the mysql 
manual pretty throughly, and in my work (extremely high traffic 
production websites) , MySQL optimization and understanding is 
critical.. so maybe something seems trivially simple and obvious to me, 
that may actually need some explanation ?

In short, whether I am working with blob or text, I would query teh 
table and properly type cast the variable or object to the data type I 
am extracting (if I know data is binary, I get it as a raw string, for 
example, if I know data is like an image or a compiled binary file, I 
would handle it as such, rather than making python process it - the 
object would just contain a reference to the location of the data which 
was extracted in the mysql query...)  but then again, I do very little 
work with binary, and dont store files or images to a database in the 
first place.. :)

in case I misunderstood your question- (it could also be read as how you 
extract the data itself from the mysql array result set) - that depends 
heavily on the type of query and method of fetching data, but as far as 
I know, you can just fetch a single piece of data from mysql as a single 
object, and assign it a reference... but that depends heavily on how you 
structure your query and has more to do with mysql than with python 
itself :)

Bri!



Adam Cripps wrote:
> On 3/17/06, Brian Gustin <brian at daviesinc.com> wrote:
> 
>>if the data is not binary, you can use TEXT type - accepts all readable
>>characters and data, BLOB type is more for binary data storage, and
>>MYSQL's Varchar type only stores up to 255 characters. (65,536 bits, or
>>64Kbits)
>>
>>If you are storing Binary data (DES-3 encrypted data, or image data, for
>>example (a silly idea, IMHO, but some people store image data in
>>databases), then you would use BLOB, but I prefer to use TEXT type for
>>plain ol' storage of text or characters (say an html page or template,
>>etc) rather than the binary BLOB type, although BLOB would be a space
>>savings if the data will be quite large, and you will have many rows)
>>
>>HTH
>>Bri!
>>
> 
> 
> Thanks - this makes sense. I will convert the table to text and see
> how I get on.
> 
> However, there is still a learning point that might be missed here -
> how does Python grab BLOB data-types, and how do you manipulate them?
> If it were a file, would you just be able to grab the file without the
> array?
> 
> Adam
> 
> --
> http://www.monkeez.org
> PGP key: 0x7111B833
> 
> !DSPAM:441be3c3320518690210016!
> 
> 


More information about the Tutor mailing list