Create a transaction save point for multiple commits

[idenyigabriel]

I am working on a project and currently using flask-sqlalchemy library.

Everything seems to work fine until I attempt to create a transaction save point, I am pretty sure am doing it wrongly, but I can't just figure out why.

I would like to make several saves which are dependent on each other, however, I need to rollback all changes if even one fails. How do I go about this, below is my current function.

from app.extensions import db
from app.models import User, Wallet, Profile

def create_user(data):
    with db.session.begin():
        # create user
        user = User(**data)
        db.session.add(user)
        db.session.commit()

        # create user profile
        profile = Profile(user_id=user)
        db.session.add(wallet)
        db.session.save()

        # create user wallet
        wallet = Walletprofile_id=profile)
        db.session.add(wallet)
        db.session.save()

Running this return raise sa_exc.InvalidRequestError( sqlalchemy.exc.InvalidRequestError: A transaction is already begun on this Session.

I have tried using db.session.begin_nested(), but that fails as expected on the second commit.

How can I rollback multiple commits using flask-sqlalchemy.

[idenyigabriel]

After much research, I found a way to go about it, please let me know if there are any issues with my process.

so, rather than having to start a new session and commit each change, I instead flush to get the id I need to perform the following action. So instead of what I have above, I have this.

from app.extensions import db
from app.models import User, Wallet, Profile

def create_user(data):
    # create user
    user = User(**data)
    db.session.add(user)
    db.session.flush()

    # create user profile
    profile = Profile(user_id=user.id)
    db.session.add(wallet)
    db.session.flush()

    # create user wallet
    wallet = Walletprofile_id=profile.id)
    db.session.add(wallet)

    # commit all changes    
    db.session.commit()

[ThiefMaster]

You should not use IDs to link related objects. If you assign the actual objects to the relationship attributes, sqlalchemy will automatically populate the FKs during flush.

user = User(...)
db.session.add(user)  # only needed if none of the objects are already in the DB (or rather session)
profile = Profile(user=user)
wallet = Wallet(profile=profile)
db.session.commit()