output of my Jinja in Airflow is value but I rather want string how can I get it?

Question

code

"{{ti.xcom_pull(task_ids='Match_Updated_dates', key='QueryTimeStamp')}}"

The Output of the above in my where clause is

Output I am getting:

where last_updated_utc > {}
where last_updated_utc > 2021-08-26 11:40:33

Desired output

where last_updated_utc > '2021-08-26 11:40:33'

I want to use it as a String , how can I convert jinja output to String from the value I am getting?

Answer 1

If all you need is just to add a single quotation mark you can just do that by adding it.

""" SELECT col FROM my_table WHERE date_col='{{ti.xcom_pull(task_ids='Match_Updated_dates', key='QueryTimeStamp')}}' """ 

I'm not sure though why you want to compare dates as strings. I would guess that your end goal is to convert the value into timestamp known to your SQL flavor? My point here is that this is something that you can handle within the SQL itself - for example:

""" SELECT col FROM my_table WHERE date_col= CAST ('{{ti.xcom_pull(task_ids='Match_Updated_dates', key='QueryTimeStamp')}}' AS TIMESTAMP)"""

A working DAG example:

from datetime import datetime

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.providers.postgres.operators.postgres import PostgresOperator


def func(task_instance, **context):
    task_instance.xcom_push(key='QueryTimeStamp', value='2021-08-26 11:40:33')


dag = DAG(
    dag_id='my_dag',
    schedule_interval=None,
    start_date=datetime(2021, 8, 28),
    catchup=False,
)

op1 = PythonOperator(
    task_id='Match_Updated_dates',
    python_callable=func,
    dag=dag,
)

op2 = PostgresOperator(
    task_id='sql',
    sql=""" SELECT col FROM my_table WHERE date_col='{{ti.xcom_pull(task_ids='Match_Updated_dates', key='QueryTimeStamp')}}' """,
    postgres_conn_id="postgres_default",
    dag=dag,

)

op1 >> op2