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 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 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()