python - sqlalchemy generic foreign key (like in django ORM) -

does sqlalchemy have django's genericforeignkey? , right use generic foreign fields.

my problem is: have several models (for example, post, project, vacancy, nothing special there) , want add comments each of them. , want use 1 comment model. worth to? or should use postcomment, projectcomment etc.? pros/cons of both ways?

thanks!

the simplest pattern use have separate comment tables each relationship. may seem frightening @ first, doesn't incur additional code versus using other approach - tables created automatically, , models referred using pattern post.comment, project.comment, etc. definition of comment maintained in 1 place. approach referential point of view simple , efficient, dba friendly different kinds of comments kept in own tables can sized individually.

another pattern use single comment table, distinct association tables. pattern offers use case might want comment linked more 1 kind of object @ time (like post , project @ same time). pattern still reasonably efficient.

thirdly, there's polymorphic association table. pattern uses fixed number of tables represent collections , related class without sacrificing referential integrity. pattern tries come closest django-style "generic foreign key" while still maintaining referential integrity, though it's not simple previous 2 approaches.

imitating pattern used ror/django, there no real foreign keys used , rows matched using application logic, possible.

the first 3 patterns illustrated in modern form in sqlalchemy distribution under examples/generic_associations/.

the ror/django pattern, since gets asked often, add sqlalchemy examples, though don't much. approach i'm using not same django seem make use of "contenttypes" table keep track of types, seems kind of superfluous me, general idea of integer column points number of tables based on discriminator column present. here is:

from sqlalchemy.ext.declarative import declarative_base, declared_attr sqlalchemy import create_engine, integer, column, \                     string, and_ sqlalchemy.orm import session, relationship, foreign, remote, backref sqlalchemy import event   class base(object):     """base class provides automated table name     , surrogate primary key column.      """     @declared_attr     def __tablename__(cls):         return cls.__name__.lower()     id = column(integer, primary_key=true) base = declarative_base(cls=base)  class address(base):     """the address class.      represents address records in     single table.      """     street = column(string)     city = column(string)     zip = column(string)      discriminator = column(string)     """refers type of parent."""      parent_id = column(integer)     """refers primary key of parent.      refer table.     """      @property     def parent(self):         """provides in-python access "parent" choosing         appropriate relationship.          """         return getattr(self, "parent_%s" % self.discriminator)      def __repr__(self):         return "%s(street=%r, city=%r, zip=%r)" % \             (self.__class__.__name__, self.street,             self.city, self.zip)  class hasaddresses(object):     """hasaddresses mixin, creates relationship     address_association table each parent.      """  @event.listens_for(hasaddresses, "mapper_configured", propagate=true) def setup_listener(mapper, class_):     name = class_.__name__     discriminator = name.lower()     class_.addresses = relationship(address,                         primaryjoin=and_(                                         class_.id == foreign(remote(address.parent_id)),                                         address.discriminator == discriminator                                     ),                         backref=backref(                                 "parent_%s" % discriminator,                                 primaryjoin=remote(class_.id) == foreign(address.parent_id)                                 )                         )     @event.listens_for(class_.addresses, "append")     def append_address(target, value, initiator):         value.discriminator = discriminator  class customer(hasaddresses, base):     name = column(string)  class supplier(hasaddresses, base):     company_name = column(string)  engine = create_engine('sqlite://', echo=true) base.metadata.create_all(engine)  session = session(engine)  session.add_all([     customer(         name='customer 1',         addresses=[             address(                     street='123 anywhere street',                     city="new york",                     zip="10110"),             address(                     street='40 main street',                     city="san francisco",                     zip="95732")         ]     ),     supplier(         company_name="ace hammers",         addresses=[             address(                     street='2569 west elm',                     city="detroit",                     zip="56785")         ]     ), ])  session.commit()  customer in session.query(customer):     address in customer.addresses:         print(address)         print(address.parent)