Is there a way to populate one table from multiple on SQLAlchemy

Question

I'm trying to build a database with SQLAlchemy, my problem is that I have two tables with the same columns name and trying to populate a third table from the two others. There is below a simple diagram to illustrate:

I usually set Foreign key on one table and the relationship on the other like that :

class TableA(Base):
    __tablename__ = "tableA"
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    age = Column(Integer)
    name_relation = relationship("TableC", backref='owner')

class TableC(Base):
    __tablename__ = "tableC"
    id = Column(Integer, primary_key=True)
    name = Column(String(100), ForeignKey('tableA.name'))
    age = Column(Integer)

You can see that this method can only works with two table because my ForeignKey on tableC for the name specifies the name of tableA.

Is there a way to do that ?

Thanks

Are you trying to do a one time copy or are you actually looking for a [`view`](https://www.postgresql.org/docs/9.2/sql-createview.html)? This is essentially a shortcut to a subquery that returns whatever you like. You can also store it as a table - then you can add indexes and stuff, but you have to update the view manually. In that case, the view is called `materialized` — Ruben Helsloot, Oct 28 '20 at 14:40
I'm trying to do a one time copy not a view, I thought that it was possible to do a copy automatically with SQLAlchemy. — Gabin Legrand, Oct 28 '20 at 15:25
So you want to copy the contents of `A` and `B` into `C` one time? And is there a reason for not wanting to use regular SQL? — Ruben Helsloot, Oct 28 '20 at 16:49

score 1 · Accepted Answer · answered Oct 29 '20 at 12:47

In SQL, the query you'd be looking for is

INSERT INTO C (id, name, age) (
  SELECT *
  FROM A
  UNION ALL
  SELECT *
  FROM B
)

As per this answer, this makes the equivalent SQLAlchemy

session = Session()
query = session.query(TableA).union_all(session.query(TableB))
stmt = TableC.insert().from_select(['id', 'name', 'age'], query)

or equivalently

stmt = TableC.insert().from_select(
  ['id', 'name', 'age'],
  TableA.select().union_all(TableB.select())
)

After which you can execute it using connection.execute(stmt) or session.execute(stmt), depending on what you're using.

Is there a way to populate one table from multiple on SQLAlchemy

1 Answers1