DEV Community

Cover image for Python: a workaround for SQLAlchemy “Table 'sessions' is already defined” exception during tests.
Be Hai Nguyen
Be Hai Nguyen

Posted on

Python: a workaround for SQLAlchemy “Table 'sessions' is already defined” exception during tests.

#python #pytest #sessions #flask

During tests, Flask-Session intermittently causes the exception “sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.” I'm presenting a workaround in this post.

I'm using pytest, and I've been experiencing intermittent tests failure due to exception “sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object.”. It only occurs if some tests are run together, run them individually one at a time, they pass. I've ignored this problem till a few days ago. Right now I have around 396 ( three hundreds and ninety six ) test cases, just by chance, I run some three ( 3 ) tests together, and two ( 2 ) consistently fail with the mentioned exception, only one ( 1 ) passes.

It turns out this is an ongoing issue with Flask-Session 0.4.0, and still persists in the latest version 0.4.0. Latest as on the 24th, November 2022.

My research led to the following post, among other posts which report the same issue:

How do you resolve 'Already defined in this MetaData Instance' Error with Flask Pytest, SqlAlchemy, and Flask Sessions? The answer by user Tassaron on 01/01/2021 leads to the following URLs:

The general consensus seems to be only creating the Model for the database session table only if it does not already exists via the condition:

if table not in self.db.metadata:
Enter fullscreen mode Exit fullscreen mode

It has been proposed a few years back, but for some reasons, it has not been implemented by the author.

Tassaron, herself, has implemented this in https://github.com/tassaron/muffin-shop/blob/main/src/helpers/main/session_interface.py:

class TassaronSessionInterface(SessionInterface):
    ...

    def __init__(self, app, db):
        ...

        if table not in self.db.metadata:
            # ^ Only create Session Model if it doesn't already exist
            # Fixes the SQLAlchemy "extend_existing must be true" exception during tests
            class Session(self.db.Model):
                ...
            self.sql_session_model = db.session_ext_session_model = Session
        else:
            self.sql_session_model = db.session_ext_session_model
Enter fullscreen mode Exit fullscreen mode

Compared to the original Flask-Session 0.4.0:

class SqlAlchemySessionInterface(SessionInterface):
    ...

    def __init__(self, app, db, table, key_prefix, use_signer=False,
                 permanent=True):
        ...

        class Session(self.db.Model):
            ...

        self.sql_session_model = Session
Enter fullscreen mode Exit fullscreen mode

In TassaronSessionInterface, when the Session Model is first created, it also gets assigned to db new attribute session_ext_session_model, afterwards db.session_ext_session_model is used.

Apart from exceptions intermittently raised during tests, Flask-Session 0.4.0 works fine. I want to stick to it as much as possible. Following is my attempt, it feels like a workaround, a hack rather than a solution, I'm okay with this for the time being:

Content of fixed_session.py
Enter fullscreen mode Exit fullscreen mode 
from flask_session.sessions import SqlAlchemySessionInterface
from flask_session import Session

class FixedSqlAlchemySessionInterface( SqlAlchemySessionInterface ):
    def __init__(self, app, db, table, key_prefix, use_signer=False,
                 permanent=True):
        """
        Assumption: the way I use it, db is always a valid instance 
        at this point.
        """
        if table not in db.metadata:
            super().__init__( app, db, table, key_prefix, use_signer, permanent )
            db.session_ext_session_model = self.sql_session_model
        else:
            # print( "`sessions` table already exists..." )

            self.db = db
            self.key_prefix = key_prefix
            self.use_signer = use_signer
            self.permanent = permanent
            self.has_same_site_capability = hasattr(self, "get_cookie_samesite")

            self.sql_session_model = db.session_ext_session_model

class FixedSession( Session ):
    def _get_interface(self, app):
        config = app.config.copy()

        if config[ 'SESSION_TYPE' ] != 'sqlalchemy':
            return super()._get_interface( app )

        else:
            config.setdefault( 'SESSION_PERMANENT', True )
            config.setdefault( 'SESSION_KEY_PREFIX', 'session:' )

            return FixedSqlAlchemySessionInterface(
                app, config['SESSION_SQLALCHEMY'],
                config['SESSION_SQLALCHEMY_TABLE'],
                config['SESSION_KEY_PREFIX'], config['SESSION_USE_SIGNER'],
                config['SESSION_PERMANENT'] )
Enter fullscreen mode Exit fullscreen mode

To use this implementation, import FixedSession as Session, then carry on as normal:

try:
    from xxx.yyyy.fixed_session import FixedSession as Session
except ImportError:
    from flask_session import Session
