Flask form: how can I use multiple forms to add Parent and Child entities to DB in one commit?

Question

NB: see answers for resolution... long story short, one cannot "use multiple forms to add Parent and Child entities to DB in one commit" using the same database session in SQLAlchemy if there are HTTP requests between forms. The appropriate approach for my use case was to save the output of my multiple forms in the Flask session and subsequently iterate through the session in a single view to make the database commits.

Original question:

TL;DR: can I use a Flask-WTF form to tentatively create a Parent item via SQLAlchemy, db.session.flush() to get Parent's ID and pass it to a second Flask-WTF form to populate Child's foreign key, and then commit Parent and Child in one db.session.commit()?

I'm building a Flask web app that enables users to create and manage competitive events. My database models include Events and Eventsets. Events may be children of Eventsets, but it is not required for an Event to have a corresponding Eventset parent. However, for situations where users want to create Eventsets and corresponding Events all at once, I want to enable this via a two step form (which I'm trying to implement using two separate flask-wtf forms and Flask views).

The first form and view enable the user to create an instance of Eventset(). This Eventset() is added to a sqlalchemy database session and flushed, but not committed. If the form validates, the app redirects to the next view that enables the creation of an Event. I want to pass the ID of the previously-created Eventset to my Event() model to complete the Parent-Child relationship.

I'm attempting to do this by passing the ID generated by SQLAlchemy for the Eventset in the first step via the Flask session. **I am able to successfully add the Eventset_id to my Flask session and verify that a SQLAlchemy session is active, but any events created in the second step do not recognize the flushed (but not committed) Eventset, and end up committed with eventset_id = NONE.

I want to avoid committing the Eventset from the first step, as I don't want users to inadvertently create orphaned Eventsets if they don't complete the full setup process (i.e. creating an Eventset and n Events).

forms.py

class EventsetsForm(FlaskForm):
    name = StringField("Eventset Name", validators=[DataRequired()])
    submit = SubmitField('Submit')

class EventForm(FlaskForm):
    eventset_id = SelectField('Eventset', validators=[Optional()], coerce=int)
    name = StringField("Event Name", validators=[DataRequired()])
    submit = SubmitField('Submit')

    def __init__(self, *args, **kwargs):
        super(EventForm, self).__init__(*args, **kwargs)
        self.eventset_id.choices = [(0, "---")]+[(eventset.id, eventset.name)
                             for eventset in Eventset.query.order_by(Eventset.name).all()]

views.py

nb: the flashed and printed messages are to help me see what's happening

@main.route('/eventsets/setup/step_one', methods=['GET', 'POST'])
@login_required
@admin_required
def setup_step_one():
    form = EventsetsForm()
    if current_user.can(Permission.WRITE) and form.validate_on_submit():
        eventset = Eventset(name=form.name.data, 
                            author=current_user._get_current_object())
        db.session.add(eventset)
        db.session.flush()
        session['eventset_id'] = eventset.id
        flash('STEP ONE: an eventset named %s has been propped.' % eventset.name)
        flash('STEP ONE: The id from session is: %s' % session['eventset_id'])
        print('STEP ONE: %s' % session['eventset_id'])
        if eventset in db.session:
            print('STEP ONE: sqlalchemy object for eventset is: %s' % eventset)
        return redirect(url_for('.setup_step_two'))
    return render_template('eventset_setup.html', form=form)  

@main.route('/eventsets/setup/step_two', methods=['GET', 'POST'])
@login_required
@admin_required
def setup_step_two():
    print('Is the db session active? %s' % db.session.is_active)
    print('STEP TWO: the eventset id from Flask session should be: %s' % session['eventset_id'])
    eventset_id = int(session['eventset_id'])
    print('STEP TWO: is the eventset_id in the session an int? %s ' % isinstance(eventset_id, int))
    form = EventForm()
    form.eventset_id.data = eventset_id
    if current_user.can(Permission.WRITE) and form.validate_on_submit():
        event = Event(name=form.name.data,
                      author=current_user._get_current_object(),
                      description=form.description.data,
                      location=form.location.data,
                      scheduled=form.scheduled.data,
                      eventset_id=form.eventset_id.data,
                      event_datetime=form.event_datetime.data,
                      open_datetime=form.open_datetime.data)
        db.session.add(event)
        db.session.commit()
        flash('An event named %s has been created, with eventset_id of %s.' % (event.name, event.eventset_id))
        return redirect(url_for('.setup_step_two'))
    return render_template('eventset_setup.html', eventset_id=eventset_id, form=form)

eventset_setup.html

{% block page_content %}

    
        {% if session['eventset_id'] != None %}Eventset id should be: {{ session['eventset_id'] }}{% endif %}
        {% if flarg != None %}{{ flarg }}{% endif %}
    
    
        {{ wtf.quick_form(form) }}
    

{% endblock %}

Terminal output

127.0.0.1 - - [11/Apr/2019 23:11:34] "GET /eventsets/setup/step_one HTTP/1.1" 200 -
STEP ONE: 54
STEP ONE: sqlalchemy object for eventset is: 
127.0.0.1 - - [11/Apr/2019 23:11:38] "POST /eventsets/setup/step_one HTTP/1.1" 302 -
Is the db session active? True
STEP TWO: the eventset id from Flask session should be: 54
STEP TWO: is the eventset_id in the session an int? True
127.0.0.1 - - [11/Apr/2019 23:11:38] "GET /eventsets/setup/step_two HTTP/1.1" 200 -

...yet Events created in this flow result in event.eventset_id == NONE

Ideally, I would like to enable users to create an Eventset and a related Event with a single SQLAlchemy commit (if I get one Eventset:Event creation to work I can figure out adding multiple Events). Currently, my code results in an Eventset.id value being written to the session, and Events created and committed to the db without the expected Eventset parent. I strongly prefer to avoid using hidden form fields to accomplish this, and unfortunately my Javascript knowledge is negligible.

Attack68 · Accepted Answer

Further to my comments your approach is not recommended becuase you are attempting to persist a database session over two routes. What if the browser did not follow the second route or was redirected somewhere else on your website? You would then have an open session with partially modified data that might prevent a commit to the database by breaking integrity.

As I commented the SQLAlchemy docs explain a little more about this here: https://docs.sqlalchemy.org/en/13/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it

And if you are using flask-sqlachemy then the bottom of this page, https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/ explains that it closes and rolls back your session automatically at the end of the first route (request). The particular line of code in the flask-sqlachemy source that does this is:

# flask-sqlalchemy source __init__.py lines 805  - 812
@app.teardown_appcontext
def shutdown_session(response_or_exc):
    if app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN']:
        if response_or_exc is None:
            self.session.commit()

    self.session.remove()
    return response_or_exc

A better way of achieving what you want if to probably use the session object as a store, instead of adding data to the database add it to session:

session['new_eventset_name'] = form.name.data

Then when you are in the second route perform a check for this:

eventset_name = session.get('new_eventset_name', None)
if eventset_name:
    eventset = Eventset(name=eventset_name, 
                        author=current_user._get_current_object())
    db.session.add(eventset)
    db.session.flush()
    eventset_id = eventset.id
    del session['new_eventset_name']
else:
    eventset_id = None

event = Event(name=form.name.data,
                  author=current_user._get_current_object(),
                  description=form.description.data,
                  location=form.location.data,
                  scheduled=form.scheduled.data,
                  eventset_id=eventset_id,  ## <-- NOTE THIS
                  event_datetime=form.event_datetime.data,
                  open_datetime=form.open_datetime.data)
db.session.add(event)
db.session.commit()