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/ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
"""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()