How to autofill table with foreign keys in SQLAlchemy using relationships?

user_17335438 0
Viewed 261

Using SQLAlchemy I have three models: Parent1, Parent2, and Child, where Parent1 has one-to-one relationship with Parent2, and both of them has the same relationship with Child. Here are they:

from extensions import db_session

class Parent1(Base):
    __tablename__ = 'parent1'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    
    parent2 = relationship("Parent2", backref="parent1", uselist=False)
    child = relationship("Child", backref="parent1", uselist=False)



class Parent2(Base):
    __tablename__ = 'parent2'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    parent1_id = Column(Integer, ForeignKey('parent1.id'))

    child = relationship("Child", backref="parent2", uselist=False)

class Child(Base):
    id = Column(Integer, primary_key=True)
    parent1_id = Column(Integer, ForeignKey('parent1.id'))
    parent2_id = Column(Integer, ForeignKey('parent2.id'))

What I am trying to achieve is to fill Child table with its parents' foreign keys. So, when I execute this:

parent1 = Parent1(name="Adil")
parent1.parent2 = Parent2(name="Aisha")
parent1.child = Child()
db_session.add(parent1)
db_session.commit()

to the parents tables it inserts data as needed, however to the Child table it inserts data like this:

Child

id  parent1_id  parent2_id
 1           1        NULL

How to properly set relationships, so that on any insert to the Parent1->Parent2 tables it also inserts its ids as foreign keys to Child table?

What I want to achieve is:

Child

id  parent1_id  parent2_id
 1           1           1
flask-sqlalchemy sqlalchemy orm sql python
1 Answers

I came up with this solution. I removed declared model Child and created association table between two models and added secondary parameter filling with assocation table name:

Base = declarative_base()

child = Table('child', Base.metadata,
               Column('parent1_id', Integer, ForeignKey('parent1.id')),
               Column('parent2_id', Integer, ForeignKey('parent2.id')))


class Parent1(Base):
    __tablename__ = 'parent1'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    parent2 = relationship("Parent2", secondary='child', uselist=False)


class Parent2(Base):
    __tablename__ = 'parent2'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    parent1_id = Column(Integer, ForeignKey('parent1.id'))
user_173354380
Related