Hi Jonathan and All,
first of all, thank you for your answer.
On 3/6/07, Jonathan Ellis wrote:
> it sounds like you inserted a bunch of new objects, but didn't flush,
> so select() doesn't see them. get() does see it because it checks the
> identity map before querying the db.
Well, at the beginning this is what I thought, but I put flush()
everywhere and I still get the same result. So, I thought to modify
the byroot_tree.py demo in the examples/adjacencytree directory to
show a couple of issues I have been fighting for 2 days.
The problems you will see are:
1) No matter how/when/where I flush(), select returns always an empty
list (see the attached file when you run the simple demo);
2) Using a "physical" database (not using an in-memory database),
baffles me: in the attached file, at the bottom, just set the
variable:
newDataBase = True
The first time to create the database: this just creates few random
nodes and saves them into the database. Then, set:
newDataBase = False
To try to load the data from the database. I get an impossible error
from the TreeLoader class:
Traceback (most recent call last):
File "C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3
.5\examples\adjacencytree\byroot_tree_1.py", line 206, in <module>
main()
File "C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3
.5\examples\adjacencytree\byroot_tree_1.py", line 203, in main
engineclass = TheEngine(newDataBase)
File "C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3
.5\examples\adjacencytree\byroot_tree_1.py", line 157, in __init__
self.LoadNodes()
File "C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3
.5\examples\adjacencytree\byroot_tree_1.py", line 193, in LoadNodes
parentnode = query.get(ii)
File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 61, in get
File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 376, in _get
File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 384, in _select_sta
tement
File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 316, in execute
File "build\bdist.win32\egg\sqlalchemy\orm\query.py", line 335, in instances
File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1258, in _instance
File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1439, in append_re
sult
File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1456, in _do
File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1439, in append_re
sult
File "build\bdist.win32\egg\sqlalchemy\orm\mapper.py", line 1456, in _do
File "C:\Documents and Settings\gavana\Desktop\SQLAlchemy-0.3.5\SQLAlchemy-0.3
.5\examples\adjacencytree\byroot_tree_1.py", line 91, in append_result
parentnode = selectcontext.identity_map[mapper.identity_key(instance.parent_
id)]
KeyError: (<class '__main__.TreeNode'>, (1,), None)
I know I am doing something really stupid, but I don't know how to fix
it. I obviously need a "physical" database, not an in-memory, and I
would like to be able to load the data after I saved them. I am sorry
for my poor knowledge of SQLAlchemy, I just started.
Thank you for your suggestions.
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 [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
-~----------~----~----~----~------~----~------~--~---
"""a more advanced example of basic_tree.py. treenodes can now reference their
"root" node, and
introduces a new selection method which selects an entire tree of nodes at
once, taking
advantage of a custom MapperExtension to assemble incoming nodes into their
correct structure."""
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 hierarchical Tree class, which adds the concept of a "root node". The
root is
the topmost node in a tree, or in other words a node whose parent ID is
NULL.
All child nodes that are decendents of a particular root, as well as a root
node itself,
reference this root node.
this is useful as a way to identify all nodes in a tree as belonging to a
single
identifiable root. Any node can return its root node and therefore the
"tree" that it
belongs to, and entire trees can be selected from the database in one
query, by
identifying their common root ID."""
def __init__(self, name):
"""for data integrity, a TreeNode requires its name to be passed as a
parameter
to its constructor, so there is no chance of a TreeNode that doesnt
have a name."""
self.name = name
self.children = NodeList()
self.root = self
self.parent = None
self.id = None
self.data =None
self.parent_id = None
self.root_id=None
def _set_root(self, root):
self.root = root
for c in self.children:
c._set_root(root)
def append(self, node):
if isinstance(node, str):
node = TreeNode(node)
node.parent = self
node._set_root(self.root)
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,%s, %d): %s" % (self.name,
self.id,self.parent_id,self.root_id, id(self), repr(self.data)) + '\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 TreeLoader(MapperExtension):
"""an extension that will plug-in additional functionality to the Mapper."""
def after_insert(self, mapper, connection, instance):
"""runs after the insert of a new TreeNode row. The primary key of the
row is not determined
until the insert is complete, since most DB's use autoincrementing
columns. If this node is
the root node, we will take the new primary key and update it as the
value of the node's
"root ID" as well, since its root node is itself."""
if instance.root is instance:
connection.execute(mapper.mapped_table.update(TreeNode.c.id==instance.id,
values=dict(root_node_id=instance.id)))
instance.root_id = instance.id
def append_result(self, mapper, selectcontext, row, instance, identitykey,
result, isnew):
"""runs as results from a SELECT statement are processed, and newly
created or already-existing
instances that correspond to each row are appended to result lists.
This method will only
append root nodes to the result list, and will attach child nodes to
their appropriate parent
node as they arrive from the select results. This allows a SELECT
statement which returns
both root and child nodes in one query to return a list of "roots"."""
if instance.parent_id is None:
result.append(instance)
else:
if isnew or populate_existing:
parentnode =
selectcontext.identity_map[mapper.identity_key(instance.parent_id)]
parentnode.children.append_without_event(instance)
# fire off lazy loader before the instance is part of the session
instance.children
return False
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)
"""create the treenodes table. This is ia basic adjacency list model
table.
One additional column, "root_node_id", references a "root node" row and
is used
in the 'byroot_tree' example."""
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('root_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)
)
metadata.create_all()
# the mapper is created with properties that specify "lazy=None" - this
is because we are going
# to handle our own "eager load" of nodes based on root id
mapper(TreeNode, trees,
properties=dict(id=trees.c.node_id,name=trees.c.node_name,
parent_id=trees.c.parent_node_id,
root_id=trees.c.root_node_id,
root=relation(TreeNode,
primaryjoin=trees.c.root_node_id==trees.c.node_id,
remote_side=trees.c.node_id, lazy=None, uselist=False),
children=relation(TreeNode,
primaryjoin=trees.c.parent_node_id==trees.c.node_id,
lazy=None,
uselist=True, cascade="delete,save-update",
collection_class=NodeList),
data=relation(mapper(TreeData,
treedata, properties=dict(id=treedata.c.data_id)),
cascade="delete,delete-orphan,save-update", lazy=False)),
extension = TreeLoader())
self.session = create_session()
if newDataBase:
print "\n\nNew Database: creating random nodes...\n"
self.CreateNodes()
# AG: Please look at the next method: select returns an empty list,
but
# we *know* the node called "node3" exists!
print "New Database: checking what select() is returning:"
self.CheckIfSelectWorks()
else:
print "\n\nExisting Database: just look what happens...\n"
# AG: This method throws an impossible error when loading from an
existing
# database!
self.LoadNodes()
def CreateNodes(self):
# AG: just create some random nodes
node2 = TreeNode('node2')
node2.append('subnode1')
node = TreeNode('rootnode')
node.append('node1')
node.append(node2)
node.append('node3')
node.children['node3'].data = TreeData('node 3s data')
node.children['node2'].append('subnode2')
node.children['node1'].data = TreeData('node 1s data')
# AG: I am calling save AND flush everywhere, but that doesn't help
self.session.save(node)
self.session.flush()
def CheckIfSelectWorks(self):
# load some nodes. we do this based on "root id" which will load an
entire sub-tree in one pass.
# the MapperExtension will assemble the incoming nodes into a tree
structure.
t = self.session.query(TreeNode).select(TreeNode.c.name=="subsubnode1",
order_by=[TreeNode.c.id])
print "==> Select Returns:", t, " <== An empty list!!\n\n"
def LoadNodes(self):
query = self.session.query(TreeNode)
count = query.count()
for ii in xrange(1, count+1):
parentnode = query.get(ii)
print parentnode.name
# 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()
