CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
maxcountryman
maxcountryman

Reputation: 1759

Is it possible to commit two related tables in a SQLAlchemy transaction?

I'm using the higher-level SQLAlchemy ORM via Flask-SQLAlchemy. I'd like to be able to create a number of tables and then commit them in a single transaction. However, one table relates to all the other tables and I'm unsure how to proliferate its ID before it actually exists. (Note that its primary key is an auto-incrementing value that doesn't exist until we actually execute the SQL.)

For example, say we have TableA and TableB. TableB has a column called table_a_id. The TableA ID is managed by the database; it auto-increments and doesn't exist until we create a row of TableA.

I'd like to be able to create TableA and TableB at the same time and if anything goes wrong rollback the transaction.

In a perfect world where magic is possible, my code would look something like:

try:
    a = TableA()
    b = TableB(table_a_id=a.id)  # a.id is None, since nothing has happened yet.
    db.session.commit()
except Exception:
    db.session.rollback()

Is it possible, maybe via a sub transaction, to fill in the value of a.id in the transaction?

Upvotes: 5

Views: 3867

Answers (2)

Ferdous Wahid
Ferdous Wahid

Reputation: 3367

I have two table one is UnserInfo another one is clientInfo. In this case userInfoId is forign key in the clientInfo table. So first i save userInfo then save the clientInfo with the userInfo id

Here I user flask, marshmallow, flask-marshmallow, marshmallow-sqlalchemy, SQLAlchemy, flask-sqlalchemy.

I did in my project just like this 

try:

    userInfo = UserInfo(
       userId=dataUserInfo['mobile'],
       password=dataUserInfo['password'],
       userTypeName='client')

    db.session.add(userInfo)     
    db.session.commit() # save userInfo in database
    dbUserInfoResult = userInfoDto.dump(userInfo).data # get userInfo id from database
    print('dbUserInfoResult.data.id: ', dbUserInfoResult['id'])

    clientInfo = ClientInfo(name=dataClintInfo['name'],
                      mobileCountryCode=dataClintInfo['mobileCountryCode'],
                      mobile=dataClintInfo['mobile'],
                      email=dataClintInfo['email'] if 'email' in dataClintInfo else None,
                      userInfoId=dbUserInfoResult['id'])

    db.session.add(clientInfo)
    db.session.commit()
except Exception:
    db.session.rollback()

Upvotes: -1

knitti
knitti

Reputation: 7033

Just use session.flush() after creating your a object, that issues all pending SQL in the current transaction and will fill in auto-generated IDs. Later you can commit() or rollback() as needed.

Upvotes: 9

Related Questions