Fix all deprecations for SQLAlchemy 2.0 #28723

potiuk · 2023-01-04T10:37:14Z

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

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

Known non-compatible with SA 20

Reported in core

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

Committer

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

The text was updated successfully, but these errors were encountered:

Taragolis · 2023-01-04T13:39:57Z

Just clarification. Our target support both 1.4.x and 2.0 or only 2.0?

potiuk · 2023-01-04T15:01:26Z

Just clarification. Our target support both 1.4.x and 2.0 or only 2.0?

This is a good point, and I do not know the answer. I think it will entirely depend on:

a) when we will do it
b) how difficult it will be to keep compatibility
c) some indication of the adoption of 2.0 once it is out there

I would refrain from trying to figure the answer before the final 2.0 release is out there - and possibly we should even wait for at least 2.0.1 or so, generally we should scout for stability indications. And then we should attempt to migrate and see how difficult it is to keep it compatible with both.

If we will decide to support both, due to many differences and generally backwards incompatible release of sqlalchemy I would be only heppy with supporting both if we extend our test suite in CI and add another test matrix dimention - sqlalchemy_version (on top of backend + backend version, Python version) that we have now.

But we will also have to see if that is really needed, depending on how complex the fixing will be and how different the two versions are. If the differences are small, we can likely get-by without having the extra matrix dimension.

If we see that differences are big, I think going 2.0-only is a better approach (and it basically means we will have to wait at least 6-10 months after release depending on adoption IMHO).

There is absolutely no hurry with migration yet, 1.4 will be out there for a long while and it will be supported with bugfixes - so we do not have (and will not have for a while) particular need to migrate to 2.0.

Taragolis · 2023-01-04T15:44:07Z

Migrate 2.0 to 1.4 potentially could have more effect rather than we have with other packages.
Some of dependency of providers also use SQLAlchemy and we need to wait until them migrate to 2.0

Current packages which explicit have SQLAlchemy as dependency from CI image.

root@6e6ad04bdb9b:/opt/airflow# pipdeptree --reverse --packages SQLAlchemy --python `which python`

sqlalchemy==1.4.46
  - alembic==1.9.1 [requires: SQLAlchemy>=1.3.0]
    - apache-airflow==2.6.0.dev0 [requires: alembic>=1.6.3,<2.0]
  - apache-airflow==2.6.0.dev0 [requires: sqlalchemy>=1.4,<2.0]
  - elasticsearch-dbapi==0.2.9 [requires: sqlalchemy]
  - eralchemy2==1.3.6 [requires: SQLAlchemy>=1.3.19]
  - Flask-AppBuilder==4.1.4 [requires: SQLAlchemy<1.5]
    - apache-airflow==2.6.0.dev0 [requires: flask-appbuilder==4.1.4]
  - Flask-SQLAlchemy==2.5.1 [requires: SQLAlchemy>=0.8.0]
    - Flask-AppBuilder==4.1.4 [requires: Flask-SQLAlchemy>=2.4,<3]
      - apache-airflow==2.6.0.dev0 [requires: flask-appbuilder==4.1.4]
  - marshmallow-sqlalchemy==0.26.1 [requires: SQLAlchemy>=1.2.0]
    - Flask-AppBuilder==4.1.4 [requires: marshmallow-sqlalchemy>=0.22.0,<0.27.0]
      - apache-airflow==2.6.0.dev0 [requires: flask-appbuilder==4.1.4]
  - snowflake-sqlalchemy==1.4.4 [requires: sqlalchemy>=1.4.0,<2.0.0]
  - sqlalchemy-bigquery==1.5.0 [requires: sqlalchemy>=1.2.0,<2.0.0dev]
  - sqlalchemy-drill==1.1.2 [requires: sqlalchemy]
  - SQLAlchemy-JSONField==1.0.1.post0 [requires: sqlalchemy]
    - apache-airflow==2.6.0.dev0 [requires: sqlalchemy-jsonfield>=1.0]
  - sqlalchemy-redshift==0.8.12 [requires: SQLAlchemy>=0.9.2,<2.0.0]
  - SQLAlchemy-Utils==0.39.0 [requires: SQLAlchemy>=1.3]
    - Flask-AppBuilder==4.1.4 [requires: sqlalchemy-utils>=0.32.21,<1]
      - apache-airflow==2.6.0.dev0 [requires: flask-appbuilder==4.1.4]

This is not complete list because some of packages could use sqlalchemy but do not have it in dependency, for example pandas use it in read_sql.

uranusjr · 2023-01-04T15:57:28Z

