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.

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.