I should note that the relationship you are actually trying to set up
includes a circular relationship between two individual rows. by
default this is impossible to accomplish with only INSERT statements
and not violate foreign key constraints. to break this circular
relation you have to also add a special flag "post_update=True" to
one side of the circle, which will add an extra UPDATE statement to
the commit.
n_mapper = mapper(Node, node_table, properties={
'parent': relation(
Node,
lazy=True,
primaryjoin=node_table.c.parent_id==node_table.c.id,
foreignkey=node_table.c.id,
uselist=False
),
'children': relation(
Node,
primaryjoin=node_table.c.id==node_table.c.parent_id,
lazy=True,
cascade="all"
),
'previous_sibling': relation(
Node,
primaryjoin=node_table.c.prev_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False
),
'next_sibling': relation(
Node,
primaryjoin=node_table.c.next_sibling_id==node_table.c.id,
foreignkey=node_table.c.id,
lazy=True,
uselist=False,
post_update=True,
)
})
produces the statements:
[2006-09-05 14:11:47,504] [engine]: BEGIN
[2006-09-05 14:11:47,510] [engine]: INSERT INTO node (path,
parent_id, prev_sibling_id, next_sibling_id) VALUES (?, ?, ?, ?)
[2006-09-05 14:11:47,511] [engine]: ['root', None, None, None]
[2006-09-05 14:11:47,516] [engine]: INSERT INTO node (path,
parent_id, prev_sibling_id, next_sibling_id) VALUES (?, ?, ?, ?)
[2006-09-05 14:11:47,516] [engine]: ['first', 1, None, None]
[2006-09-05 14:11:47,521] [engine]: INSERT INTO node (path,
parent_id, prev_sibling_id, next_sibling_id) VALUES (?, ?, ?, ?)
[2006-09-05 14:11:47,522] [engine]: ['second', 1, 2, None]
[2006-09-05 14:11:47,527] [engine]: UPDATE node SET path=? WHERE
node.id = ?
[2006-09-05 14:11:47,527] [engine]: ['root', 1]
[2006-09-05 14:11:47,531] [engine]: UPDATE node SET path=?,
parent_id=?, next_sibling_id=? WHERE node.id = ?
[2006-09-05 14:11:47,532] [engine]: ['first', 1, 3, 2]
[2006-09-05 14:11:47,536] [engine]: UPDATE node SET path=?,
parent_id=?, prev_sibling_id=? WHERE node.id = ?
[2006-09-05 14:11:47,537] [engine]: ['second', 1, 2, 3]
[2006-09-05 14:11:47,539] [engine]: COMMIT
On Sep 5, 2006, at 10:12 AM, Nick Joyce wrote:
> I am attempting to extend the self referential example in the docs
> http://www.sqlalchemy.org/docs/index.myt?
> paged=no#advdatamapping_circular
> by adding properties 'prev_sibling' and 'next_sibling'. (See attached
> script)
>
> When run, the following exception is raised:
>
> Traceback (most recent call last):
> File "tree.py", line 79, in ?
> session.flush()
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/session.py", line
> 234,
> in flush
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
> 197, in flush
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
> 359, in execute
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
> 407, in _sort_dependencies
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
> 395, in sort_hier
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/unitofwork.py", line
> 800, in _sort_circular_dependencies
> File "build/bdist.linux-i686/egg/sqlalchemy/orm/topological.py",
> line
> 139, in sort
> sqlalchemy.exceptions.FlushError: Circular dependency detected
> {<Node(path=first)> (idself=1079711436): {<Node(path=second)>
> (idself=1079711468): True, <Node(path=root)> (idself=1079711404):
> True}, <Node(path=root)> (idself=1079711404): {<Node(path=second)>
> (idself=1079711468): True, <Node(path=first)> (idself=1079711436):
> True}, <Node(path=second)> (idself=1079711468): {<Node(path=first)>
> (idself=1079711436): True, <Node(path=root)> (idself=1079711404):
> True}}[]
>
> I am doing something wrong? If so can someone point me in the right
> direction ...
>
> Thanks,
>
> Nick
> import sqlalchemy.mods.threadlocal
> from sqlalchemy import *
>
> engine = create_engine('sqlite:///:memory:', echo=True);
> metadata = BoundMetaData(engine)
>
> node_table = Table('node', metadata,
> Column('id', Integer, primary_key=True),
> Column('path', String(50), nullable=False),
> Column('parent_id', Integer, ForeignKey('node.id'),
> nullable=True),
> Column('prev_sibling_id', Integer, ForeignKey('node.id'),
> nullable=True),
> Column('next_sibling_id', Integer, ForeignKey('node.id'),
> nullable=True)
> )
>
> class Node(object):
> def __init__(self, path=''):
> self.path = path
>
> def has_child_nodes(self):
> return len(self.children) == 0
>
> def append_child(self, child):
> try:
> lc = self.children[len(self.children) - 1]
> except IndexError, e:
> lc = None
>
> self.children.append(child)
>
> child.parent = self
>
> if lc is not None:
> lc.next_sibling = child
> child.previous_sibling = lc
>
> def __repr__(self):
> return '<Node(path=%s)>' % self.path
>
> n_mapper = mapper(Node, node_table, properties={
> 'parent': relation(
> Node,
> lazy=True,
> primaryjoin=node_table.c.parent_id==node_table.c.id,
> uselist=False
> ),
> 'children': relation(
> Node,
> primaryjoin=node_table.c.id==node_table.c.parent_id,
> lazy=True,
> cascade="all, delete-orphan"
> ),
> 'previous_sibling': relation(
> Node,
> primaryjoin=node_table.c.prev_sibling_id==node_table.c.id,
> lazy=True,
> uselist=False
> ),
> 'next_sibling': relation(
> Node,
> primaryjoin=node_table.c.next_sibling_id==node_table.c.id,
> lazy=True,
> uselist=False
> )
> })
>
> if __name__ == '__main__':
> node_table.create()
>
> root = Node('root')
> first = Node('first')
> second = Node('second')
>
> root.append_child(first)
> root.append_child(second)
>
> print first==second.previous_sibling
>
> session = objectstore.session
>
> session.flush()
> ----------------------------------------------------------------------
> ---
> Using Tomcat but need to do more? Need to support web services,
> security?
> Get stuff done quickly with pre-integrated technology to make your
> job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache
> Geronimo
> http://sel.as-us.falkag.net/sel?
> cmd=lnk&kid=120709&bid=263057&dat=121642______________________________
> _________________
> Sqlalchemy-users mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