I think we should support both 1.4 and 2.0 for at least one minor release, preferrably much longer. Dependencies don’t tend to catch up very fast for this kind of migrations, and an Airflow installation generally has a lot of those.

potiuk · 2023-01-17T09:51:53Z

Agree.

kaxil · 2023-01-28T19:51:40Z

I think we should support both 1.4 and 2.0 for at least one minor release, preferrably much longer. Dependencies don’t tend to catch up very fast for this kind of migrations, and an Airflow installation generally has a lot of those.

Fully agreed, we need to support both

infohash · 2023-03-06T14:06:18Z

What can be the expected version that will bring SQLAlchemy 2.0 support. I am making a service that will use SQLAlchemy 2.0 APIs. I will also integrate Airflow with that service. Based on ETA, I can decide whether to use SQLAlchemy 2.0 or go ahead with SQLAlchemy 1.4 and not wait for the supported version of Airflow.

uranusjr · 2023-03-07T10:10:33Z

It can be the next version if you help out on it 🙂

auvipy · 2023-03-11T08:55:48Z

I want to contribute here

auvipy · 2023-03-11T09:08:03Z

SQLA 1.4 & 2.0 both can be supported at the same time.

moiseenkov · 2023-07-25T10:14:52Z

Hi everyone,
Do we have some kind of board or issues list that are currently in progress? I see the work is going on, so I would like to help and fix some part that nobody else is working on at the moment?
@potiuk

VladaZakharova · 2023-07-26T08:39:06Z

@kaxil @phanikumv @uranusjr @hussein-awala
Maybe you could help with coordination here? Do you have any plan for the process here?
Thanks!

phanikumv · 2023-07-26T10:10:07Z

Hi @VladaZakharova - we are currently suspending this effort till 2.7 is released. We have already completed the changes, but will keep the PR in draft mode until then. The other pending change is to fix the tests so that they use the sqlalchemy 2.0 style (but again they need to wait till 2.7 is released)

cc @jedcunningham

Taragolis · 2023-12-14T11:42:21Z

Just for the record, fix all stuff do not automatically granted ability to run Airflow with SQLAlchemy 2.0, due to upper bound limitation of Airflow and Providers dependencies:

root@4c41f46e319c:/opt/airflow# pipdeptree --packages sqlalchemy -r
SQLAlchemy==1.4.50
├── alembic==1.13.0 [requires: SQLAlchemy>=1.3.0]
│   ├── apache-airflow==2.9.0.dev0 [requires: alembic>=1.6.3,<2.0]
│   ├── databricks-sql-connector==2.9.3 [requires: alembic>=1.0.11,<2.0.0]
│   └── sqlalchemy-spanner==1.6.2 [requires: alembic]
├── apache-airflow==2.9.0.dev0 [requires: SQLAlchemy>=1.4.28,<2.0]
├── databricks-sql-connector==2.9.3 [requires: SQLAlchemy>=1.3.24,<2.0.0]
├── eralchemy2==1.3.8 [requires: SQLAlchemy>=1.4]
├── Flask-AppBuilder==4.3.10 [requires: SQLAlchemy<1.5]
│   └── apache-airflow==2.9.0.dev0 [requires: Flask-AppBuilder==4.3.10]
├── Flask-SQLAlchemy==2.5.1 [requires: SQLAlchemy>=0.8.0]
│   └── Flask-AppBuilder==4.3.10 [requires: Flask-SQLAlchemy>=2.4,<3]
│       └── apache-airflow==2.9.0.dev0 [requires: Flask-AppBuilder==4.3.10]
├── marshmallow-sqlalchemy==0.26.1 [requires: SQLAlchemy>=1.2.0]
│   └── Flask-AppBuilder==4.3.10 [requires: marshmallow-sqlalchemy>=0.22.0,<0.27.0]
│       └── apache-airflow==2.9.0.dev0 [requires: Flask-AppBuilder==4.3.10]
├── snowflake-sqlalchemy==1.5.1 [requires: SQLAlchemy>=1.4.0,<2.0.0]
├── sqlalchemy-bigquery==1.9.0 [requires: SQLAlchemy>=1.2.0,<2.0.0dev]
├── sqlalchemy-drill==1.1.4 [requires: SQLAlchemy]
├── SQLAlchemy-JSONField==1.0.2 [requires: SQLAlchemy]
│   └── apache-airflow==2.9.0.dev0 [requires: SQLAlchemy-JSONField>=1.0]
├── sqlalchemy-redshift==0.8.14 [requires: SQLAlchemy>=0.9.2,<2.0.0]
├── sqlalchemy-spanner==1.6.2 [requires: SQLAlchemy>=1.1.13]
└── SQLAlchemy-Utils==0.41.1 [requires: SQLAlchemy>=1.3]
    └── Flask-AppBuilder==4.3.10 [requires: SQLAlchemy-Utils>=0.32.21,<1]
        └── apache-airflow==2.9.0.dev0 [requires: Flask-AppBuilder==4.3.10]

