SqlAlchemy linking tables

Question

So with SQLAlchemy i want to create 3 tables

Obj
obj_id | obj_type

User
user_id | obj_id | name | mail 

Society
society_id | obj_id | name 

so lets say i push 3 elements and i want to have something like this :

Obj
obj_id | obj_type
1      | society
2      | user
3      | society

User
user_id | obj_id | name | mail 
1       | 2      | John | john.smith@700.com

Society
society_id | obj_id | name
1          | 1      | Google
2          | 3      | Facebook

how do i construct my code and add / commit so i link my tables?

class Obj(Base):
    __tablename__ = 'objects'
    obj_id = Column(Integer, primary_key=True)
    obj_type = Column(String(30))

class User(Base):
    __tablename__ = 'users'
    user_id = Column(Integer, primary_key=True)
    obj_id = Column(Integer, ForeignKey('objects.obj_id'))
    name = Column(String(100), default=None)
    mail = Column(String(200), default=None)
    objects = relationship("Obj", back_populates="users")

class Society(Base):
    __tablename__ = 'society'
    society_id = Column(Integer, primary_key=True)
    obj_id = Column(Integer, ForeignKey("objects.obj_id"))
    name = Column(String(100), default=None)
    objects = relationship("Obj", back_populates="society")
    people = relationship("People", back_populates="society")

Answer 1

Your table structure resembles joined table inheritance, with objects.obj_type as the discriminator:

In [2]: class Obj(Base):
   ...:     __tablename__ = 'objects'
   ...:     obj_id = Column(Integer, primary_key=True)
   ...:     obj_type = Column(String(30))
   ...:     __mapper_args__ = {
   ...:         'polymorphic_identity': 'object',
   ...:         'polymorphic_on': obj_type
   ...:     }
   ...:     

In [3]: class User(Obj):
   ...:     __tablename__ = 'users'
   ...:     user_id = Column(Integer, primary_key=True)
   ...:     obj_id = Column(Integer, ForeignKey('objects.obj_id'))
   ...:     name = Column(String(100), default=None)
   ...:     mail = Column(String(200), default=None)
   ...:     __mapper_args__ = {
   ...:         'polymorphic_identity': 'user'
   ...:     }
   ...:     

In [4]: class Society(Obj):
   ...:     __tablename__ = 'society'
   ...:     society_id = Column(Integer, primary_key=True)
   ...:     obj_id = Column(Integer, ForeignKey("objects.obj_id"))
   ...:     name = Column(String(100), default=None)
   ...:     ...
   ...:     __mapper_args__ = {
   ...:         'polymorphic_identity': 'society'
   ...:     }
   ...:    

Note that you don't need the objects relationships in the child classes in order to link them to their related Obj. You can access the parent's attributes as you would normally when inheriting.

Adding the 3 elements:

In [6]: session.add(User(name='John', mail='john.smith@700.com'))

In [7]: session.add_all([Society(name='Google'), Society(name='Facebook')])

In [8]: session.commit()

And checking that we can polymorphically query all Objs:

In [9]: session.query(Obj).all()
Out[9]: 
[<__main__.User at 0x7f37b5aaa780>,
 <__main__.Society at 0x7f37b5aaab00>,
 <__main__.Society at 0x7f37b5aaacc0>]

You can also check what the tables actually contain:

In [10]: session.query(Obj.__table__).all()
Out[10]: [(1, 'user'), (2, 'society'), (3, 'society')]

In [11]: session.query(User.__table__).all()
Out[11]: [(1, 1, 'John', 'john.smith@700.com')]

In [12]: session.query(Society.__table__).all()
Out[12]: [(1, 2, 'Google'), (2, 3, 'Facebook')]