MySQL blobs confusion

Kirby Urner urner at alumni.princeton.edu
Fri Nov 5 00:03:56 CET 2004


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




More information about the Python-list mailing list