On 6/30/15 3:54 PM, Viktor Roytman wrote:
I have a three classes that I am using to represent a tree:
Tree has some information about the tree at large and a list of nodes.
Node has some information specific to a node, a list of subtrees, and
needs a particular piece of information about the Tree for a constraint.
SubTree has a list of nodes.
Here is some (working) example code for this setup:
|
fromsqlalchemy import(
Column,Integer,UniqueConstraint,ForeignKeyConstraint,
create_engine
)
fromsqlalchemy.ext.declarative importdeclarative_base
fromsqlalchemy.orm importsessionmaker,relationship
Base=declarative_base()
classTree(Base):
__tablename__ ='tree'
id =Column(Integer,primary_key=True)
important_value =Column(Integer,nullable=False)
nodes =relationship('Node')
classSubTree(Base):
__tablename__ ='sub_tree'
id =Column(Integer,primary_key=True)
parent_node_id =Column(Integer,nullable=False)
important_value =Column(Integer,nullable=False)
nodes =relationship(
'Node',
foreign_keys='[Node.parent_sub_tree_id, Node.important_value]',
)
__table_args__ =(
UniqueConstraint('id','important_value'),
ForeignKeyConstraint(
['parent_node_id','important_value'],
['node.id','node.important_value']
),
)
classNode(Base):
__tablename__ ='node'
id =Column(Integer,primary_key=True)
parent_tree_id =Column(Integer)
important_value =Column(Integer,nullable=False)
parent_sub_tree_id =Column(Integer)
sub_trees =relationship(
'SubTree',
foreign_keys=(
'[SubTree.parent_node_id, SubTree.important_value]'
),
)
__table_args__ =(
UniqueConstraint('id','important_value'),
ForeignKeyConstraint(
['parent_tree_id','important_value'],
['tree.id','tree.important_value']
),
ForeignKeyConstraint(
['parent_sub_tree_id','important_value'],
['sub_tree.id','sub_tree.important_value']
),
)
engine =create_engine('...')
Base.metadata.create_all(engine)
session =sessionmaker(bind=engine,autocommit=True)()
withsession.begin():
session.add(
Tree(
important_value=3,
nodes=[
Node(
sub_trees=[
SubTree(
nodes=[
Node(),
],
),
],
),
],
)
)
|
When I run this example, I get this warning:
|
/.../lib/python3.4/site-packages/sqlalchemy/orm/relationships.py:2667:SAWarning:relationship
'Tree.nodes'will copy column tree.important_value to column
node.important_value,which conflicts
withrelationship(s):'SubTree.nodes'(copies sub_tree.important_value to
node.important_value).Considerapplying viewonly=Trueto read-only
relationships,orprovide a primaryjoin condition marking writable
columns withthe foreign()annotation.
for(pr,fr_)inother_props)
|
I can't figure out how to modify the relationships to make SQLAlchemy
happy with my (admittedly convoluted) scheme.
so the warning is expressing this contradiction:
If I have a Node, and I associate some SubTree with that Node's
"sub_trees" collection, foreign key here means that the value of
Node.important_value will be copied into SubTree.important_value at
flush time. It has to; because you've declared that all
SubTree.important_value columns must refer to a value that is already
present in Node.important_value.
The next sentence I will write by literally reversing the words:
If I have a SubTree, and I associate some Node with that SubTree's
"nodes" collection, foreign key here means that the value of
SubTree.important_value will be copied into Node.important_value at
flush time. It has to; because you've declared that all
Node.important_value columns must refer to a value that is already
present in SubTree.important_value.
I've run your test and it seems that the approach is to avoid the issue
by following the pattern that a Node of a SubTree will have the same
important_value as the Node to which the SubTree is a child.
Here's how I can hit your contradiction:
n1 = Node(thingy='x')
n2 = Node(thingy='y')
t1 = Tree(important_value=3)
t2 = Tree(important_value=4)
s1 = SubTree()
t1.nodes.append(n1)
t2.nodes.append(n2)
n1.sub_trees.append(s1)
s1.nodes.append(n2)
session.add_all([
n1, n2, t1, t2, s1
])
and the error (note I've given the names x, y, z to the constraints to
get them to be droppable):
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or
update on table "node" violates foreign key constraint "y"
DETAIL: Key (parent_tree_id, important_value)=(2, 3) is not present in
table "tree".
So...the warning is basically saying, that's a thing. The object model
allows a contradiction that isn't allowed in the relational model.
The INSERTs still proceed provided you've assembled the objects
appropriately, so you could just try living with the warning. I guess
ideally, if we could make separate Node classes to indicate each intent,
e.g. Tree.nodes only get a TreeNode, SubTree.nodes only get a
SubTreenode, that would be a way of avoiding the above contradiction,
though I tried that and at the moment the warning we see is only
thinking about the tables, not the classes, so we still get the
warning. Even if I make a completely separate SubTreeNode class that
isn't even a subclass. To trick it into not warning like that would
require more gymnastics than is appropriate, really. Usually what we
do for these "the user is almost certainly doing it wrong" cases when
there's a real use is that we add a flag to turn off the warning. But
then all those confused users who are doing it actually wrong then flip
on that flag to try to fix their problem. I see endless mappings with
incorrect use of "single_parent=True" in this regard.
The best I can come up with at the moment is to take out
Node.important_value from the foreign keys list of SubTree.nodes, and
copy that "important_value" yourself. This is *somewhat* appropriate at
least, because "Node.important_value" really has to match up to that
Tree.important_value in any case, so really it's from
Tree.important_value -> <everything below it>.
class SubTree(Base):
__tablename__ = 'sub_tree'
id = Column(Integer, primary_key=True)
parent_node_id = Column(Integer, nullable=False)
important_value = Column(Integer, nullable=False)
nodes = relationship('Node', foreign_keys='[Node.parent_sub_tree_id]')
__table_args__ = (
UniqueConstraint('id', 'important_value'),
ForeignKeyConstraint(
['parent_node_id', 'important_value'],
['node.id', 'node.important_value'],
name='x'
),
)
session.add(
Tree(
important_value=3,
nodes=[
Node(
sub_trees=[
SubTree(
nodes=[
Node(important_value=3),
],
),
],
),
],
)
)
Any ideas?
--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
