Python convert datetime in jinja template to update SQL table

Question

I have a jinja template below in which some of the values are of datetime. I initially ran my template but the SQL query generated would convert datetime values to the following:

2023-02-13 20:56:13.112000+00:00

. The datetime should instead be strings without the extra +00:00 like

"2023-02-13 20:56:13.112000"

I tried to add this check but got an error saying no test named datetime found.

{% elif val is datetime %}
      {{col}} = '{{val.strftime('%Y-%m-%d %H:%M:%S')}}'

Jinja template:

UPDATE {{database_name}}.{{table_name}} 
SET
{% for col, val in zip(column_list, value_list) %}
    {% if val is string %}
      {{col}} = '{{val}}'
    {% elif val is datetime %}
      {{col}} = '{{val.strftime('%Y-%m-%d %H:%M:%S')}}'
    {% else %}
      {{col}} = {{val}}
    {% endif %}
    {% if not loop.last %},{% endif %}
{% endfor %}

WHERE 1= 1
{% for key,val  in filters.items() %}  
    {% if  val is sequence and val is not string and val is not mapping  %} 
        AND {{key}}  in  ({% for i in val %}{% if i is string %}'{{i}}'{% else %}{{i}}{% endif %}{% if not loop.last %},{% endif %}{% endfor %}) 
    {% elif  val is string   %} 
        AND {{key}} =  '{{val}}'
    {% elif  val is number   %} 
        AND {{key}} =  {{val}} 
     {% elif  val is boolean %} 
        AND {{key}} =  {{val}}         
    {% else %} 
        AND {{key}} =  '{{ val }}'
    {% endif %}
{% endfor %}

Any idea what the best way is to convert a datetime value in a jinja template for SQL insertion?

Answer 1

I figured it out. You have to add a custom filter to test if the value is datetime.

Add the following to your sql/jinja helper file.

from jinja2 import Template
from jinja2.filters import FILTERS
import os
from datetime import datetime
def check_if_datetime(val):
    print(val)
    return isinstance(val, datetime)
FILTERS["check_if_datetime"] = check_if_datetime

Then add/change the jinja template like below.

UPDATE {{database_name}}.{{table_name}} 
SET
{% for col, val in zip(column_list, value_list) %}
    {% if val is string %}
      {{col}} = '{{val}}'
    {% elif val | check_if_datetime %}
      {{col}} = '{{val.strftime('%Y-%m-%d %H:%M:%S')}}'
    {% else %}
      {{col}} = {{val}}
    {% endif %}
    {% if not loop.last %},{% endif %}
{% endfor %}
WHERE 1= 1
{% for key,val  in filters.items() %}  
    {% if  val is sequence and val is not string and val is not mapping  %} 
        AND {{key}}  in  ({% for i in val %}{% if i is string %}'{{i}}'{% else %}{{i}}{% endif %}{% if not loop.last %},{% endif %}{% endfor %}) 
    {% elif  val is string   %} 
        AND {{key}} =  '{{val}}'
    {% elif  val is number   %} 
        AND {{key}} =  {{val}} 
     {% elif  val is boolean %} 
        AND {{key}} =  {{val}}         
    {% else %} 
        AND {{key}} =  '{{ val }}'
    {% endif %}
{% endfor %}