[FRPythoneers] Pickling into BLOB field in Windows into Access MDB

Sue Giller sag at hydrosphere.com
Thu Oct 4 15:09:18 MDT 2001


I got this address from Mark Lutz who suggested that the community 
might be able to help me with the following problem.  If I have 
breeched local etiquette by just posting this message out of the blue, 
I apologize - just inform me of the proper channels and I will use 
them.  I have posted the following questions to the python.org help 
desk, but have heard nothing back, and I am stumped.

I am somewhat of a python newbie, but a C/VB/Access oldie.  I 
don't know if my question is simply stupidity on my part in using 
python, or just a lack of documentation on how to handle the 
following.  I am using Python 2.1 for windows,  win32all build 140, 
PythonWin interface on Windows 2000, service pack 1.  

I am trying to use python to pickle numeric data in a python array 
and store that data in an Access (97) database OLE object field.  I 
have been doing this for some time using VB6, but I want to use 
python if possible so I can store the data as an object.

I can successfully cPickle and retrieve the data object as binary to a 
file.  To do the same into an OLE field, I assume I must use the 
dumps and loads functions.  As a test, I tried this with both a simple 
list and a simple text string.  Both times I get the same results.  The 
object is properly placed in the field, I can retrieve the object, but I 
can't unpickle it or print it if it's a string.  (see test code at the end of 
this email) 

The object returned from the OLE field seems to be a buffer type.  

<read-write buffer ptr 0167B3B4, size 44 at 0167B398>

If the original value placed in the OLE field was a simple text string, 
and I try to print that returned item, I get only the first character of 
the string, no matter what I try (back tics, str(), print, etc).

If the value was pickled and then stored, and I try to unpickle it after 
retrieval, cPickle complains that the item isn't a string.

Obviously, the contents of the field are retrieved as a buffer object, 
but cPickle.loads doesn't accept such an object.  I can find no way 
to convert this buffer thing into something that loads will accept.  The 
documentation on python.org is too high level for me to make any 
sense out of when it discusses buffers.  I know they point to raw 
memory, but I don't want to have to parse out the bits of the 
memory, since that means I must have knowledge of the memory 
layout (that is what I have to do now in VB and it's a pain) - it would 
be nice if the pickling process did this for me!  

So, my questions are:

1) is there a way to get cPickle to accept the buffer item returned 
out of the dataset via the recordset emulation offered in win32com?

2) is there a good discussion of saving non-text items into databases 
(access or some other database) via pickling/unpickling python 
objects/data?  I know that Zope does this into their own database, 
but I need a more conventional relational database.  

3) I would like to change to using a more generic ODBC module 
(mxODBC), but I don't want to use DSN names, but rather the path 
to the database files.  All the examples I've found use a DSN.  In VB, 
you would use a connection string such as 

myConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
&  FilePath  

Any assistance you can offer would be most gratefully appreciated.  

sue giller
hydrosphere data products
boulder, co

---- CODE   -----

# simple.py
# demonstrates problems with using OLE field in Access 97 database
# tests three cases that store data into an OLE field and retrieve it
#   1: using simple text string
#   2: using pickled text string
#   3: using pickled numeric list
#   cannot test unpickled numeric array since OLE field won't accept 
# Prints the data items before they are stored, and after they are 
# if they were pickled, tries to unpickle and print results
# printed data are delimited by vertical bars | to show what is in the 
# Results after retrieving:
#   1: prints the first character only, with or without str()
#   2: gets exception - ValueError: insecure string pickle
#             when try to unpickle
#   3: gets exception - UnpicklingError: invalid load key, '
#             when try to unpickle with str()
#   3: gets exception - loads() argument 1 must be string, not buffer
#             when try to unpickle without str()

# To use, create a simple mdb database (Access 97) with one table
# in the table, create one text field at least 2 chars long, and one 
OLE Object field
# edit the variables below to match your mdb, table and field names
import cPickle
import win32com.client
# edit as needed to match your mdb
basePath = "c:\\python21\\scripts\\"
mdbName = basePath + "simple.mdb"
tableName = "basic"
blobfieldName = "DataBlob"
typefieldName = "BlobType"
# test data
textData = "Long text for the blob field"
listData = [1,2,3,4]

def AddRecord(blobType, dataBlob):
    select = "Select * from " + tableName
    where = " where " + typefieldName + " = '" + blobType + "'"    
    daoStnRS = daoDB.OpenRecordset(select + where)
    if daoStnRS.RecordCount == 0:
        daoStnRS.Fields(typefieldName).Value = blobType
        daoStnRS.Fields(blobfieldName).Value = dataBlob
        daoStnRS.Fields(blobfieldName).Value = dataBlob # replace 

def GetRecord(blobtype):
    select = "Select * from " + tableName
    where = " where " + typefieldName + " = '" + blobtype + "'"
    daoStnRS = daoDB.OpenRecordset(select + where)
    if daoStnRS.RecordCount <> 0:
        data = daoStnRS.Fields(blobfieldName).Value       
        return data

# ++++++++++++ TESTING ++++++++++    
    daoengine = win32com.client.Dispatch('DAO.DBEngine.35')
    daoDB = daoengine.OpenDatabase(mdbName)
    # make three types of ole data
    dtype = "st"           # simple text
    blob = textData
    print "1: simple text blob|", blob, "|"
    AddRecord(dtype, blob)
    dtype = "Pt"         # pickled text
    blob = cPickle.dumps(textData)
    print "2: pickled text blob|", blob, "|"
    AddRecord(dtype, blob)
    #type = "sl"         # simple numeric list - cant do this
    dtype = "Pl"         # pickled numeric list
    blob = cPickle.dumps(listData)
    print "3: pickled list blob|", blob, "|"
    AddRecord(dtype, blob)
    # now retrieve each item and look at it
    print "Retrieving blobs"
    dtype = "st"           # simple text - don't need to unpickle
    blob = GetRecord(dtype)    
    print "1: simple text|", blob, "|"      # note doesn't print second |
    print `blob`
    print "with str|", str(blob), "|"       # note doesn't print second |
    dtype = "Pt"         # pickled text    
    blob = GetRecord(dtype)
    print "2: pickled text|", blob, "|"
    print `blob`
        unblob = cPickle.loads(str(blob))
        print "unpickled with str|", unblob, "|"
        print "Cant unpickle"
        # ValueError: insecure string pickle
    dtype = "Pl"         # pickled numeric list    
    blob = GetRecord(dtype)
    print "3: pickled list|", blob, "|"
    print `blob`
        unblob = cPickle.loads(str(blob))
        print "cant unpickle"
        # UnpicklingError: invalid load key, '
        unblob = cPickle.loads(blob)
        print "cant unpickle"
        #TypeError: loads() argument 1 must be string, not buffer
    print "+++"

More information about the FRPythoneers mailing list