ansible mysql query not showing results

Question

I have used community.mysql collection and was able to run a query against the database.

---
- name: Get stats of a file
  hosts: localhost
  become: true
tasks:
    - name: run mysql query
      community.mysql.mysql_query:
        login_user: testuser
        login_password: testpass
        login_db: test_db
        query: SELECT * FROM STATION WHERE ID = %(id_val)s AND STATE = %(story_val)s
        named_args:
          id_val: 12
          story_val: AZ
      register: r_query

The playbook runs fine without errors

root@ip-172-31-83-231:/home/ubuntu# ansible-playbook sql_query.yml
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match 'all'
PLAY [Get stats of a file] ***************************************************************************************************************************************************************
TASK [Gathering Facts] *******************************************************************************************************************************************************************
ok: [localhost]
TASK [run mysql query] *******************************************************************************************************************************************************************
ok: [localhost]
PLAY RECAP *******************************************************************************************************************************************************************************
localhost                  : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored

could not find the documentation on how to display the queries results. Can you help me in the right direction.

Answer 1

The community.mysql.mysql_query module documents its return values as:

• executed_queries
• query_results
• rowcount

So, if you're registering r_query, and you want to see the result, you can call r_query.query_result.

A common approach when developing playbooks initially is to send to stdout with a debug task:

- name: Get stats of a file
  hosts: localhost
  become: true
tasks:
    - name: run mysql query
      community.mysql.mysql_query:
        login_user: testuser
        login_password: testpass
        login_db: test_db
        query: SELECT * FROM STATION WHERE ID = %(id_val)s AND STATE = %(story_val)s
        named_args:
          id_val: 12
          story_val: AZ
      register: r_query
- name: what happened
  ansible.builtin.debug:
    msg: "{{ r_query.query_result }}"