Enter fullscreen mode Exit fullscreen mode

When Flask-Session is fixed, I can just remove fixed_session.py without needing to update any codes -- however, I should update the import, exceptions are expensive.

Back to my attempt, in FixedSqlAlchemySessionInterface, I copied the idea of db.session_ext_session_model from TassaronSessionInterface. The following lines are copied from the original codes:

            self.db = db
            self.key_prefix = key_prefix
            self.use_signer = use_signer
            self.permanent = permanent
            self.has_same_site_capability = hasattr(self, "get_cookie_samesite")
Enter fullscreen mode Exit fullscreen mode

That means, if Flask-Session gets updated without fixing this issue, I might have to update my codes!

In class FixedSession, the following lines in the overridden method def _get_interface(self, app):

            config.setdefault( 'SESSION_PERMANENT', True )
            config.setdefault( 'SESSION_KEY_PREFIX', 'session:' )
Enter fullscreen mode Exit fullscreen mode

are also copied from the original codes, I never have SESSION_PERMANENT and SESSION_KEY_PREFIX in my environment variables file.

With, or without the “sessions” table in the database, my tests and application run with no problems. If I drop “sessions” table, it gets created as expected.

It's been fun investigating this issue. I'm not too happy with my code, but it works for me for the time being. Hopefully, the author will fix it in the future releases. In the meantime, I really do hope this post helps others whom come across this same problem. Thank you for reading and stay safe as always.

Top comments (11)

Collapse
 
lxstr profile image
Lex •

Hi there is a recent fix for this in 0.6.0. Please let me know if that works for you

Collapse
 
behainguyen profile image
Be Hai Nguyen • • Edited

Hi @lxstr,

First, thank you for your hard works. And do apologise for the late reply. I had been caught up in something else.

I have today just finished testing out Flask-Session new versions.

Following are what I have found out:

  • Version 0.6.0 works as you have stated. I have been able to run tests multiple without the stated error occured.

  • Version 0.8.0 is worse than version 0.4.0, it actually raises 3 ( three ) errors:

On Tests

This is the one that occurs using version 0.4.0:

E               sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
Enter fullscreen mode Exit fullscreen mode

This is the new one, which did not happen on version version 0.4.0:

ERROR tests/bro/test_rbac_bro.py::test_list_all_system_menu - AttributeError: 'SqlAlchemySessionInterface' object has no attribute 'db'
ERROR tests/unit/test_rbac_system_menu.py::test_sqlalchemy_filter_id_equal_01 - sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance.  Specify 'exten...
ERROR tests/unit/test_rbac_system_menu.py::test_sqlalchemy_filter_id_equal_02 - sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance.  Specify 'exten...
Enter fullscreen mode Exit fullscreen mode

The Application

This is the new one, which did not happen on version version 0.4.0:

...
  File "E:\book-keeping\app.py", line 16, in <module>
    app = create_app()
  File "e:\book-keeping\src\book_keeping\__init__.py", line 45, in create_app
    init_app_session( app )
  File "e:\book-keeping\src\book_keeping\__init__.py", line 69, in init_app_session
    app_session.app.session_interface.db.create_all()
AttributeError: 'SqlAlchemySessionInterface' object has no attribute 'db'
Enter fullscreen mode Exit fullscreen mode

Please note, I did also verify that version 0.4.0 causes error as has been described in post.

Thank you and best regards,

...behai.

Collapse
 
lxstr profile image
Lex •

Hi Behai, there were some changes from 0.6 to 0.8 but they 'should' have been improvements. From the errors I'm seeing it looks as if you are trying to subclass/override the interface and/or create tables or access the db attribute (which is internal and now renamed). There should ideally no longer be any need to do anything other than use the default interface, could you you confirm you are trying 0.8.0 without any such modifications or show me the specific lines the error occurs? Thanks!

Thread Thread
 
behainguyen profile image
Be Hai Nguyen •

Hi @lxstr,

Thank you for replying so quickly. What I did was verifying that "my errors" still occur using 0.4.0. And they do. Then I uninstalled 0.4.0, install 0.6.0, no errors occur for the tests or the application. Then I uninstalled 0.6.0, installed 0.8.0, then I got the three errors as reported in my previous reply, I ran it more than once, the errors were consistent. I then uninstalled 0.8.0, reinstalled 0.6.0 again. No errors, I ran everything more than once.

-- I tested both 0.6.0 and 0.8.0 with exactly the same code.

I have not looked into why the errors occur yet. I will do so in the next few days.

Thank you and best regards, Lex.

...behai.

Thread Thread
 
behainguyen profile image
Be Hai Nguyen •

