No such event 'before_execute' for target '<sqlalchemy.orm.session.Session object ...

[pliniozanini]

Describe the bug Description of what the bug is.

Environment

• Ubuntu 22.04.1 LTS
• 3.9
• venv

To Reproduce Trying to reproduce the example given

tests/test.temp.py:

# Redshift Example:
from pytest_mock_resources import create_redshift_fixture
from package.utilities import sql_sum

db = create_redshift_fixture()
# or
db = create_redshift_fixture(session=True)

def test_sql_sum(db):
   sql_sum(db)


# Postgres Example:
from pytest_mock_resources import create_postgres_fixture
from package.utilities import sql_sum

db = create_postgres_fixture()
# or
db = create_postgres_fixture(session=True)

def test_sql_sum(db):
   sql_sum(db)

src/util/temp.py:

# Redshift Example:
from pytest_mock_resources import create_redshift_fixture
from  util.temp import sql_sum

db = create_redshift_fixture()
# or
db = create_redshift_fixture(session=True)

def test_sql_sum(db):
   sql_sum(db)

Expected behavior Test to be concluded successfully

Actual Behavior

==================================================================== test session starts =====================================================================
platform linux -- Python 3.9.12, pytest-6.2.5, py-1.11.0, pluggy-1.0.0
rootdir: /home/zanini/repo/RecSys, configfile: tox.ini
plugins: mock-resources-2.6.0, xdist-1.34.0, postgresql-4.1.1, cov-2.12.1, forked-1.4.0, anyio-3.6.2
collected 1 item                                                                                                                                             

tests/test_temp.py E                                                                                                                                   [100%]

=========================================================================== ERRORS ===========================================================================
_______________________________________________________________ ERROR at setup of test_sql_sum _______________________________________________________________

pmr_redshift_container = python_on_whales.Container(id='c2e8218e3a27', name='pmr_redshift_5532')
pmr_redshift_config = RedshiftConfig(username='user', image='postgres:9.6.10-alpine', port=5532, root_database='dev', ci_port=5432, host='localhost', password='password')

    @pytest.fixture(scope=scope)
    def _sync(pmr_redshift_container, pmr_redshift_config):
        engine_manager = _create_engine_manager(pmr_redshift_config)
        database_name = engine_manager.engine.url.database
    
        for engine in engine_manager.manage_sync():
>           sqlalchemy.register_redshift_behavior(engine)

../.venv/lib/python3.9/site-packages/pytest_mock_resources/fixture/redshift/__init__.py:101: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../.venv/lib/python3.9/site-packages/pytest_mock_resources/patch/redshift/sqlalchemy.py:14: in register_redshift_behavior
    event.listen(engine, "before_execute", receive_before_execute, retval=True)
../.venv/lib/python3.9/site-packages/sqlalchemy/event/api.py:115: in listen
    _event_key(target, identifier, fn).listen(*args, **kw)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

target = <sqlalchemy.orm.session.Session object at 0x7f8c1050e6a0>, identifier = 'before_execute', fn = <function receive_before_execute at 0x7f8c11de7ee0>

    def _event_key(target, identifier, fn):
        for evt_cls in _registrars[identifier]:
            tgt = evt_cls._accept_with(target)
            if tgt is not None:
                return _EventKey(target, identifier, fn, tgt)
        else:
>           raise exc.InvalidRequestError(
                "No such event '%s' for target '%s'" % (identifier, target)
            )
E           sqlalchemy.exc.InvalidRequestError: No such event 'before_execute' for target '<sqlalchemy.orm.session.Session object at 0x7f8c1050e6a0>'

../.venv/lib/python3.9/site-packages/sqlalchemy/event/api.py:29: InvalidRequestError
================================================================== short test summary info ===================================================================
ERROR tests/test_temp.py::test_sql_sum - sqlalchemy.exc.InvalidRequestError: No such event 'before_execute' for target '<sqlalchemy.orm.session.Session obj...
====================================================================== 1 error in 7.82s ======================================================================

Additional context I managed to fix the issue by noticing the event "before_execute" and the event in the following call "before_cursor_execute" are core events the should be called by a connection.

