0

So a python list will render like ["a", "b", "c"...] which SQL will see as invalid syntax.

I have some python code that sets an xcom variable

#tried with () and []
y = ("2020-10-01", "2020-10-02")
kwargs['ti'].xcom_push(key='refresh_dates', value=y)

Then in my sql I do

where start_date IN {{ task_instance.xcom_pull(key='refresh_dates', task_ids='prep_dag_task')

However this gets rendered as a python list like ["2020-10-01", "2020-10-02"] which sql can't reconize. How can I pass a proper sql list? If I create a string like "("2020-10-01", "2020-10-02")" it will get rendered as a string and not a list.. so how can I accomplish this?

asdasd32
  • 77
  • 5
  • could you try the following : `{{ tuple(task_instance.xcom_pull(key='refresh_dates', task_ids='prep_dag_task')) }}`, essentially, changing the list to a tuple which is a valid sql form. – Anand Vidvat May 21 '21 at 14:27
  • this should help : https://stackoverflow.com/questions/47052582/pass-a-list-of-strings-as-parameter-of-a-dependant-task-in-airflow – DonKnacki May 21 '21 at 14:29
  • @AnandVidvat I get this error `jinja2.exceptions.UndefinedError: 'tuple' is undefined` – asdasd32 May 21 '21 at 16:50
  • could you use the format function instead of jinja? so it would transform like "select * from table where start_date in {}".format(tuple(task_instance.xcom_pull(key='refresh_dates', task_ids='prep_dag_task')) – Anand Vidvat May 22 '21 at 05:30
  • @AnandVidvat this is in a seperate .sql file. – asdasd32 May 24 '21 at 15:08

1 Answers1

0

Tuple is the way to go here:

test = ["2022-01-01", "2022-02-01"]
sql = f'select * from table where date_col in {tuple(test)}'
print(sql)

Should be noted this will break if there is only one item in the tuple (leaves a comma at the end). You can always remove the comma if one item is a possibility

Brandon Sollins
  • 293
  • 1
  • 3
  • 8