How do I correctly pass NULL in set_fact ?

Archives Ansible Project
1

I am not able to pass a NULL to community.mysql.mysql_query but I think the issue is independent from the module

https://gist.github.com/aworldofcode/35de280946fabcc66e2eea15c8a13dd8

mariadb sql table includes the following column

date_of_removal datetime DEFAULT NULL,

The Key Dict is either

‘main_dict.removal_info is defined’: ‘2021-12-19 21:50:43’ # a datetime properly formated
‘main_dict.removal_info is defined’: ‘N/A’ # A string

The goal is to properly provide a Null when ‘N/A’ is present

Meanwhile in Ansible set_fact

  • name: Set the etl_dictionary Key Value Dictionary from main_dict request
    set_fact:
    etl_dictionary: >-
    {

{% if main_dict.removal_info is defined -%}
{% if ‘N/A’ in main_dict.removal_info -%}
“date_of_removal”: ‘Null’ ,
{% else %}
“date_of_removal”: “{{main_dict.removal_info}}”,
{% endif %}
{% endif %}

}

SQL Statement

Cannot execute SQL 'INSERT INTO … ‘N/A’, ‘No’, ‘null’, ‘N/A’ The expected behaviour to achieve is … ‘N/A’, ‘No’, Null, ‘N/A’

how would I pass correctly null in a set_fact ?

2

I am not able to pass a NULL to community.mysql.mysql_query but I think the issue is independent from the module

https://gist.github.com/aworldofcode/35de280946fabcc66e2eea15c8a13dd8

# mariadb sql table includes the following column
`date_of_removal` datetime DEFAULT NULL,

# The Key Dict is either
'main_dict.removal_info is defined': '2021-12-19 21:50:43' # a datetime properly formated
'main_dict.removal_info is defined': 'N/A' # A string

# The goal is to properly provide a Null when 'N/A' is present

# Meanwhile in Ansible set_fact

- name: Set the etl_dictionary Key Value Dictionary from main_dict request
set_fact:
etl_dictionary: >-
{

{% if main_dict.removal_info is defined -%}
{% if 'N/A' in main_dict.removal_info -%}
"date_of_removal": 'Null' ,
{% else %}
"date_of_removal": "{{main_dict.removal_info}}",
{% endif %}

}

# SQL Statement
# Cannot execute SQL 'INSERT INTO .... 'N/A', 'No', 'null', 'N/A' The expected behaviour to achieve is ... 'N/A', 'No', Null, 'N/A'

how would I pass correctly null in a set_fact ?

I think that the Python keyword None (without quotes around) is the equivalent of a null value and should translate to NULL in the SQL world.
Not tested though.

Regards
        Racke