Hi Lex,

My E:\book-keeping\setup.py, please note Flask-Session=0.8.0:

"""
Installation script for book_keeping project.

Editable install command:

    venv\Scripts\pip.exe install -e .
"""
from pathlib import Path
from setuptools import setup, find_packages

setup(
    name='book-keeping',
    description='Omphalos Book Keeping.',
    version='1.0.0',
    author='Van Be Hai Nguyen',
    author_email='behai_nguyen@hotmail.com',
    packages=find_packages(where="src"),
    package_dir={"": "src"},
    python_requires='>=3.10',
    install_requires=[
        'Werkzeug==2.3.4',
        'Flask==2.2.5',
        'python-dotenv==1.0.0',
        'mysql-connector-python==8.0.33',
        'Flask-Login==0.6.2',
        'Flask-SQLAlchemy==3.0.2',
        'Flask-Session=0.8.0',
        'Flask-Bcrypt==1.0.1',
        'Flask-WTF==1.1.1',
        'PyYAML==6.0',
        'simplejson==3.19.1',
        'email-validator==2.0.0',
        'xhtml2pdf==0.2.11',
        'blinker==1.6.2',
        'pytest==7.3.1',
        'coverage==7.2.5',
    ],
)
Enter fullscreen mode Exit fullscreen mode

My E:\book-keeping\src\book_keeping\__init__.py:

"""
Application package.
"""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

try:
    from book_keeping.library.fixed_session import FixedSession as Session
except ImportError:
    # print( 'from flask_session import Session' )
    from flask_session import Session

from flask_bcrypt import Bcrypt

from flask_wtf.csrf import CSRFProtect

from flask_login import LoginManager

import yaml
import logging
import logging.config

from book_keeping.config import get_config

db = SQLAlchemy()

csrf = CSRFProtect()

login_manager = LoginManager()
login_manager.session_protection = 'strong'
login_manager.login_view = 'auths.login'
login_manager.login_message_category = 'info'

bcrypt = Bcrypt()

def create_app( config=None ):
    """Construct the core application."""
    app = Flask( __name__, instance_relative_config=False )

    app.config.from_object( config if config != None else get_config() )

    init_extensions( app )
    register_loggers()

    init_app_session( app )

    init_csrf( app )
    register_blueprints( app )

    return app

def init_extensions( app ):
    app.url_map.strict_slashes = False
    db.init_app( app )

    login_manager.init_app( app )
    bcrypt.init_app( app )

def register_loggers():
    with open( 'omphalos-logging.yml', 'rt' ) as file:
        config = yaml.safe_load( file.read() )
        logging.config.dictConfig( config )

def init_app_session( app ):
    app.config[ 'SESSION_SQLALCHEMY' ] = db

    app_session = Session( app )
    with app.app_context():
        app_session.app.session_interface.db.create_all()

def init_csrf( app ):
    csrf.init_app( app )

def register_blueprints( app ):
    from book_keeping import urls

    for url in urls.urls:
        url[ 2 ].add_url_rule( url[0], methods=url[1], view_func=url[3] )

    for blueprint in urls.blueprints:
        app.register_blueprint( blueprint )
Enter fullscreen mode Exit fullscreen mode

Please note:

try:
    from book_keeping.library.fixed_session import FixedSession as Session
except ImportError:
    # print( 'from flask_session import Session' )
    from flask_session import Session
Enter fullscreen mode Exit fullscreen mode

E:\book-keeping\src\book_keeping\library\ DOES NOT have fixed_session.py module. I renamed it. Please see attached screenshot:

048-flask-session-error-response.png

My E:\book-keeping\.env:

SECRET_KEY = 51bae554-b54c-4ee6-8114-643893d371a0
FLASK_APP = app.py
FLASK_DEBUG = True
SQLALCHEMY_DATABASE_URI = mysql+mysqlconnector://behai1:password@localhost/ompdev1
# SQLALCHEMY_DATABASE_URI = mysql+mysqlconnector://behai1:password@HP-Pavilion-15/ompdev1
### SQLALCHEMY_DATABASE_URI = postgresql+psycopg2://postgres:pcb.2176310315865259@localhost/ompdev
# SQLALCHEMY_DATABASE_URI = postgresql+psycopg2://postgres:pcb.2176310315865259@HP-Pavilion-15/ompdev
# SQLALCHEMY_DATABASE_URI = postgresql+psycopg2://postgres:pcb.2176310315865259@192.168.0.16/ompdev
SQLALCHEMY_DATABASE_SCHEMA = ompdev1
SESSION_TYPE = sqlalchemy
SESSION_SQLALCHEMY_TABLE = sessions
SESSION_USE_SIGNER = True
SQLALCHEMY_TRACK_MODIFICATIONS = False
SESSION_LIFETIME_IN_MINUTES = 60
MINIFIED_CSS_JS = False
TO_EMAIL_ADDRESSES = behai_nguyen@hotmail.com, blla, blah
CC_EMAIL_ADDRESSES = blah
EMAIL_ON_ACCOUNT_CREATED = False
EMAIL_ON_ACCOUNT_UPDATED = False
Enter fullscreen mode Exit fullscreen mode

