Compress cPickled object with zlib ==> store in DB?
"Andrea Gavana" <[email protected]>
| Newsgroups | gmane.comp.python.db.pysqlite.user |
|---|---|
| Message-ID | <[email protected]> |
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. It uses SQLAlchemy, but I believe it is a general issue for sqlite itself. 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/ _______________________________________________ pysqlite mailing list pysqlite-IAPFreCvJWPBWskQ1e/[email protected] http://lists.initd.org/mailman/listinfo/pysqlite
basic_tree_1.py
(text/plain, 5.2 KB)
"""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()