Remove deprecated Query usage and update to SQLAlchemy2.0 style

[Prab-27]

Description

The Query object is deprecated in SQLAlchemy 2.0, and we are removing its usage here.
We are updating it to use the new SQLAlchemy 2.0 style.

airfow-core/src

• airflow-core/src/airflow/api_fastapi
• airflow-core/src/airflow/dag_processing
• airflow-core/src/airflow/jobs
• airflow-core/src/airflow/migrations
• airflow-core/src/airflow/utils

airflow-core/tests

• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_assets.py - Remove Legacy Query Object Usage from test_assets.py #59412
• airflow-core/tests/unit/dag_processing/bundles/test_dag_bundle_manager.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_variables.py
• airflow-core/tests/unit/api_fastapi/execution_api/versions/head/test_dag_runs.py.
• airflow-core/tests/unit/cli/commands/test_task_command.py.
• airflow-core/tests/unit/models/test_serialized_dag.py
• airflow-core/tests/unit/models/test_variable.py.
• airflow-core/tests/unit/models/test_dagrun.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_pools.py
• airflow-core/tests/unit/utils/test_log_handlers.py
• airflow-core/tests/unit/models/test_cleartasks.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/ui/test_structure.py
• airflow-core/tests/unit/models/test_deadline.py
• airflow-core/tests/unit/cli/commands/test_rotate_fernet_key_command.py
• airflow-core/tests/unit/dag_processing/test_processor.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/ui/test_assets.py
• airflow-core/tests/unit/cli/commands/test_dag_command.py
• airflow-core/tests/unit/models/test_taskinstance.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_hitl.py
• airflow-core/tests/unit/api_fastapi/execution_api/versions/head/test_xcoms.py
• airflow-core/tests/unit/models/test_pool.py
• airflow-core/tests/unit/models/test_callback.py
• airflow-core/tests/unit/utils/test_state.py
• airflow-core/tests/unit/models/test_dagcode.py
• airflow-core/tests/unit/ti_deps/deps/test_not_previously_skipped_dep.py
• airflow-core/tests/unit/cli/commands/test_team_command.py
• airflow-core/tests/unit/utils/test_types.py
• airflow-core/tests/unit/models/test_mappedoperator.py
• airflow-core/tests/unit/api_fastapi/execution_api/versions/head/test_variables.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_task_instances.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_backfills.py
• airflow-core/tests/unit/dag_processing/test_manager.py
• airflow-core/tests/unit/models/test_trigger.py
• airflow-core/tests/unit/jobs/test_scheduler_job.py
• airflow-core/tests/unit/models/test_xcom.py
• airflow-core/tests/unit/cli/commands/test_connection_command.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_dag_sources.py
• airflow-core/tests/unit/cli/commands/test_pool_command.py
• airflow-core/tests/unit/models/test_dag.py
• airflow-core/tests/unit/utils/test_db_cleanup.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_connections.py
• airflow-core/tests/unit/dag_processing/test_collection.py
• airflow-core/tests/unit/models/test_dagwarning.py
• airflow-core/tests/unit/ti_deps/deps/test_runnable_exec_date_dep.py
• airflow-core/tests/integration/otel/test_otel.py
• airflow-core/tests/unit/api_fastapi/core_api/routes/public/test_dag_run.py
• airflow-core/tests/unit/assets/test_manager.py
• airflow-core/tests/unit/models/test_renderedtifields.py
• in airflow-core/tests/unit/timetables/test_assets_timetable.py
• airflow-core/tests/unit/api_fastapi/execution_api/versions/head/test_task_instances.py
• airflow-core/tests/unit/utils/test_cli_util.py
• airflow-core/tests/unit/models/test_timestamp.py

dev/

• dev/airflow_perf/scheduler_dag_execution_timing.py

devel-common/

