Refer One Column To Another Of Different Tables Using Sqlalchemy Orm

I have the following scenario, I need to insert to a table(table1) which have primary key of type 'serial'. Now I need to insert to another table(table2) which contains the primar

Solution 1:

When you create a model object in Python it is not yet flushed to the DB. In case of a serial column the DB is responsible for generating the new value, and so it is just None before generation. In the statement

table2 = Table2(table2id=table1.table1id)

you simply read that None and pass it as the keyword argument table2id. In order to obtain a value you need to flush the changes to the database, so you should reorder your operations a bit:

table1 = Table1(name='abc')
session.add(table1)
# Flush the changes to the DB
session.flush()
table2 = Table2(table2id=table1.table1id)
session.add(table2)
session.commit()

SQLAlchemy could also perform most of this for you more or less automatically, if you'd define the relationships between table 1 and 2, or if this is actually an inheritance hierarchy.