MySQL blobs confusion

[Kirby Urner]

I've been testing the Cookbook example 8.6 (2002 edition) re using cPickle to
insert and retrieve BLOBs from mySQL, using Python's MySQLdb module.

When I try to cPickle.loads(blob), I get an error telling me that loads wants a
string, not type array.array. So I go cPickle.loads(blob.tostring()) instead
and it works.

My question is: has something changed in the Python API since this example was
written?

Here's my version of the code (change commented):

import MySQLdb, cPickle

def cookbook():
connection = MySQLdb.connect('','root','xxxx','test')
cursor = connection.cursor()

cursor.execute("CREATE TABLE justatest (name TEXT, ablob BLOB)")

try:
# Prepare some BLOBs to insert in the table
names = 'aramis', 'athos', 'porthos'
data = {}
for name in names:
datum = list(name)
datum.sort()
data[name] = cPickle.dumps(datum, 1)

# Perform the insertions
sql = "INSERT INTO justatest VALUES (%s, %s)"
for name in names:
cursor.execute(sql, (name, MySQLdb.escape_string(data[name])) )

# Recover the data so you can check back
sql = "SELECT name, ablob FROM justatest ORDER BY name"
cursor.execute(sql)
for name, blob in cursor.fetchall():
print name, cPickle.loads(data[name]), \
cPickle.loads(blob.tostring()) # note conversion

finally:
# Done, Remove the table and close the connection
cursor.execute("DROP TABLE justatest")
connection.close()

if __name__ == '__main__':
cookbook()

Kirby

 

[deelan]

I've been testing the Cookbook example 8.6 (2002 edition) re using cPickle to
insert and retrieve BLOBs from mySQL, using Python's MySQLdb module.

When I try to cPickle.loads(blob), I get an error telling me that loads wants a
string, not type array.array. So I go cPickle.loads(blob.tostring()) instead
and it works.

see bug:
<http://sourceforge.net/tracker/index.php?func=detail&aid=975831&group_id=22307&atid=374932>

''Cursor returns array instead of string for blob column type
The standard cursor class ('Cursor') used to return
MySQL blob row types as Python string values. In
version 1.0 it returns arrays containing a single item
with the string 'c' as key and the blob string as the
value.''


BTW i'm using this code:

pickle.loads(row['meta'])

to unpickle a blob field (in the 'meta' column)
from a mysql4's MyISAM table and it works as-is.

i'm using both mysqldb 1.0.0 (dev box) and
1.1.6 (on the server).

but i'm using DictionaryCursor instead of classic
return-as-tuples Cursor.

cheers,
deelan.