• devel-common/src/tests_common/pytest_plugin.py
• devel-common/src/tests_common/test_utils/logs.py
• devel-common/src/tests_common/test_utils/api_client_helpers.py
• devel-common/src/tests_common/test_utils/otel_utils.py
• devel-common/src/tests_common/test_utils/api_fastapi.py
• devel-common/src/tests_common/test_utils/db

providers/

• arangodb
• celery
• cncf/kubernetes
• databricks
• edge3 - Make Edge provider SQLA2 compatible #59414
• mysql
• openlineage
• standard

Use case/motivation

The process of doing it is rather easy:

1. Add the module or file (where you want to work on it) to the prevent-deprecated-sqlalchemy-usage hook in the .pre-commit-config.yaml file.
   Please Add them in between the files entries, and please ensure to add a | at the end of the expression.
   If I’m working on a file like devel-common/src/tests_common/pytest_plugin.py or on a module like airflow-core/src/airflow/jobs, I’ll add it like this

 - id: prevent-deprecated-sqlalchemy-usage
        name: Prevent deprecated sqlalchemy usage
        entry: ./scripts/ci/prek/prevent_deprecated_sqlalchemy_usage.py
        language: python
        files: >
            (?x)
            ^airflow-ctl.*\.py$|
            ^airflow-core/src/airflow/jobs/.*\.py$|
            ^devel-common/src/tests_common/pytest_plugin.py$|
            ^airflow-ctl/tests/.*\.py$|

2. Stage your changes and then run the hook :
   If working on a file, run:
   prek prevent-deprecated-sqlalchemy-usage --files devel-common/src/tests_common/pytest_plugin.py
   If working on a module, run :
   prek prevent-deprecated-sqlalchemy-usage -d airflow-core/src/airflow/jobs/

3. The hook will show failed checks and indicate where the deprecated Query is used.
   A failed hook shows like this :

Prevent deprecated sqlalchemy usage......................................Failed
- hook id: prevent-deprecated-sqlalchemy-usage
- exit code: 1
  Deprecated query-obj found at line 2433 in airflow-core/src/airflow/jobs/scheduler_job_runner.py.
  Deprecated query-obj found at line 2453 in airflow-core/src/airflow/jobs/scheduler_job_runner.py.
  Please update SQLAlchemy 2.0 style.

4. Please update the queries to follow the SQLAlchemy 2.0 style. Make the necessary changes where needed and commit the changes...

5. After applying the new changes, please also check whether these changes create any new mypy errors (related to this issue Sqlalchemy 2 migration - Fix MyPy type errors in core Airflow #56735 for airflow-core and for providers Sqlalchemy 2 migration - Fix MyPy type errors in providers #56738). Verification for dev and devel-common is not required
   Please verify it locally by following the steps described in the issue (and mentioned here as well).

• Apply the changes from PR Remove SQLA 1 limit in Fab provider #56212 locally.
• Run : breeze ci-image build --python 3.10 --upgrade-to-newer-dependencies
• For airflow-core, run : prek --color always --verbose --hook-stage manual "mypy-airflow-core" --all-files
• For provider, run : prek --color always --verbose --hook-stage manual "mypy-providers" --all-files
  If any new mypy errors are generated, fix them locally. Be sure not to commit the file from PR Remove SQLA 1 limit in Fab provider #56212.

6. Please raise a draft PR. If checks fail, read the instructions in the failed checks and make the necessary changes until all checks pass successfully.
   Once all checks are green, convert the PR into a Ready for Review PR.
   Ensure the code changes and the pre-commit hook changes (added module or file) are included in the same PR

Related issues

session.query() returns a deprecated Query object, so most uses of the deprecated Query are in this pattern.

Helpful documentation : here

I have attempted to remove the deprecated Query part from airflow-core/src/airflow/models PR: #52325 and from the fab provider PR: #53953.

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

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

[henry3260]

Thanks for opening this!!!

[choo121600]

I think this is good first issue :)
Thank you for opening this issue🙌