Body

Airflow is currently not compatible with SQLAlchemy 2.0 which is about to be released. We need to make a deliberate effort
to support it.

Here are some info to aid in this effort:

• Description of all removed featuers in SQLAlchemy 2.0: https://sqlalche.me/e/b8d9

• The way to see all the deprecations in latest version of SQLAlchemy 1.4 is to set environment variable SQLALCHEMY_WARN_20=1

• How to start it:

  • add SQLALCHEMY_WARN_20=1 to ci.yml at top level
  • look through warnings.txt in tests to investigate the warnings (there will also be errors in the provider imports)
  • when all of them are fixed - remove <2.0 limitation in sqlalchemy in setup.cfg
  • make sure CI is green

Local setup in PyCharm

If you use PyCharm for run tests, you might want to setup SQLALCHEMY_WARN_20=1 for all pytest runs by default

1. Go to Run -> Edit Configurations... -> click on Edit configuration templates...
   
2. In open window select Python Tests -> pytest and put SQLALCHEMY_WARN_20=1 into the environment variable section
   
3. Click Apply, Click OK
4. Thats it, every new pytest run configuration will have this option by default

Known non-compatible with SA 20

Reported in core

• airflow/models/taskinstance.py:1869
  sqlalchemy.exc.RemovedIn20Warning - Using strings to indicate column or relationship paths in loader options is deprecated and will be removed in SQLAlchemy 2.0. Please use the class-bound attribute directly. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/models/taskinstance.py:1871
  sqlalchemy.exc.RemovedIn20Warning - Using strings to indicate column or relationship paths in loader options is deprecated and will be removed in SQLAlchemy 2.0. Please use the class-bound attribute directly. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/www/views.py:845
  sqlalchemy.exc.RemovedIn20Warning - The "columns" argument to Select.with_only_columns(), when referring to a sequence of items, is now passed as a series of positional elements, rather than as a list. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/www/views.py:3455
  sqlalchemy.exc.RemovedIn20Warning - The Row.keys() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Use the namedtuple standard accessor Row._fields, or for full mapping behavior use row._mapping.keys() (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/www/views.py:3455
  sqlalchemy.exc.RemovedIn20Warning - Retrieving row members using strings or other non-integers is deprecated; use row._mapping for a dictionary interface to the row (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/www/views.py:3293
  sqlalchemy.exc.RemovedIn20Warning - The Row.keys() method is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Use the namedtuple standard accessor Row._fields, or for full mapping behavior use row._mapping.keys() (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/www/views.py:3293
  sqlalchemy.exc.RemovedIn20Warning - Retrieving row members using strings or other non-integers is deprecated; use row._mapping for a dictionary interface to the row (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/jobs/scheduler_job_runner.py:1642
  sqlalchemy.exc.RemovedIn20Warning - Using strings to indicate column or relationship paths in loader options is deprecated and will be removed in SQLAlchemy 2.0. Please use the class-bound attribute directly. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/models/trigger.py:141
  sqlalchemy.exc.RemovedIn20Warning - Using strings to indicate column or relationship paths in loader options is deprecated and will be removed in SQLAlchemy 2.0. Please use the class-bound attribute directly. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/utils/db_cleanup.py:200
  sqlalchemy.exc.RemovedIn20Warning - The bind argument for schema methods that invoke SQL against an engine or connection will be required in SQLAlchemy 2.0. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/cli/commands/task_command.py:202
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/models/taskinstance.py:524
  sqlalchemy.exc.RemovedIn20Warning - Using strings to indicate column or relationship paths in loader options is deprecated and will be removed in SQLAlchemy 2.0. Please use the class-bound attribute directly. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

Reported in providers

• airflow/providers/openlineage/utils/sql.py:152
  sqlalchemy.exc.RemovedIn20Warning - The MetaData.bind argument is deprecated and will be removed in SQLAlchemy 2.0. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• airflow/providers/fab/auth_manager/security_manager/override.py:1509
  sqlalchemy.exc.RemovedIn20Warning - "User" object is being merged into a Session along the backref cascade path for relationship "Role.user"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

Reported in tests

• tests/providers/fab/auth_manager/api_endpoints/test_user_schema.py:63
  sqlalchemy.exc.RemovedIn20Warning - "User" object is being merged into a Session along the backref cascade path for relationship "Role.user"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/jobs/test_backfill_job.py:1912
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/models/test_dagrun.py:2090
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/models/test_taskinstance.py:1487
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/ti_deps/deps/test_trigger_rule_dep.py:140
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/utils/test_log_handlers.py:475
  sqlalchemy.exc.RemovedIn20Warning - "Trigger" object is being merged into a Session along the backref cascade path for relationship "TaskInstance.trigger"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/api_experimental/common/test_mark_tasks.py:137
  sqlalchemy.exc.RemovedIn20Warning - The eagerload construct is considered legacy as of the 1.x series of SQLAlchemy and will be removed in 2.0. Please use joinedload(). (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/api_connexion/endpoints/test_task_instance_endpoint.py:190
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/api_connexion/schemas/test_task_instance_schema.py:69
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/api_connexion/schemas/test_task_instance_schema.py:113
  sqlalchemy.exc.RemovedIn20Warning - "TaskInstance" object is being merged into a Session along the backref cascade path for relationship "DagRun.task_instances"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/serialization/test_pydantic_models.py:162
  sqlalchemy.exc.RemovedIn20Warning - "DagScheduleDatasetReference" object is being merged into a Session along the backref cascade path for relationship "DatasetModel.consuming_dags"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

• tests/serialization/test_pydantic_models.py:163
  sqlalchemy.exc.RemovedIn20Warning - "TaskOutletDatasetReference" object is being merged into a Session along the backref cascade path for relationship "DatasetModel.producing_tasks"; in SQLAlchemy 2.0, this reverse cascade will not take place. Set cascade_backrefs to False in either the relationship() or backref() function for the 2.0 behavior; or to set globally for the whole Session, set the future=True flag (Background on this error at: https://sqlalche.me/e/14/s9r1) (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)

Committer

• I acknowledge that I am a maintainer/committer of the Apache Airflow project.