apache / apache/airflow

Add support for SqlAlchemy statement in the SqlExecuteQueryOperator

Open
#40,066 6 comments 0 reactions 0 assignees View on GitHub
kind:feature provider:common-sql
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.