Also required that this packages also supports SA 2.0:

Flask-AppBuilder - Doesn't support SA 2.0 yet
databricks-sql-connector - Support SA 2.0 from 3.0.0, and drop support 1.4
snowflake-sqlalchemy - Doesn't support SA 2.0 yet
sqlalchemy-bigquery - Doesn't support SA 2.0 yet
sqlalchemy-redshift - Doesn't support SA 2.0 yet

infohash · 2023-12-15T14:28:14Z

This is why I forked SQLAlchemy 2.0 and put it in my project's source directory instead of installing it as a project dependency. Once airflow ecosystem completely moves to SQLA 2.0, I will just stop using my own fork. All I had to do was change the package name of SQLA 2.0. You can also fork it and rebuild SQLA 2.0 wheel with a different package name.

Dev-iL · 2024-02-18T10:39:49Z

Not a deprecation but probably related to this effort: DagBag cannot be imported with SQLA 2 (sqlite connection):

>>> from airflow.models import DagBag
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/.../.venv/lib64/python3.9/site-packages/airflow/__init__.py", line 68, in <module>
    settings.initialize()
  File "/home/.../.venv/lib64/python3.9/site-packages/airflow/settings.py", line 544, in initialize
    configure_orm()
  File "/home/.../.venv/lib64/python3.9/site-packages/airflow/settings.py", line 242, in configure_orm
    engine = create_engine(SQL_ALCHEMY_CONN, connect_args=connect_args, **engine_args, future=True)
  File "<string>", line 2, in create_engine
  File "/home/.../.venv/lib64/python3.9/site-packages/sqlalchemy/util/deprecations.py", line 281, in warned
    return fn(*args, **kwargs)  # type: ignore[no-any-return]
  File "/home/.../.venv/lib64/python3.9/site-packages/sqlalchemy/engine/create.py", line 686, in create_engine
    raise TypeError(
TypeError: Invalid argument(s) 'encoding' sent to create_engine(), using configuration SQLiteDialect_pysqlite/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

This is due to the removal of the deprecated encoding parameter for SQLA 2.0.

I suppose one way to solve this while maintaining backward compatibility is by wrapping the following in a version check:

    # Allow the user to specify an encoding for their DB otherwise default
    # to utf-8 so jobs & users with non-latin1 characters can still use us.
    engine_args["encoding"] = conf.get("database", "SQL_ENGINE_ENCODING", fallback="utf-8")

BTW, resolving the above results in the following deprecation warning:

/home/.../.venv/lib64/python3.9/site-packages/airflow/utils/orm_event_handlers.py:37 SADeprecationWarning: The `sqlalchemy.orm.mapper()` symbol is deprecated and will be removed in a future release. For the mapper-wide event target, use the 'sqlalchemy.orm.Mapper' class.

Changing the offending line as below, while keeping the import the same, seems to resolve the warning:

-event.listen(sqlalchemy.orm.mapper, "before_configured", import_all_models, once=True)
+event.listen(sqlalchemy.orm.Mapper, "before_configured", import_all_models, once=True)

potiuk · 2024-02-18T21:43:05Z

Why don you make a PR for that @Dev-iL ?

Dev-iL · 2024-02-18T22:09:41Z

@potiuk Sure thing, will ping when ready for review.

VladaZakharova · 2024-04-12T10:05:42Z

Hi Team! How is the progress going in this one? :)

Taragolis · 2024-04-12T22:21:04Z

Potentially if you able to run Airflow without FAB, the airflow potentially could starts with sqlalchemy 2. I’ve analyze Airflow warnings recently and unable to find any sqlalchemy.exc.RemovedIn20Warnings from the Airflow. Only FAB

Taragolis · 2024-04-12T22:27:16Z

Query API at that moment marked as legacy, and do not raise any warnings about future removal and there is become record about that this API will not removed from sqlalchemy

moiseenkov · 2024-04-16T07:46:30Z

Excluding providers (FAB and openlineage) I managed to catch two warnings in the core:

airflow/www/views.py:845: [W0513(warning), ] 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/models/taskinstance.py:1870: [W0513(warning), ] 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)

So, there are only these two places left, and we will have this problem resolved on the Airflow core side.
Is anybody working on this at the moment?

