Skip DELETE FROM query in sql.j2 template when ansible variable is undefined or empty

Question

I have a bunch of

DELETE FROM user_data WHERE userId = {{ my_user_id }};

in my sql.j2 template.

my_user_id is dynamic and might not exist at all. When it does not exist and its value is an empty string I get

"Cannot execute SQL 'DELETE FROM user_data WHERE userId = ;' args [None]: (1064, \"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1\")",

What is the way to execute the queries so that they don't delete anything or skip just them if there is no my_user_id?

I can't skip the whole Ansible task because there are other queries in the template.

Answer 1

Use the filter default and test the length of the string. For example, given the inventory

shell> cat hosts
host_a

the play below

shell> cat pb.yml 
- hosts: all

  tasks:

    - debug:
        msg: |
          -- Start
          {% if my_user_id|default('')|length > 0 %}
          DELETE FROM user_data WHERE userId = {{ my_user_id }};
          {% endif %}
          -- End

will skip DELETE FROM ... if my_user_id is undefined or empty

shell> ansible-playbook pb.yml 

PLAY [all] ***********************************************************************************

TASK [debug] *********************************************************************************
ok: [host_a] => 
  msg: |-
    -- Start
    -- End

PLAY RECAP ***********************************************************************************
host_a: ok=1    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

The SQL command will be included if my_user_id is not empty

shell> ansible-playbook pb.yml -e my_user_id=12

PLAY [all] ***********************************************************************************

TASK [debug] *********************************************************************************
ok: [host_a] => 
  msg: |-
    -- Start
    DELETE FROM user_data WHERE userId = 12;
    -- End

PLAY RECAP ***********************************************************************************
host_a: ok=1    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

Use the if condition in your template.