sqlalchemy 2 set in a virtualenv breaks variable access

[tgrandje]

Apache Airflow version

2.10.4

If "Other Airflow 2 version" selected, which one?

No response

What happened?

Note: just adding this to be tracked for future reference (I haven't seen this specific issue with sqlalchemy 2 in the roadmap toward airflow 3). If you feel this is redondant, please just close this issue.

Running airflow 2.10.4 from the docker image with python 3.10.

Setting a virtualenv config with "sqlalchemy>2.0.0" breaks Variable retrieval (but otherwise works fine).

What you think should happen instead?

Up to now, I had'nt seen side effects linked to using sqlalchemy 2 in virtualenv (that's even something recommented on some SO's answers like here). Maybe that's just something that needs to be displayed explicitly until airflow 3 is out?

How to reproduce

Dag example:

from airflow.decorators import dag, task


@dag()
def sqlalchemy_test():

    from airflow.models import Variable

    Variable.set("eggs", "spam")

    @task
    def get_variable():
        from airflow.models import Variable

        print(Variable.get("eggs"))

    @task.virtualenv(requirements=["sqlalchemy>2.0.0"])
    def get_variable2():
        from airflow.models import Variable

        print(Variable.get("eggs"))

    get_variable()
    get_variable2()


sqlalchemy_test()

Operating System

Ubuntu 22.04.3 LTS

Versions of Apache Airflow Providers

No response

Deployment

Docker-Compose

Deployment details

Docker image derived from apache/airflow:2.10.4-python3.10

Anything else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[boring-cyborg[bot]]

Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.

[eladkal]

Please share also traceback so we can see the error

[eladkal]

Ah OK I get your issue. it's not about virtual env it's about variables.

You are doing it wrong. You can not do Variable.get() inside virtual env. You should use op_args / op_kwargs and pass the variable to the environment using Jinja macro {{ var.value.<variable_name> }}

[tgrandje]

get_variable2's full log. The first errors seems to be linked to the usage of sqlalchemy 2 (failed to import plugin openlinage and the like). I'm not sure there's much usable traceback in the final error, though...

[2025-01-28, 10:20:20 CET] {local_task_job_runner.py:123} ▶ Pre task execution logs
[2025-01-28, 10:20:20 CET] {process_utils.py:186} INFO - Executing cmd: /home/airflow/.local/bin/python -m virtualenv /tmp/venvjh12i7vg --system-site-packages --python=python
[2025-01-28, 10:20:20 CET] {process_utils.py:190} INFO - Output:
[2025-01-28, 10:20:21 CET] {process_utils.py:194} INFO - created virtual environment CPython3.10.16.final.0-64 in 214ms
[2025-01-28, 10:20:21 CET] {process_utils.py:194} INFO -   creator CPython3Posix(dest=/tmp/venvjh12i7vg, clear=False, no_vcs_ignore=False, global=True)
[2025-01-28, 10:20:21 CET] {process_utils.py:194} INFO -   seeder FromAppData(download=False, pip=bundle, setuptools=bundle, wheel=bundle, via=copy, app_data_dir=/home/airflow/.local/share/virtualenv)
[2025-01-28, 10:20:21 CET] {process_utils.py:194} INFO -     added seed packages: pip==24.3.1, setuptools==75.6.0, wheel==0.45.1
[2025-01-28, 10:20:21 CET] {process_utils.py:194} INFO -   activators BashActivator,CShellActivator,FishActivator,NushellActivator,PowerShellActivator,PythonActivator
[2025-01-28, 10:20:21 CET] {process_utils.py:186} INFO - Executing cmd: /tmp/venvjh12i7vg/bin/pip install -r /tmp/venvjh12i7vg/requirements.txt
[2025-01-28, 10:20:21 CET] {process_utils.py:190} INFO - Output:
[2025-01-28, 10:20:22 CET] {process_utils.py:194} INFO - Collecting sqlalchemy>2.0.0 (from -r /tmp/venvjh12i7vg/requirements.txt (line 1))
[2025-01-28, 10:20:22 CET] {process_utils.py:194} INFO -   Using cached SQLAlchemy-2.0.37-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (9.6 kB)
[2025-01-28, 10:20:22 CET] {process_utils.py:194} INFO - Requirement already satisfied: greenlet!=0.4.17 in /home/airflow/.local/lib/python3.10/site-packages (from sqlalchemy>2.0.0->-r /tmp/venvjh12i7vg/requirements.txt (line 1)) (3.1.1)
[2025-01-28, 10:20:22 CET] {process_utils.py:194} INFO - Requirement already satisfied: typing-extensions>=4.6.0 in /home/airflow/.local/lib/python3.10/site-packages (from sqlalchemy>2.0.0->-r /tmp/venvjh12i7vg/requirements.txt (line 1)) (4.12.2)
[2025-01-28, 10:20:22 CET] {process_utils.py:194} INFO - Using cached SQLAlchemy-2.0.37-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.1 MB)
[2025-01-28, 10:20:23 CET] {process_utils.py:194} INFO - Installing collected packages: sqlalchemy
[2025-01-28, 10:20:23 CET] {process_utils.py:194} INFO -   Attempting uninstall: sqlalchemy
[2025-01-28, 10:20:23 CET] {process_utils.py:194} INFO -     Found existing installation: SQLAlchemy 1.4.54
[2025-01-28, 10:20:23 CET] {process_utils.py:194} INFO -     Not uninstalling sqlalchemy at /home/airflow/.local/lib/python3.10/site-packages, outside environment /tmp/venvjh12i7vg
[2025-01-28, 10:20:23 CET] {process_utils.py:194} INFO -     Can't uninstall 'SQLAlchemy'. No files were found to uninstall.
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - flask-appbuilder 4.5.2 requires SQLAlchemy<1.5, but you have sqlalchemy 2.0.37 which is incompatible.
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - apache-airflow 2.10.4 requires sqlalchemy<2.0,>=1.4.36, but you have sqlalchemy 2.0.37 which is incompatible.
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - marshmallow-sqlalchemy 0.28.2 requires SQLAlchemy<2.0,>=1.3.0, but you have sqlalchemy 2.0.37 which is incompatible.
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - Successfully installed sqlalchemy-2.0.37
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - 
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - [notice] A new release of pip is available: 24.3.1 -> 25.0
[2025-01-28, 10:20:24 CET] {process_utils.py:194} INFO - [notice] To update, run: python -m pip install --upgrade pip
[2025-01-28, 10:20:24 CET] {process_utils.py:186} INFO - Executing cmd: /tmp/venvjh12i7vg/bin/python /tmp/venv-calld1zaldnp/script.py /tmp/venv-calld1zaldnp/script.in /tmp/venv-calld1zaldnp/script.out /tmp/venv-calld1zaldnp/string_args.txt /tmp/venv-calld1zaldnp/termination.log
[2025-01-28, 10:20:24 CET] {process_utils.py:190} INFO - Output:
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - [2025-01-28T10:20:25.517+0100] {plugins_manager.py:266} ERROR - Failed to import plugin openlineage
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - Traceback (most recent call last):
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/plugins_manager.py", line 258, in load_entrypoint_plugins
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     plugin_class = entry_point.load()
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/importlib_metadata/__init__.py", line 211, in load
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     module = import_module(match.group('module'))
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _bootstrap._gcd_import(name[level:], package, level)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap_external>", line 883, in exec_module
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/providers/openlineage/plugins/openlineage.py", line 21, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.providers.openlineage.plugins.listener import get_openlineage_listener
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/providers/openlineage/plugins/listener.py", line 30, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.models import DagRun
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1075, in _handle_fromlist
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/__init__.py", line 79, in __getattr__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     val = import_string(f"{path}.{name}")
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/module_loading.py", line 39, in import_string
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     module = import_module(module_path)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _bootstrap._gcd_import(name[level:], package, level)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/dagrun.py", line 60, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.models.taskinstance import TaskInstance as TI
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/taskinstance.py", line 1799, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     class TaskInstance(Base, LoggingMixin):
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_api.py", line 198, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     _as_declarative(reg, cls, dict_)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 244, in _as_declarative
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 325, in setup_mapping
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _ClassScanMapperConfig(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 561, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     self._scan_attributes()
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 1021, in _scan_attributes
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     collected_annotation = self._collect_annotation(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 1303, in _collect_annotation
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     extracted = _extract_mapped_subtype(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/util.py", line 2365, in _extract_mapped_subtype
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     raise sa_exc.ArgumentError(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - sqlalchemy.exc.ArgumentError: Type annotation for "TaskInstance.dag_model" can't be correctly interpreted for Annotated Declarative Table form.  ORM annotations should normally make use of the ``Mapped[]`` generic type, or other ORM-compatible generic type, as a container for the actual type, which indicates the intent that the attribute is mapped. Class variables that are not intended to be mapped by the ORM should use ClassVar[].  To allow Annotated Declarative to disregard legacy annotations which don't use Mapped[] to pass, set "__allow_unmapped__ = True" on the class or a superclass this class. (Background on this error at: https://sqlalche.me/e/20/zlpr)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - [2025-01-28T10:20:25.607+0100] {variable.py:357} ERROR - Unable to retrieve variable from secrets backend (MetastoreBackend). Checking subsequent secrets backend.
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - Traceback (most recent call last):
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/variable.py", line 353, in get_variable_from_secrets
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     var_val = secrets_backend.get_variable(key=key)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/session.py", line 97, in wrapper
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return func(*args, session=session, **kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/secrets/metastore.py", line 66, in get_variable
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return MetastoreBackend._fetch_variable(key=key, session=session)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/api_internal/internal_api_call.py", line 166, in wrapper
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return func(*args, **kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/session.py", line 94, in wrapper
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return func(*args, **kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/secrets/metastore.py", line 84, in _fetch_variable
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     var_value = session.scalar(select(Variable).where(Variable.key == key).limit(1))
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2413, in scalar
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return self._execute_internal(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2251, in _execute_internal
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     result: Result[Any] = compile_state_cls.orm_execute_statement(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 305, in orm_execute_statement
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     result = conn.execute(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return meth(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return connection._execute_clauseelement(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 703, in _compile_w_cache
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     compiled_sql = self._compiler(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 316, in _compiler
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return dialect.statement_compiler(dialect, self, **kw)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 1429, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     Compiled.__init__(self, dialect, statement, **kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 870, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     self.string = self.process(self.statement, **compile_kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 915, in process
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return obj._compiler_dispatch(self, **kwargs)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 141, in _compiler_dispatch
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return meth(self, **kw)  # type: ignore  # noqa: E501
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 4680, in visit_select
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     compile_state = select_stmt._compile_state_factory(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 683, in create_for_statement
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return klass.create_for_statement(statement, compiler, **kw)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 1110, in create_for_statement
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     _QueryEntity.to_compile_state(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 2565, in to_compile_state
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     _MapperEntity(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/context.py", line 2645, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     entity._post_inspect
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1257, in __get__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     obj.__dict__[self.__name__] = result = self.fget(obj)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 2724, in _post_inspect
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     self._check_configure()
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 2401, in _check_configure
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     _configure_registries({self.registry}, cascade=True)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/mapper.py", line 4207, in _configure_registries
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     Mapper.dispatch._for_class(Mapper).before_configured()  # type: ignore # noqa: E501
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/event/attr.py", line 378, in __call__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     fn(*args, **kw)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/events.py", line 893, in wrap
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     fn(*arg, **kw)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 1916, in go
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return once_fn(*arg, **kw)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/__init__.py", line 60, in import_all_models
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     __getattr__(name)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/__init__.py", line 79, in __getattr__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     val = import_string(f"{path}.{name}")
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/module_loading.py", line 39, in import_string
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     module = import_module(module_path)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/usr/local/lib/python3.10/importlib/__init__.py", line 126, in import_module
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _bootstrap._gcd_import(name[level:], package, level)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1050, in _gcd_import
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1027, in _find_and_load
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 1006, in _find_and_load_unlocked
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 688, in _load_unlocked
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap_external>", line 883, in exec_module
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/dag.py", line 103, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.models.baseoperator import BaseOperator
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/baseoperator.py", line 83, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.models.mappedoperator import OperatorPartial, validate_mapping_kwargs
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/mappedoperator.py", line 54, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.triggers.base import StartTriggerArgs
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/triggers/base.py", line 27, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     from airflow.models.taskinstance import SimpleTaskInstance
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/taskinstance.py", line 1799, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     class TaskInstance(Base, LoggingMixin):
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_api.py", line 198, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     _as_declarative(reg, cls, dict_)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 244, in _as_declarative
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 325, in setup_mapping
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     return _ClassScanMapperConfig(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 561, in __init__
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     self._scan_attributes()
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 1021, in _scan_attributes
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     collected_annotation = self._collect_annotation(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py", line 1303, in _collect_annotation
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     extracted = _extract_mapped_subtype(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venvjh12i7vg/lib/python3.10/site-packages/sqlalchemy/orm/util.py", line 2365, in _extract_mapped_subtype
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     raise sa_exc.ArgumentError(
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - sqlalchemy.exc.ArgumentError: Type annotation for "TaskInstance.dag_model" can't be correctly interpreted for Annotated Declarative Table form.  ORM annotations should normally make use of the ``Mapped[]`` generic type, or other ORM-compatible generic type, as a container for the actual type, which indicates the intent that the attribute is mapped. Class variables that are not intended to be mapped by the ORM should use ClassVar[].  To allow Annotated Declarative to disregard legacy annotations which don't use Mapped[] to pass, set "__allow_unmapped__ = True" on the class or a superclass this class. (Background on this error at: https://sqlalche.me/e/20/zlpr)
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - Traceback (most recent call last):
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venv-calld1zaldnp/script.py", line 48, in <module>
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     res = get_variable2(*arg_dict["args"], **arg_dict["kwargs"])
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/tmp/venv-calld1zaldnp/script.py", line 21, in get_variable2
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     print(Variable.get("eggs"))
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -   File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/variable.py", line 145, in get
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO -     raise KeyError(f"Variable {key} does not exist")
[2025-01-28, 10:20:25 CET] {process_utils.py:194} INFO - KeyError: 'Variable eggs does not exist'
[2025-01-28, 10:20:25 CET] {taskinstance.py:3311} ERROR - Task failed with exception
Traceback (most recent call last):
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/taskinstance.py", line 767, in _execute_task
    result = _execute_callable(context=context, **execute_callable_kwargs)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/taskinstance.py", line 733, in _execute_callable
    return ExecutionCallableRunner(
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/utils/operator_helpers.py", line 252, in run
    return self.func(*args, **kwargs)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/baseoperator.py", line 422, in wrapper
    return func(self, *args, **kwargs)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/decorators/base.py", line 266, in execute
    return_value = super().execute(context)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/baseoperator.py", line 422, in wrapper
    return func(self, *args, **kwargs)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/operators/python.py", line 505, in execute
    return super().execute(context=serializable_context)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/models/baseoperator.py", line 422, in wrapper
    return func(self, *args, **kwargs)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/operators/python.py", line 238, in execute
    return_value = self.execute_callable()
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/operators/python.py", line 870, in execute_callable
    result = self._execute_python_callable_in_subprocess(python_path)
  File "/home/airflow/.local/lib/python3.10/site-packages/airflow/operators/python.py", line 588, in _execute_python_callable_
```in_subprocess
    raise AirflowException(error_msg) from None
airflow.exceptions.AirflowException: Process returned non-zero exit status 1.
'Variable eggs does not exist'
[2025-01-28, 10:20:25 CET] {taskinstance.py:1225} INFO - Marking task as FAILED. dag_id=sqlalchemy_test, task_id=get_variable2, run_id=manual__2025-01-28T09:20:19.722028+00:00, execution_date=20250128T092019, start_date=20250128T092020, end_date=20250128T092025
[2025-01-28, 10:20:25 CET] {taskinstance.py:340} ▶ Post task execution logs

[tgrandje]

Ah OK I get your issue. it's not about virtual env it's about variables.

You are doing it wrong. You can not do Variable.get() inside virtual env. You should use op_args / op_kwargs and pass the variable to the environment using Jinja macro {{ var.value.<variable_name> }}

@eladkal You sure about that? I hadn't had any trouble up to now. For instance, that dag will run get_variable3 just fine:

from airflow.decorators import dag, task


@dag()
def sqlalchemy_test():

    from airflow.models import Variable

    Variable.set("eggs", "spam")

    @task
    def get_variable():
        from airflow.models import Variable

        print(Variable.get("eggs"))

    @task.virtualenv(requirements=["sqlalchemy>2.0.0"])
    def get_variable2():
        from airflow.models import Variable

        print(Variable.get("eggs"))

    @task.virtualenv(requirements=["geopandas"])
    def get_variable3():
        from airflow.models import Variable

        print(Variable.get("eggs"))

    get_variable()
    get_variable2()
    get_variable3()


sqlalchemy_test()

[laksh-krishna-sharma]

Hello sir may i work on this issue with a bit of your guidance

[tgrandje]

@laksh-krishna-sharma Sure! Do you need anything beside what I had already posted?

[potiuk]

apache-airflow 2.10.4 requires sqlalchemy<2.0,>=1.4.36, but you have sqlalchemy 2.0.37 which is incompatible.

This is clear I think. Aoache Airflow does not work with sqlalchemy > 2.0 - mainly because some of the dependencies do not work with it.

I thin it's clear because airflow has sqlalchemy < 2.0 as requirement.

[tgrandje]

I know that (I think I stated this first hand with the reference to the airflow 3 roadmap) ; that's even the reason why I had'nt posted the traceback in the first place.

I still this needs to be referenced as :

• using a virtualenv with sqlalchemy > 2 in airflow it currently the only way to work with recent data libraries (like polars), and that's going to stay as it is until airflow 3 is released ;
• we have unconsistent behaviour, as variables set with the os.environ["AIRFLOW_VAR_..."] are accessible (is this through the SecretCache?). Again, I know this is by design, but I still think this info is needed.

For instance, that dag works just fine:

from airflow.decorators import dag, task


@dag()
def sqlalchemy_test():

    import os

    os.environ["AIRFLOW_VAR_BACON"] = "sausage"

    @task
    def get_variable():
        from airflow.models import Variable

        print(Variable.get("bacon"))

    @task.virtualenv(requirements=["sqlalchemy>2.0.0"])
    def get_variable2():
        from airflow.models import Variable

        print(Variable.get("bacon"))

    get_variable()
    get_variable2()


sqlalchemy_test()

[potiuk]

Again, I know this is by design, but I still think this info is needed.

Feel free to propose a change to the docs where you think it's missing - or more accurately - where you would look for it in the docs. It's as easy as clicking "Suggest a change on this page" button at bottom right of the page where you think it should be added - and PR will be opened for you. That would be great to make it clearer for peoople like you and likely you know already best how to phrase it.

[tgrandje]

Done (better late than never 😳 ...): #47914
Closing this discussion (for now?).

[potiuk]

Converting into a discussion.