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

Jim Vickroy Jim.Vickroy at noaa.gov
Fri Oct 5 07:49:32 MDT 2001


Hello Sue,

Have you tried posting this to the PythonWin32 list?

Information is at:

 http://mail.python.org/mailman/listinfo/python-win32


Sue Giller wrote:

> Greetings,
>
> 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.
> Thanks.
>
> 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
> it
> # Prints the data items before they are stored, and after they are
> stored
> # if they were pickled, tries to unpickle and print results
> # printed data are delimited by vertical bars | to show what is in the
> field
> # 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.AddNew()
>         daoStnRS.Fields(typefieldName).Value = blobType
>         daoStnRS.Fields(blobfieldName).Value = dataBlob
>         daoStnRS.Update()
>     else:
>         daoStnRS.Edit()
>         daoStnRS.Fields(blobfieldName).Value = dataBlob # replace
> blob
>         daoStnRS.Update()
>
> 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
>     else:
>         raise
>
> # ++++++++++++ TESTING ++++++++++
> try:
>     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`
>     try:
>         unblob = cPickle.loads(str(blob))
>         print "unpickled with str|", unblob, "|"
>     except:
>         print "Cant unpickle"
>         # ValueError: insecure string pickle
>     dtype = "Pl"         # pickled numeric list
>     blob = GetRecord(dtype)
>     print "3: pickled list|", blob, "|"
>     print `blob`
>     try:
>         unblob = cPickle.loads(str(blob))
>     except:
>         print "cant unpickle"
>         # UnpicklingError: invalid load key, '
>     try:
>         unblob = cPickle.loads(blob)
>     except:
>         print "cant unpickle"
>         #TypeError: loads() argument 1 must be string, not buffer
>     print "+++"
> except:
>     raise
>
> _______________________________________________
> This message sent by the FRPythoneers mailing list.
> Unsubscribe: echo unsubscribe | FRPythoneers-request at lists.community.tummy.com
> URL: http://lists.community.tummy.com/mailman/listinfo/frpythoneers




More information about the FRPythoneers mailing list