Add support for SqlAlchemy statement in the SqlExecuteQueryOperator
- Dominant language
- Python
- Stars
- 46.9k
- Forks
- 17.8k
- Avg merge
- 2d 10h
- Merged PRs (30d)
- 483
Description
### Description
Allow to pass a (list of) SqlAlchemy statement in addition to `str` to the `sql` parameter of the `SQLExecuteQueryOperator`.
### Use case/motivation
I need to execute a query either from Airflow or manually.
So I generate a SqlStatement (`sa.select(...).where(...)`), using `bindparam`.
I can then do: `session.execute(stmt, params=dict(data_interval_start=mydate))` for "manual" execution, and I expect to be able to do: `SQLExecuteQueryOperator(sql=stmt, params=dict(data_interval_start="{{ data_interval_start }}"))` for Airflow.
However, this construct fails, because Airflow operator expect a string. But passing `sql=str(stmt)` replaces all bind parameters following a schema like `:data_interval_start`, and this does not work for my Postgresql backend (`psycopg2.errors.SyntaxError: syntax error at or near ":"`)
### Related issues
_No response_
### Are you willing to submit a PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
Contributor guide
Research direction
Start by reading the SQLExecuteQueryOperator implementation and how its sql and params arguments are handled. Check how SQLAlchemy statements and bind parameters should be passed without converting them to strings, including the PostgreSQL case described. Done means the operator accepts a statement or list of statements alongside strings and preserves parameter binding for Airflow execution.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sqlalchemy
- Domain
- data-engineering, databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100