I am using version 0.9.3.
When defining a many-to-many self-referential relationship, I found that
the primary key column has to be defined in the table itself. It cannot be
defined in the model base or a mixin class from which this entity is
derived. For example:
Given these definitions:
1 #! /usr/bin/python
2
3 from sqlalchemy.ext import declarative
4 from sqlalchemy.schema import DefaultClause, Sequence
5 import sqlalchemy as sa
6 from sqlalchemy.orm import relationship, backref, sessionmaker
7
8 class Model(object):
9 """All model classes must have table name in lower case, with
underscores representing
10 camel case
11 They must have 'id' column, populated using uuid
12 Must have a version column for optimistic locking
13 """
14 @declarative.declared_attr
15 def __tablename__(cls):
16 return cls.__name__.lower()
17
18 id = sa.Column('id', sa.Integer, Sequence('id_seq'),
primary_key=True)
19
20
21 ModelBase = declarative.declarative_base(cls=Model)
22
23
24 class VersionColumnMixin(object):
25 version = sa.Column('instance_version', sa.INTEGER,
nullable=False)
26 @declarative.declared_attr
27 def __mapper_args__(cls):
28 mapper_dict = {
29 'version_id_col': cls.version
30 }
31 return mapper_dict
32
33
34 # many-to-many between Groups and Groups
35 groups_and_groups_assoc_table = \
36 sa.Table('groups_and_groups', ModelBase.metadata,
37 sa.Column('parent_group_id', sa.Integer,
sa.ForeignKey('groups.id'),
38 primary_key=True),
39 sa.Column('child_group_id', sa.Integer,
sa.ForeignKey('groups.id'),
40 primary_key=True)
41 )
42
43
44
45 class Groups(ModelBase, VersionColumnMixin):
46 *id = sa.Column('id', sa.Integer, Sequence('id_seq'),
primary_key=True)*
47 domain_id = sa.Column(sa.String(36),
DefaultClause('default'), nullable=False)
48 description = sa.Column(sa.Text)
49 date_created = sa.Column(sa.DateTime)
50 group_name = sa.Column(sa.String(64), nullable=False,
index=True)
51 child_groups = relationship('Groups',
secondary=groups_and_groups_assoc_table,
52
primaryjoin=id==groups_and_groups_assoc_table.c.parent_group_id,
53
secondaryjoin=id==groups_and_groups_assoc_table.c.child_group_id,
54 backref='parent_groups')
55 __table_args__ = (sa.UniqueConstraint('domain_id',
'group_name'),)
56
57
58 if __name__ == '__main__':
59
60 engine =
sa.create_engine('postgresql://admin@localhost:5435/testdb', echo="debug")
61
62 print 'Creating database tables'
63 ModelBase.metadata.create_all(engine)
64
65 Session = sessionmaker(bind=engine)
66 sess = Session()
67
68 g1 = Groups(description='1234', group_name='group1')
69
70 c1_g1 = Groups(description='c1_1234', group_name='c1_group1')
71 c2_g1 = Groups(description='c2_1234', group_name='c2_group1')
72 g1.child_groups = [c1_g1, c2_g1]
73
74 sess.add(g1)
75 sess.commit()
If the 'id' column definition (highlighted in *bold red italics* above at
line 46 is commented out), but inherited via the ModelBase class, I get
this error when running the test code:
Traceback (most recent call last):
File "./selfref4.py", line 68, in <module>
g1 = Groups(description='1234', group_name='group1')
File "<string>", line 2, in __init__
File
"/usr/lib64/python2.6/site-packages/sqlalchemy/orm/instrumentation.py",
line 322, in _new_state_if_none
state = self._state_constructor(instance, self)
...
...
File
"/usr/lib64/python2.6/site-packages/sqlalchemy/orm/relationships.py", line
2392, in _check_foreign_cols
raise sa_exc.ArgumentError(err)
sqlalchemy.exc.ArgumentError: Could not locate any simple equality
expressions involving locally mapped foreign key columns for primary join
condition 'group_and_group.parent_group_id = :parent_group_id_1' on
relationship Groups.child_groups. Ensure that referencing columns are
associated with a ForeignKey or ForeignKeyConstraint, or are annotated in
the join condition with the foreign() annotation. To allow comparison
operators other than '==', the relationship can be marked as viewonly=True.
However, the many-to-many relationship works when the 'id' column is
inherited from the model base as long as the relationship is between two
different classes.
I do not like to define the 'id' column in every model class, instead they
all should get their definitions from the model base.
What could be wrong in the definitions above?
Any help is appreciated.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
