By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,583 Members | 1,084 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,583 IT Pros & Developers. It's quick & easy.

MySQL blobs confusion

P: n/a

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

Jul 18 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Kirby Urner wrote:
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&at id=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.

--
@prefix foaf: <http://xmlns.com/foaf/0.1/> .
<#me> a foaf:Person ; foaf:nick "deelan" ;
foaf:weblog <http://www.netspyke.com/> .
Jul 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.