SQLAlchemy Generic Relationship simple example

Question

I know similar questions have been asked, however I am really struggling to understand how generic fields are implemented in SQLAlchemy.

I have a Permissions class/table which I want to contain a field which can relate to any model type.

I have looked at the examples and this blog post http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/

Is it possible to have a generic relation without a separate table? Just by storing the object_type and id? Something along these lines:

class Permission(AbstractBase):
    user = relationship("User", backref=backref('permissions'))
    permission_type = column(String())
    object = #The object the permission applies to, could be any type.

I guess just a really simple example would be appreciated!

Also, it's worth noting I am coming from a Django background!

Thanks

What do you mean "without another table"? Can you better define what your SQL schema looks like? — Mark Hildreth, Jul 16 '13 at 22:52
Sorry I should have made myself clearer. By without another table I meant having a separate table for storing the generic relation. — Charlie, Jul 16 '13 at 23:01
that's an old blog post with older patterns in it, but the first example, "how rails does it", is also the django approach - it foregoes the usage of traditional foreign keys in order to get around needing an extra table. but why so important to build your schema incorrectly just to save a table ? — zzzeek, Jul 20 '13 at 01:02

score 11 · Answer 1 · answered Sep 25 '16 at 08:09

You can also use generic_relationship from the sqlalchemy-utils package.

Here's an example for their documentation;

from sqlalchemy_utils import generic_relationship

class User(Base):
    __tablename__ = 'user'
    id = sa.Column(sa.Integer, primary_key=True)

class Customer(Base):
    __tablename__ = 'customer'
    id = sa.Column(sa.Integer, primary_key=True)

class Event(Base):
    __tablename__ = 'event'
    id = sa.Column(sa.Integer, primary_key=True)
    object_type = sa.Column(sa.Unicode(255))
    object_id = sa.Column(sa.Integer)
    object = generic_relationship(object_type, object_id)

score 4 · Accepted Answer · edited May 23 '17 at 12:34

4

Someone else asked the identical question about a day later, I pointed to the three examples we have for this kind of thing but also I wrote a new example that will do (mostly) the same thing Django does (minus that weird "contenttypes" table): sqlalchemy generic foreign key (like in django ORM)

edited May 23 '17 at 12:34

Community

1
1

answered Jul 20 '13 at 02:19

zzzeek

72,307
23
193
185

SQLAlchemy Generic Relationship simple example

2 Answers2

Linked