Airflow's PostgresOperator includes a field called parameters for providing SQL parameters for prepared statements.
We wanted to use named parameters since our parameter order would vary, which meant understanding the syntax. In their provided example: SELECT * FROM pet WHERE birth_date BETWEEN SYMMETRIC %(begin_date)s AND %(end_date)s, the format here was unclear - for example the trailing s (we guessed this meant string, but queries with numerical inputs were failing without the s.
After some research we realized that the underlying dependency here was Psycopg. From this page:
Named arguments are supported too using %(name)s placeholders in the query and specifying the values into a mapping. Using named arguments allows to specify the values in any order and to repeat the same value in several places in the query:
and:
The variables placeholder must always be a %s, even if a different placeholder (such as a %d for integers or %f for floats) may look more appropriate for the type.
So, follow the above format for your variable placeholders, and ensure that the variable passed in is cast to the correct format (i.e. don't quote numbers).
Top comments (0)