why not copy the source of PickleType and add the zlib steps to its pickling logic ? then it would just be transparent.
On Mar 8, 8:12 pm, "Andrea Gavana" <[EMAIL PROTECTED]> wrote: > Hi All, > > I was wondering if it is possible to use zlib.compress() to > compress a cPickled object and store it into a database, and then > retrieve it using zlib.decompress(). I have tried for a while, but I > keep getting errors like: > > <snip> > > File "build\bdist.win32\egg\sqlalchemy\engine\base.py", line 369, in > _execute > sqlalchemy.exceptions.SQLError: (OperationalError) Could not decode to UTF-8 > col > ' 'SELECT treenodes.node_name AS treenodes_node_name, > treenodes.parent_node_id A > S treenodes_parent_node_id, treenodes.node_id AS treenodes_node_id, > treedata_f69 > f.data_id AS treedata_f69f_data_id, treedata_f69f.value AS > treedata_f69f_value, > treenodes.data_ident AS treenodes_data_ident \nFROM treenodes LEFT OUTER JOIN > tr > eedata AS treedata_f69f ON treedata_f69f.data_id = treenodes.data_ident > \nWHERE > treenodes.node_name = ? ORDER BY treenodes.oid, treedata_f69f.oid' > ['rootnode'] > > I assume something like that is not possible, although it would be > handy to reduce the size of stored objects in a database... I attach a > small demo if someone is willing to try it. As usual, just change the > variable newDataBase=True for the first run (no errors, but database > looks empty, the size is only 3 Kb), and then change to > newDataBase=False. > > Any thought? > > Andrea. > > "Imagination Is The Only Weapon In The War Against > Reality."http://xoomer.virgilio.it/infinity77/ > > [basic_tree_1.py]"""a basic Adjacency List model tree.""" > > import os > import cPickle > import zlib > > from sqlalchemy import * > from sqlalchemy.util import OrderedDict > > class TreeData(object): > def __init__(self, value=None): > self.id = None > self.value = value > def __repr__(self): > return "TreeData(%s, %s)" % (repr(self.id), repr(self.value)) > > class NodeList(OrderedDict): > """subclasses OrderedDict to allow usage as a list-based property.""" > def append(self, node): > self[node.name] = node > def __iter__(self): > return iter(self.values()) > > class TreeNode(object): > """a rich Tree class which includes path-based operations""" > def __init__(self, name): > self.children = NodeList() > self.name = name > self.parent = None > self.id = None > self.parent_id = None > self.value = None > > def setdata(self, data): > self.data = data > > def getdata(self): > return self.data > > def append(self, node): > if isinstance(node, str): > node = TreeNode(node) > node.parent = self > self.children.append(node) > > def __repr__(self): > > return self._getstring(0, False) > def __str__(self): > return self._getstring(0, False) > > def _getstring(self, level, expand = False): > s = (' ' * level) + "%s (%s,%s, %d)" % (self.name, > self.id,self.parent_id,id(self)) + '\n' > if expand: > s += ''.join([n._getstring(level+1, True) for n in > self.children.values()]) > return s > > def print_nodes(self): > return self._getstring(0, True) > > class TheEngine(object): > > def __init__(self, newDataBase=True): > > if newDataBase and os.path.isfile("tutorial_modified.db"): > os.remove("tutorial_modified.db") > > self.engine = create_engine('sqlite:///tutorial_modified.db', > echo=False) > metadata = BoundMetaData(self.engine) > > trees = Table('treenodes', metadata, > Column('node_id', Integer, > Sequence('treenode_id_seq',optional=False), primary_key=True), > Column('parent_node_id', Integer, > ForeignKey('treenodes.node_id'), nullable=True), > Column('node_name', String(50), nullable=False), > Column('data_ident', Integer, ForeignKey('treedata.data_id')) > ) > > treedata = Table("treedata", metadata, > Column('data_id', Integer, primary_key=True), > Column('value', String(100), nullable=False) > ) > > mapper(TreeNode, trees, properties=dict(id=trees.c.node_id, > name=trees.c.node_name, > > parent_id=trees.c.parent_node_id, > children=relation(TreeNode, > cascade="all", backref=backref("parent", remote_side=[trees.c.node_id]), > collection_class=NodeList), > > data=relation(mapper(TreeData, treedata, > properties=dict(id=treedata.c.data_id)), > cascade="delete,delete-orphan,save-update", lazy=False)) > ) > > metadata.create_all() > self.session = create_session() > > if newDataBase: > self.CreateNodes() > else: > self.LoadNodes() > > def CreateNodes(self): > > node2 = TreeNode('node2') > thedata = zlib.compress(cPickle.dumps("Hello "*10000)) > node2.setdata(TreeData(thedata)) > > node2.append('subnode1') > > node = TreeNode('rootnode') > thedata = zlib.compress(cPickle.dumps("World "*10000)) > node.setdata(TreeData(thedata)) > > node.append('node1') > node.append(node2) > node.append('node3') > node.children['node2'].append('subnode2') > > print "\n\n\n----------------------------" > print "Created new tree structure:" > print "----------------------------" > print node.print_nodes() > > self.session.save(node) > self.session.flush() > > print "\n\n\n----------------------------" > print "DATABASE SIZE: ", os.stat("tutorial_modified.db")[6]/1000.0, > "Kb" > print "----------------------------" > > def LoadNodes(self): > > self.session.clear() > t = > self.session.query(TreeNode).select(TreeNode.c.name=="rootnode")[0] > > print "\n\n\n----------------------------" > print "Check the previous tree structure:" > print "----------------------------" > print t.print_nodes() > > print "\n\n----------------------------" > print "Deleting all the nodes from DB" > print "----------------------------" > > self.session.delete(t) > del t > self.session.flush() > self.engine.execute("VACUUM") > > print "\n\n----------------------------" > print "DATABASE SIZE: ", os.stat("tutorial_modified.db")[6]/1000.0, > "Kb" > print "----------------------------" > > # AG: Just modify this variable, the first time to create a new > # database use True, then use False to see what happens by > # loading the existing database > newDataBase = True > > def main(): > engineclass = TheEngine(newDataBase) > > if __name__ == "__main__": > main() --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
