英文:
Python convert datetime in jinja template to update SQL table
问题
以下是翻译好的部分：
我有一个 Jinja 模板，其中一些值是日期时间。最初运行模板时，生成的 SQL 查询会将日期时间值转换为以下格式：
> 2023-02-13 20:56:13.112000+00:00
日期时间应该是字符串，不包含额外的 +00:00，如下所示：
> "2023-02-13 20:56:13.112000"
我尝试添加此检查，但出现错误，错误消息为“未找到名为 datetime 的测试”。
Jinja 模板：
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 %}
希望这有助于你在 Jinja 模板中正确处理日期时间值以进行 SQL 插入。
英文:
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?
答案1
得分: 0
我已经理解了。您需要添加一个自定义过滤器来测试值是否为日期时间。
将以下内容添加到您的SQL/Jinja助手文件中。
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
然后像下面这样添加/更改Jinja模板。
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 %}
请注意，上述代码已经将Jinja2模板和Python代码进行了合理的分隔。
英文:
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 %}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论