Database file size issues
"Andrea Gavana" <[email protected]>
| Newsgroups | gmane.comp.python.db.pysqlite.user |
|---|---|
| Message-ID | <[email protected]> |
Hi All,
I am a user of SQLAlchemy, from which I access the sqlite
database. I don't know if this is the right forum to post, but I only
use Python so I thought that maybe someone of you may know the answer.
I know next to nothing about databases, so please forgive my
newbieness. I attach a simple demo, which unfortunately uses
SQLAlchemy (with sqlite as database). In this small demo, at the end,
setting the variable newDataBase=True will create a new database of
about 120 Kb by adding some data to it. Then, re-running the script
and setting newDataBase=False, the demo loads the database, deletes
*everything* from the database, and then exits.
Well, curiously enough, the size of the database is still 120 Kb, even
if I deleted everything.
The main problem, is that the real database I am using increases in
size *forever*, no matter what I do or what I delete. I can add 1000
items to my real database, growing it to 10 MB, then delete everything
and still have an empty database of 10 MB. There is no reduction in
the database size, never. That can be an issue if after 2 months I
have a database file of 4 GB with nothing inside.
I have written to the SQLAlchemy maintainer, and he said it is an
issue only with sqlite, not with other databases.
Is there anyone that can actually try my small demo and maybe suggest
what I should do?
Thank you for your time and for your suggestions.
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.1 KB)
"""a basic Adjacency List model tree."""
import os
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')
node2.setdata(TreeData("Hello "*10000))
node2.append('subnode1')
node = TreeNode('rootnode')
node.setdata(TreeData("World "*10000))
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()
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()