[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