Add support for SqlAlchemy statement in the SqlExecuteQueryOperator about airflow HOT 2 OPEN

This feels kinda risky from a security perspective. Could you use SqlAlchemy to dump the SA query to a SQL string instead?

from airflow.

Can you explain which kind of security issues you see?

Actually yes, I finally found a way to dump the query as a SQL string. However, it requires that you know which kind of backend will execute the query.

import sqlalchemy as sa

stmt = sa.select(sa.bindparam("foo"))


from sqlalchemy.dialects import postgresql, sqlite, mysql

print("str: ", str(stmt))
print("compile(): ", str(stmt.compile()))
print("compile(postgresql): ", str(stmt.compile(dialect=postgresql.dialect())))
print("compile(mysql): ", str(stmt.compile(dialect=mysql.dialect())))
print("compile(sqlite): ", str(stmt.compile(dialect=sqlite.dialect())))

Will output:

str:  SELECT :foo AS anon_1
compile():  SELECT :foo AS anon_1
compile(postgresql):  SELECT %(foo)s AS anon_1
compile(mysql):  SELECT %s AS anon_1
compile(sqlite):  SELECT ? AS anon_1

As you can see, depending on your backend, the generated string is not the same. But I often use a sqlite backend during my dev/test process, while my production DB use Postgres. So it could be nice to abstract such kind of implementation, as long as your Airflow connection is a valid DbApi connection.

from airflow.