Ensure vendor app's sqlalchemy code will not break when Airflow upgrades to sqlalchemy 2.0

[lwrubel]

Sqlalchemy 1.4 (current version) has 2.0 query styles and features available, and it doesn't look like we're using 1.4-style select statements, but to make sure we don't introduce anything that might break in an Airflow upgrade, consider:

• Monitoring Fix all deprecations for SQLAlchemy 2.0 apache/airflow#28723
• Turning on deprecation warnings by adding SQLALCHEMY_WARN_20=1 to docker-compose.yaml environment variables (not in prod). I think we'd see deprecation warnings in the DAG logs and/or docker logs.

More docs: https://docs.sqlalchemy.org/en/20/changelog/migration_20.html#the-1-4-2-0-migration-path

[jmartin-sul]

per standup discussion, i also added this to the prod docker compose config, see #528.

per the suggestion for the airflow project itself in the linked airflow ticket, i also added the env var to our CI test config.

i spun up airflow locally with the new env var, and clicked around the UI, hitting the tabs in various DAGs, and clicking around in the vendor app. looking at my local log output, all the deprecation warnings seemed to be from airflow itself, and not from the vendor app:

libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1768 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1769 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1779 RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1780 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1768 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1769 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1779 RemovedIn20Warning: Passing a string to Connection.execute() is deprecated and will be removed in version 2.0.  Use the text() construct, or the Connection.exec_driver_sql() method to invoke a driver-level SQL string. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1780 RemovedIn20Warning: The connection.execute() method in SQLAlchemy 2.0 will accept parameters as a single dictionary or a single sequence of dictionaries only. Parameters passed as keyword arguments, tuples or positionally oriented dictionaries and/or tuples will no longer be accepted. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-init-1       | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-webserver-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-webserver-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-triggerer-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-triggerer-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-worker-1     | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-worker-1     | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-scheduler-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:992 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)
libsys-airflow-airflow-scheduler-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/utils/db.py:1000 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)
libsys-airflow-airflow-triggerer-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/models/trigger.py:109 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)
libsys-airflow-airflow-scheduler-1  | /home/airflow/.local/lib/python3.10/site-packages/airflow/jobs/scheduler_job_runner.py:424 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)

i also ran the tests with the deprecation warning flag, and didn't see any SQLAlchemy 2.0 related deprecation warnings, though i did see some other SQLAlchemy related warnings, also entirely from airflow itself and not the vendor app:

Library/Caches/pypoetry/virtualenvs/libsys-airflow-wuyX_Bf1-py3.10/lib/python3.10/site-packages/airflow/www/app.py:86: DeprecationWarning: The sql_alchemy_conn option in [core] has been moved to the sql_alchemy_conn option in [database] - the old setting has been used, but please update your config.
.pyenv/versions/3.10.7/lib/python3.10/configparser.py:408: DeprecationWarning: The sql_alchemy_conn option in [core] has been moved to the sql_alchemy_conn option in [database] - the old setting has been used, but please update your config.
Library/Caches/pypoetry/virtualenvs/libsys-airflow-wuyX_Bf1-py3.10/lib/python3.10/site-packages/airflow/settings.py:280: DeprecationWarning: The sql_alchemy_pool_enabled option in [core] has been moved to the sql_alchemy_pool_enabled option in [database] - the old setting has been used, but please update your config.
.pyenv/versions/3.10.7/lib/python3.10/configparser.py:408: DeprecationWarning: The sql_alchemy_pool_enabled option in [core] has been moved to the sql_alchemy_pool_enabled option in [database] - the old setting has been used, but please update your config.
Library/Caches/pypoetry/virtualenvs/libsys-airflow-wuyX_Bf1-py3.10/lib/python3.10/site-packages/airflow/settings.py:343: DeprecationWarning: The sql_engine_encoding option in [core] has been moved to the sql_engine_encoding option in [database] - the old setting has been used, but please update your config.
.pyenv/versions/3.10.7/lib/python3.10/configparser.py:408: DeprecationWarning: The sql_engine_encoding option in [core] has been moved to the sql_engine_encoding option in [database] - the old setting has been used, but please update your config.