DEV Community

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

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

Be Hai Nguyen on November 24, 2022

During tests, Flask-Session intermittently causes the exception “sqlalchemy.exc.InvalidRequestError: Table 'sessions' is already defined for this M...
Read full post
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.