SQLAlchemy session.begin() giving transaction error when context manager doesn't

Question

In short, why am I getting an "sqlalchemy.exc.InvalidRequestError: A transaction is already begun. Use subtransactions=True to allow subtransactions" error?

Following the best practices of separating and keeping the session external, I created foo(input) with a context manager instead of using the try / except / else. If I use foo(user) instead of it I get the above error. My guess is that foo() isn't committing and closing the connection. Howevere the documentation states otherwise.

Flask documentation uses a scoped_session but the SQLAlchemy documentation says "It is however strongly recommended that the integration tools provided with the web framework itself be used, if available, instead of scoped_session." Perhaps the scoped_session is causing errors across threads with the requests?

Here is my main code:

#__init__.py
import os

from flask import Flask, render_template, redirect, request, url_for


def create_app(test_config=None):
    # create and configure the app
    app = Flask(__name__, instance_relative_config=False)
    app.config.from_object('config.DevelopmentConfig')
    
    # set up extensions
    # all flask extensions must support factory pattern
    # can run these two steps from the cli
    from app.database import init_db
    init_db()

    
    @app.route('/')
    def index():
        return render_template('index.html')

    from app.auth import RegistrationForm
    from app.models import User
    from app.database import db_session, foo

    @app.route('/register', methods=['GET', 'POST'])
    def register():
        form = RegistrationForm(request.form)
        if request.method == 'POST' and form.validate():
            user = User(form.name.data, form.email.data,
                        form.password.data)
            foo(user)
            # try:
            #     db_session.add(user)
            # except:
            #     db_session.rollback()
            #     raise
            # else:
            #     db_session.commit()
            return redirect(url_for('login'))
        return render_template('register.html', form=form)

    
    @app.route('/login', methods=['GET'])
    def login():
        return render_template('login.html')
    

    @app.teardown_appcontext
    def shutdown_session(exception=None):
        db_session.remove()
    
    return app

Here is my database code:

#database.py
from sqlalchemy import create_engine 
from sqlalchemy.orm import scoped_session, sessionmaker 
from sqlalchemy.ext.declarative import declarative_base

_database_uri = os.environ['DATABASE_URL'] engine = create_engine(_database_uri)
db_session = scoped_session(sessionmaker(autocommit=False,
                                         autoflush=False,
                                         bind=engine))

Base = declarative_base() Base.query = db_session.query_property()

def init_db():
    # import all modules here that might define models so that
    # they will be registered properly on the metadata.  Otherwise
    # you will have to import them first before calling init_db()
    import app.models
    Base.metadata.create_all(bind=engine)


def foo(input):
    with db_session.begin() as session:
        session.add(input)

Answer 1

I'm not sure whether this will actually answer your question or not but I think it worth mentioning.

Near the last line in your database.py file, I suggest you to not alias db_session.begin() as session because you'll then be confused thinking that session is an object of Session class while it's an object of SessionTransaction class which is:

largely an internal object that in modern use provides a context manager for session transactions. SessionTransaction

You can switch to either:

with db_session() as session, session.begin():
    session.add(input)

or shorter version

with db_session.begin():
    db_session.add(input)

Also you need to wrap your User object creation with Session.begin() context like below:

def register():
    form = RegistrationForm(request.form)
    if request.method == 'POST' and form.validate():
        with db_session.begin():
            user = User(form.name.data, form.email.data, 
                        form.password.data)
        foo(user)

Because User model is just a proxy object that will actually execute database query under the hood. Therefore A transaction is already begun. in the creation process. The exception itself will be raised upon the next transactional query calls.

When using a Session, it’s useful to consider the ORM mapped objects that it maintains as proxy objects to database rows, which are local to the transaction being held by the Session. In order to maintain the state on the objects as matching what’s actually in the database, there are a variety of events that will cause objects to re-access the database in order to keep synchronized. It is possible to “detach” objects from a Session, and to continue using them, though this practice has its caveats. It’s intended that usually, you’d re-associate detached objects with another Session when you want to work with them again, so that they can resume their normal task of representing database state.
Session Basic

As additional answer to your last question: Perhaps the scoped_session is causing errors across threads with the requests?

No. SQLAlchemy scoped_session is actually a helper function that act as Registry for the global Session object. It is very useful in multithreading application, helping to ensure the same Session object is being used accross threads while each keeping their own data to local via threading.local api provided by python. Most web framework uses threading strategies to cope with many web requests at once hence most of them provide some integration with/without this helper.