Changing register_redshift_behavior in pytest_mock_resources/patch/redshift/sqlalchemy.py

def register_redshift_behavior(engine):
    """Substitute the default execute method with a custom execute for copy and unload command."""

    event.listen(engine, "before_execute", receive_before_execute, retval=True)
    event.listen(engine, "before_cursor_execute", receive_before_cursor_execute, retval=True)

to

def register_redshift_behavior(engine):
   """Substitute the default execute method with a custom execute for copy and unload command."""

   event.listen(engine.connection(), "before_execute", receive_before_execute, retval=True)
   event.listen(engine.connection(), "before_cursor_execute", receive_before_cursor_execute, retval=True)

solved the problem, since it was passing a sqlalchemy.orm.session.Session object instead of a connection one

Not sure if that was due to some issues on my system, since that is the main code I assume other have previously used with no problem.

[DanCardin]

Released fix for this in 2.6.1. Thanks for the report!

[pliniozanini]

Thank you, @DanCardin, for the quick response!

However, I noticed we still have a problem when trying to use the same example: When using db = create_redshift_fixture(session=True), it works fine, nonetheless, when using db = create_redshift_fixture(session=False), the following error appears:

============================================================ test session starts =============================================================
platform linux -- Python 3.9.12, pytest-6.2.5, py-1.11.0, pluggy-1.0.0 -- /home/zanini/repo/RecSys/.venv/bin/python
cachedir: .pytest_cache
rootdir: /home/zanini/repo/RecSys, configfile: tox.ini
plugins: mock-resources-2.6.1, mock-3.10.0, xdist-1.34.0, postgresql-4.1.1, cov-2.12.1, forked-1.4.0, anyio-3.6.2
collected 1 item                                                                                                                             

tests/test_temp.py::test_sql_sum === <class 'sqlalchemy.engine.base.Engine'>
FAILED

================================================================== FAILURES ==================================================================
________________________________________________________________ test_sql_sum ________________________________________________________________

db = Engine(postgresql+psycopg2://user:***@localhost:5532/pytest_mock_resource_db_1?sslmode=disable)

    def test_sql_sum(db):
>       sql_sum(db)

tests/test_temp.py:14: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
util/temp.py:5: in sql_sum
    redshift_conn.execute("CREATE TEMP TABLE mytemp(c INT);")
<string>:2: in execute
    ???
../.venv/lib/python3.9/site-packages/sqlalchemy/util/deprecations.py:402: in warned
    return fn(*args, **kwargs)
../.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py:3257: in execute
    return connection.execute(statement, *multiparams, **params)
../.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1365: in execute
    return self._exec_driver_sql(
../.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1669: in _exec_driver_sql
    ret = self._execute_context(
../.venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1842: in _execute_context
    statement, parameters = fn(
../.venv/lib/python3.9/site-packages/pytest_mock_resources/patch/redshift/sqlalchemy.py:54: in receive_before_cursor_execute
    normalized_statement = strip(statement).lower()
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

input_string = <sqlalchemy.sql.elements.TextClause object at 0x7fd3db9461f0>

    def strip(input_string):
        """Strip trailing whitespace, single/double quotes."""
>       return input_string.strip().rstrip(";").strip('"').strip("'")
E       AttributeError: 'TextClause' object has no attribute 'strip'

../.venv/lib/python3.9/site-packages/pytest_mock_resources/patch/redshift/mock_s3_copy.py:189: AttributeError
========================================================== short test summary info ===========================================================
FAILED tests/test_temp.py::test_sql_sum - AttributeError: 'TextClause' object has no attribute 'strip'
============================================================= 1 failed in 8.57s ==============================================================

I noticed the error I reported previously was also exclusive from the session=True case.

[DanCardin]

@pliniozanini I dont suppose you'd mind checking out this branch https://github.com/schireson/pytest-mock-resources/pull/172 and testing to see if it solves your issue? I added tests of both sides and now don't see either issue

[DanCardin]

Just released 2.6.3 which hopefully fixes both directions of issue? We at least have some tests in either direction, so it should be a net-different issue if you're still encountering problems hopefully.

Feel free to keep commenting here if you do, and i can re-open again! Thanks!