Hi,
I've been playing with the Many-to-Many relationship
<http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#many-to-many>
from
the documentation. Suppose I have a student and teacher and would like to
define a bidirectional “favorite” relationship between the two, i.e. if a
student is a favorite of a teacher then the teacher is also a favorite of
that student:
favorite_association_table = Table("favorite_associations", Base.metadata,
Column("student_id", Integer, ForeignKey("students.id"),
nullable=False),
Column("teacher_id", Integer, ForeignKey("teachers.id"),
nullable=False),
)
and in the Teacher class:
favs = relationship("Student", secondary=favorite_association_table,
backref="favs")
Now I observe the following:
>>> teacher.favs
[]
>>> student.favs
[]
>>> student.favs.append(teacher)
>>> teacher.favs.append(student)
which creates two entries in the association table:
mysql> select * from favorite_associations;
+----------------------------------+----------------------------------+
| student_id | instructor_id |
+----------------------------------+----------------------------------+
| 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
| 030ced9060d2460fa30936cffd2e0a0a | 08315ae48d574bc3ac29526c675e67fc |
+----------------------------------+----------------------------------+
2 rows in set (0.00 sec)
That is probably alright, but not really desirable. Now, when I attempt to
delete either the student or the teacher from the other, I get an error:
>>> student.favs.remove(teacher)
[…]
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
'favorite_association' expected to delete 1 row(s); Only 2 were matched.
I guess I could avoid the list.remove() and build a query like:
delete from favorite_associations where student_id='...'
but that seems clumsy in the context of the ORM.
So my question is: what is the proper and recommended way of implementing
and handling such a bidirectional relationship?
Thank you!
Jens
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