Taragolis · 2024-04-19T10:26:42Z

I've add collect this warning during the CI step, however our warning collection system collect warnings only during run tests, and errors might happen during initial configurations, so this might be other incompatibilities, i would plan to extend our plugin and collect in the other steps but it required some time.

I have add all currently found warnings in Issue detail, so fill free to fix it

dondaum · 2024-04-20T10:35:33Z

Hi. I would like to support. To clarify is it correct that all warnings can be found in this issue detail section from potiuk?

veinkr · 2024-05-05T09:18:26Z

Hi, @dondaum , I saw you said that all RemovedIn20Warning should be fixed in PR #39299 , does that mean we can upgrade the sqlalchemy to 2.0 version safely?

Taragolis · 2024-05-05T10:02:46Z

Not really, see this comment #28723 (comment)

veinkr · 2024-05-05T10:32:51Z

@Taragolis , sorry to bother, I just checked the Flask-AppBuilder repo, it haven't updated since two months ago, and it seems that it blocked by dpgaspar/Flask-AppBuilder#2038 (comment) so that it can't update to flask-sqlalchemy >=3.
Seems that we can't upgrade to sqlalchemy to 2.0 in a short time, do we have other plan for it?
I can see that in previous PR, the pandas have been limited to pandas<2.2, which is a important library for data engineer, if we keep sqlalchemy<2.0, then we will behind too much things.

Taragolis · 2024-05-05T10:42:57Z

The strategic, maybe even since Airflow 2.0, is get rid of FAB in Airflow at all.
It moved into the separate provider since 2.9, however all Authorisation still happen go through the FAB.

There is no ETA when it happen, more possible that in Airflow 3.0 but maybe sooner if someone could propose how we could get rid off FAB "Here and Now"

potiuk added the kind:meta High-level information important to the community label Jan 4, 2023

Taragolis mentioned this issue Jan 27, 2023

SQLA20: Replace Session.query(...).get by Session.get #29205

Merged

eladkal mentioned this issue Mar 6, 2023

Use MyPy support for SQLAlchemy #20484

Open

auvipy mentioned this issue Mar 11, 2023

Attempt to fix deprecation warnings in SQLA 2.0 #30036

Closed

jedcunningham mentioned this issue Apr 7, 2023

Update SQLAlchemy select() to new style #30515

Merged

graham33 mentioned this issue May 14, 2023

Move apache-airflow out of python package set NixOS/nixpkgs#231187

Closed

lwrubel mentioned this issue May 19, 2023

Ensure vendor app's sqlalchemy code will not break when Airflow upgrades to sqlalchemy 2.0 sul-dlss/libsys-airflow#467

Closed

ephraimbuddy mentioned this issue May 23, 2023

Update code style for airflow db commands to SQLAlchemy 2.0 style #31486

Merged

ephraimbuddy self-assigned this May 25, 2023

jmartin-sul mentioned this issue May 31, 2023

[defer till Airflow upgrades to SQLAlchemy 2.0] remove use of the SQLALCHEMY_WARN_20 env var sul-dlss/libsys-airflow#532

Open

This was referenced Jun 27, 2023

Refactor Sqlalchemy queries to 2.0 style (Part 3) #32177

Merged

Refactor Sqlalchemy queries to 2.0 style (Part 4) #32339

Merged

This was referenced Jul 4, 2023

Refactor Sqlalchemy queries to 2.0 style (Part 3) #32350

Merged

Refactor Sqlalchemy queries to 2.0 style (Part 5) #32474

Merged

hussein-awala mentioned this issue Jul 15, 2023

Improve getting the query count in Airflow API endpoints #32630

Merged

phanikumv mentioned this issue Jul 17, 2023

Refactor Sqlalchemy queries to 2.0 style (Part 6) #32645

Merged

moiseenkov mentioned this issue Jul 26, 2023

Refactor SqlAlchemy Session.execute() to 2.0 style #32857

Merged

Dev-iL mentioned this issue Feb 19, 2024

Refrain from passing encoding to the SQL engine in SQLAlchemy v2 #37545

Merged

lgonzalez-silen mentioned this issue Feb 27, 2024

BUG: Pandas 2.2 breaks SQLAlchemy 1.4 compatibility pandas-dev/pandas#57049

Open

3 tasks

Taragolis mentioned this issue Feb 27, 2024

Limit pandas to <2.2 #37748

Merged

hussein-awala mentioned this issue Feb 27, 2024

Migrate XCom model queries to sqlalchemy v2 #37761

Closed