Full application error using version 0.8.0, "application" being venv\Scripts\flask.exe run:

(venv) E:\book-keeping>venv\Scripts\flask.exe run
Traceback (most recent call last):
  File "C:\PF\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\PF\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "E:\book-keeping\venv\Scripts\flask.exe\__main__.py", line 7, in <module>
  File "E:\book-keeping\venv\lib\site-packages\flask\cli.py", line 1050, in main
    cli.main()
  File "E:\book-keeping\venv\lib\site-packages\click\core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "E:\book-keeping\venv\lib\site-packages\click\core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "E:\book-keeping\venv\lib\site-packages\click\core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "E:\book-keeping\venv\lib\site-packages\click\core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "E:\book-keeping\venv\lib\site-packages\click\decorators.py", line 92, in new_func
    return ctx.invoke(f, obj, *args, **kwargs)
  File "E:\book-keeping\venv\lib\site-packages\click\core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "E:\book-keeping\venv\lib\site-packages\flask\cli.py", line 911, in run_command
    raise e from None
  File "E:\book-keeping\venv\lib\site-packages\flask\cli.py", line 897, in run_command
    app = info.load_app()
  File "E:\book-keeping\venv\lib\site-packages\flask\cli.py", line 308, in load_app
    app = locate_app(import_name, name)
  File "E:\book-keeping\venv\lib\site-packages\flask\cli.py", line 218, in locate_app
    __import__(module_name)
  File "E:\book-keeping\app.py", line 16, in <module>
    app = create_app()
  File "e:\book-keeping\src\book_keeping\__init__.py", line 45, in create_app
    init_app_session( app )
  File "e:\book-keeping\src\book_keeping\__init__.py", line 69, in init_app_session
    app_session.app.session_interface.db.create_all()
AttributeError: 'SqlAlchemySessionInterface' object has no attribute 'db'
Enter fullscreen mode Exit fullscreen mode

I can confirm that, after extracting the above error for this post, I went back to version 0.6.0, and everything works.

-- Please note that, to go back to version 0.6.0, I have to change the entry in setup.py to 'Flask-Session=0.6.0',, then manually install it with:

(venv) E:\book-keeping>venv\Scripts\pip.exe install Flask-Session==0.6.0
Enter fullscreen mode Exit fullscreen mode

Thank you and best regards,

...behai.

Thread Thread
 
behainguyen profile image
Be Hai Nguyen •

Hi Lex,

Just a little bit more info...

Regarding version 0.8.0, in E:\book-keeping\src\book_keeping\__init__.py, change to:

        app_session.app.session_interface.client.create_all()
Enter fullscreen mode Exit fullscreen mode

from:

        app_session.app.session_interface.db.create_all()
Enter fullscreen mode Exit fullscreen mode

-- That is attribute client replaced attribute db. The application runs.

However, my test still results in error:

E               sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.
Enter fullscreen mode Exit fullscreen mode

as per version .0.4.0.

Thank you and best regards,

...behai.

Thread Thread
 
behainguyen profile image
Be Hai Nguyen •

Hi Lex,

For version 0.8.0, in sqlalchemy\sqlalchemy.py, I copy the following from 0.6.0:

        if schema is not None:
            __table_args__ = {"schema": self.schema, "keep_existing": True}
        else:
            __table_args__ = {"keep_existing": True}
Enter fullscreen mode Exit fullscreen mode

to replace:

        __table_args__ = {"schema": schema} if schema else {}
Enter fullscreen mode Exit fullscreen mode

That is:

def create_session_model(db, table_name, schema=None, bind_key=None, sequence=None):
    class Session(db.Model):
        __tablename__ = table_name
        # __table_args__ = {"schema": schema} if schema else {}
        if schema is not None:
            __table_args__ = {"schema": self.schema, "keep_existing": True}
        else:
            __table_args__ = {"keep_existing": True}
        __bind_key__ = bind_key
        ...
Enter fullscreen mode Exit fullscreen mode

And errors disappear. The tests and the application work.

Thank you and best regards,

