I am working on a simple note taking application. There are Items which are
just short pieces of text with a creation date. And there are Connections
that link two Items together.
The twist is that Connections are themselves Items, so, they have a text
and creation date and can be connected with each other as well as with
plain Items. This functionality is modelled by defining the class
Connection as a subclass of Item.
Connection has (among other things) two attributes of type Item: left and
right. Unfortunately, it turns out that mapping these classes on the
database is too tricky for me. I hope that someone can help me with this.
I am using sqlalchemy version 1.3.1 and an SQLight back end, version 3.26.0.
First, I tried Joined Table Inheritance:
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
type = Column(String(20))
creation_date = Column(Date, nullable=False)
contents = Column(String(500), nullable=False)
__mapper_args__ = {
'polymorphic_identity':'items',
'polymorphic_on':type
}
class Connection(Item):
__tablename__ = 'connections'
id = Column(Integer, ForeignKey('items.id'), primary_key=True)
left_id = Column(Integer, ForeignKey('items.id'))
left = relationship('Item', foreign_keys=[left_id])
right_id = Column(Integer, ForeignKey('items.id'))
right = relationship('Item', foreign_keys=[right_id])
__mapper_args__ = {'polymorphic_identity':'connections'}
There are multiple foreign keys from Connection to Item. I resolved the
conflicts for left and right by specifying foreign_keys in the relationship
but there is no way to do that for the id which is used for the class
hierarchy join. Running this code yields the error:
Can't determine join between 'items' and 'connections'; tables have more
than one foreign key constraint relationship between them. Please specify
the 'onclause' of this join explicitly.
I have no idea how to do that.
I also tried Single Table Inheritance, to avoid the hierarchy foreign key:
class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
type = Column(String(20))
creation_date = Column(Date, nullable=False)
contents = Column(String(500), nullable=False)
__mapper_args__ = {
'polymorphic_identity':'items',
'polymorphic_on':type
}
class Connection(Item):
left_id = Column(Integer, ForeignKey('items.id'))
left = relationship('Item', foreign_keys=[left_id])
right_id = Column(Integer, ForeignKey('items.id'))
right = relationship('Item', foreign_keys=[right_id])
__mapper_args__ = {'polymorphic_identity':'connections'}
Alas, this also results in an error:
Incompatible collection type: Item is not list-like.
I do not understand this at all, as there are no back_populates from
Connection to Item, so why should it be list like?
Please, help me!
Thank you very much,
Huub
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