Taragolis mentioned this issue Apr 18, 2024

Enable sqlalchemy.exc.RemovedIn20Warning in breeze testing by default #39121

Merged

Taragolis added the good first issue label Apr 18, 2024

Taragolis mentioned this issue Apr 19, 2024

Use declarative_base from sqlalchemy.orm instead of sqlalchemy.ext.declarative #39134

Merged

This was referenced Apr 23, 2024

Use class-bound attribute directly in SA lazyload #39195

Merged

Use class-bound attribute directly in SA joinedload #39198

Merged

This was referenced Apr 23, 2024

fix: sqa deprecations for airflow core #39211

Merged

fix: sqa deprecations for airflow task cmd #39244

Merged

fix: sqa deprecations for airflow providers #39293

Open

sunchao mentioned this issue Apr 28, 2024

Update Databricks provider to depend on databricks-sql-connector >= 3.1.0 #39274

Open

2 tasks

dondaum mentioned this issue Apr 28, 2024

fix: sqa deprecations for airflow tests #39299

Merged

12 tasks

amindadgar mentioned this issue May 14, 2024

Very old sqlalchemy library dpgaspar/Flask-AppBuilder#2240

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Fix all deprecations for SQLAlchemy 2.0 #28723

Fix all deprecations for SQLAlchemy 2.0 #28723

potiuk commented Jan 4, 2023 •

edited by Taragolis

Taragolis commented Jan 4, 2023

potiuk commented Jan 4, 2023 •

edited

Taragolis commented Jan 4, 2023

uranusjr commented Jan 4, 2023

potiuk commented Jan 17, 2023

kaxil commented Jan 28, 2023

infohash commented Mar 6, 2023 •

edited

uranusjr commented Mar 7, 2023

auvipy commented Mar 11, 2023

auvipy commented Mar 11, 2023

moiseenkov commented Jul 25, 2023 •

edited

VladaZakharova commented Jul 26, 2023

phanikumv commented Jul 26, 2023

Taragolis commented Dec 14, 2023

infohash commented Dec 15, 2023

Dev-iL commented Feb 18, 2024 •

edited

potiuk commented Feb 18, 2024

Dev-iL commented Feb 18, 2024

VladaZakharova commented Apr 12, 2024

Taragolis commented Apr 12, 2024

Taragolis commented Apr 12, 2024

moiseenkov commented Apr 16, 2024 •

edited

Taragolis commented Apr 19, 2024

dondaum commented Apr 20, 2024

veinkr commented May 5, 2024

Taragolis commented May 5, 2024

veinkr commented May 5, 2024 •

edited

Taragolis commented May 5, 2024

Fix all deprecations for SQLAlchemy 2.0 #28723

Fix all deprecations for SQLAlchemy 2.0 #28723

Comments

potiuk commented Jan 4, 2023 • edited by Taragolis

Body

Local setup in PyCharm

Known non-compatible with SA 20

Reported in core

Reported in providers

Reported in tests

Committer

Taragolis commented Jan 4, 2023

potiuk commented Jan 4, 2023 • edited

Taragolis commented Jan 4, 2023

uranusjr commented Jan 4, 2023

potiuk commented Jan 17, 2023

kaxil commented Jan 28, 2023

infohash commented Mar 6, 2023 • edited

uranusjr commented Mar 7, 2023

auvipy commented Mar 11, 2023

auvipy commented Mar 11, 2023

moiseenkov commented Jul 25, 2023 • edited

VladaZakharova commented Jul 26, 2023

phanikumv commented Jul 26, 2023

Taragolis commented Dec 14, 2023

infohash commented Dec 15, 2023

Dev-iL commented Feb 18, 2024 • edited

potiuk commented Feb 18, 2024

Dev-iL commented Feb 18, 2024

VladaZakharova commented Apr 12, 2024

Taragolis commented Apr 12, 2024

Taragolis commented Apr 12, 2024

moiseenkov commented Apr 16, 2024 • edited

Taragolis commented Apr 19, 2024

dondaum commented Apr 20, 2024

veinkr commented May 5, 2024

Taragolis commented May 5, 2024

veinkr commented May 5, 2024 • edited

Taragolis commented May 5, 2024

potiuk commented Jan 4, 2023 •

edited by Taragolis

potiuk commented Jan 4, 2023 •

edited

infohash commented Mar 6, 2023 •

edited

moiseenkov commented Jul 25, 2023 •

edited

Dev-iL commented Feb 18, 2024 •

edited

moiseenkov commented Apr 16, 2024 •

edited

veinkr commented May 5, 2024 •

edited