...behai.

Thread Thread
 
lxstr profile image
Lex •

Ok I may decide to add again the "keep_existing": True, however this shouldn't be needed. If you delete or comment the following lines and use 0.8.0, I would be interested to know the result. There should be no need to manually create the table as flask-session does this now.

# try:
   # from book_keeping.library.fixed_session import FixedSession as Session
# except ImportError:
    # print( 'from flask_session import Session' )
from flask_session import Session
Enter fullscreen mode Exit fullscreen mode

and

with app.app_context(): 

   # app_session.app.session_interface.client.create_all()
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
behainguyen profile image
Be Hai Nguyen •

Hi Lex,

Based on your advice:

E:\book-keeping\src\book_keeping\__init__.py modified to:

"""
Application package.
"""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from flask_session import Session

...
def init_app_session( app ):
    app.config[ 'SESSION_SQLALCHEMY' ] = db

    # app_session = Session( app )
    # with app.app_context():
    #    app_session.app.session_interface.client.create_all()
...
Enter fullscreen mode Exit fullscreen mode

I have also decided to use latest of everything, E:\book-keeping\setup.py updated to:

...
    install_requires=[
        'Flask',
        'python-dotenv',
        'mysql-connector-python',
        'Flask-Login',
        'Flask-SQLAlchemy',
        'Flask-Session',
        'Flask-Bcrypt',
        'Flask-WTF',
        'PyYAML',
        'simplejson',
        'email-validator',
        'xhtml2pdf',
        'blinker',
        'pytest',
        'coverage',
    ],
...
Enter fullscreen mode Exit fullscreen mode

I am using the original Flask-Session 0.8.0:

048-flask-session-0-8-0.png

Test 1: table sessions exists:

  • The application runs. That is, venv\Scripts\flask.exe run does not raise exception.

  • Tests no longer raise the table sessions exists exception. They now raise another unrelated exception.

Test 2: I manually removed the existing sessions table:

The results are as in Test 1. BUT THE TABLE sessions DOES NOT GET CREATED.

Further note:

With:

def init_app_session( app ):
    app.config[ 'SESSION_SQLALCHEMY' ] = db

    app_session = Session( app )
    with app.app_context():
        app_session.app.session_interface.client.create_all()
Enter fullscreen mode Exit fullscreen mode

  • Tests raise the old exception (0.4.0). But the sessions table gets created.

  • venv\Scripts\flask.exe run no longer raises the old exception (0.4.0) and the table sessions table also gets created.

Please note that, I posted this article on the 24th, November 2022. And labelled the code for this article. That is why I am able to get the code test this problem. I forgot to label database version!

Since the this article, I added more functionalities including database changes till around May 2023, I have since stopped working on this project.

For these tests, for the application (that is, venv\Scripts\flask.exe run), I can only get to the login page. But that should be enough.

Please let me know if you want me to do anything else.

Thank you and best regards,

...behai.

Thread Thread
 
behainguyen profile image
Be Hai Nguyen • • Edited

Hi @lxstr,

Your advice works.

And this report from my last response is not correct:

Test 2: I manually removed the existing sessions table:

The results are as in Test 1. BUT THE TABLE sessions DOES NOT GET CREATED.

There was something in my code what caused it not to work. I am using all latest packages now.

There is no need for:

Ok I may decide to add again the "keep_existing": True

dev.to/lxstr/comment/2e7gb

I do apologise for all the fault alarms. I appreciate your helps.

Thank you and best regards,

...behai.

P.S.

Just a side note, my application has a view session functionality for admins, whereby it just displays existing sessions as JSON. I was using pickle to serialise the session data. Flask-Session 0.8.0 now uses msgspec, the serialisation of sessions.data is now:

data = msgspec.msgpack.Decoder().decode(<value of sessions.data>)
Enter fullscreen mode Exit fullscreen mode
Collapse
 
behainguyen profile image
Be Hai Nguyen •

Hi Lex,

I am sorry for my late reply. Thank you very much. I will have a look at it. I have been working on some other things.

Thank you and best regards,

...behai.

View full discussion (11 comments)

Read next

saint_vandora profile image

Resolving psycopg2.errors.InsufficientPrivilege

FOLASAYO SAMUEL OLAYEMI -

spwoodcock profile image

FastAPI, Pydantic, Psycopg3: the holy trinity for Python web APIs

Sam -

m_sea_bass profile image

Janus 1.3B: A Unified Model for Multimodal Understanding and Generation Tasks

M Sea Bass -

vincenttommi profile image

2. Understanding Django’s Architecture: The MTV Pattern.

Mr #1738 -