After running a SQL query using the ansible mysql_query module, how do I extract the data returned?

Question

I have a playbook that is retrieving data from a test database, but I am unable to figure out how to extract the rows returned from the database.

The whole purpose of the exercise is to pull the value from the KVP and add it to a report, so eventual the output would look similar to:

datetime	hostname	server_status	server_type	associated_controller
null	ATT_DELL	UP	NODE	123.456.78.901
null	steve_ubuntu	UP	CONTROLLER	NOT APPLICABLE

- name: Sample playbook
  gather_facts: True
  hosts: sql_server
  tasks:
    - name: Retrieve data from database
      tags: always
      community.mysql.mysql_query:
        login_host: "{{ login_host }}"
        login_db: "{{ login_db }}"
        login_user: "{{ login_user }}"
        login_password: "{{ login_password }}"
        query: select * from test_db
      register: mysql_result

    - name: output query results
      tags: always
      debug:
        msg: "{{ item }}"
      loop:  "{{ mysql_result.query_result }}"

PLAY [Sample playbook] **************************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************

ok: [server_name]

TASK [Pull data from database] *************************************************************************************************************************

ok: [server_name]

TASK [output query results] **************************************************************************************************************************

ok: [servername] => (item=[{u'datetime': None, u'hostname': u'steve_ubuntu', u'associated_controller': u'NOT APPLICABLE', u'server_type': u'controller', u'server_status': u'Up'}, {u'datetime': None, u'hostname': u'ATT_DELL', u'associated_controller': u'NOT APPLICABLE', u'server_type': u'controller', u'server_status': u'Up'}]) =>
  msg:
  - associated_controller: NOT APPLICABLE
    datetime: null
    hostname: steve_ubuntu
    server_status: Up
    server_type: controller
  - associated_controller: NOT APPLICABLE
    datetime: null
    hostname: ATT_DELL
    server_status: Up
    server_type: controller

I tried the following:

    - name: output query results
      tags: never
      debug:
        msg: "{{ item[0] }}"
      loop:  "{{ mysql_result.query_result | items2dict }}"

Which resulted in the following, but I need to loop through each list items, extracting the individual values from each kvp to be used in the report.

ok: [servername] => (item=[{u'datetime': None, u'hostname': u'steve_ubuntu', u'associated_controller': u'NOT APPLICABLE', u'server_type': u'controller', u'server_status': u'Up'}, {u'datetime': None, u'hostname': u'ATT_DELL', u'associated_controller': u'NOT APPLICABLE', u'server_type': u'controller', u'server_status': u'Up'}]) =>
  msg:
    associated_controller: NOT APPLICABLE
    datetime: null
    hostname: steve_ubuntu
    server_status: Up
    server_type: controller

I have tried so many other things like converting the list to a dictionary, but I received the error below. I need to keep the input dynamic, but I don't know how many items will be retrieved from the database.

fatal: [servername]: FAILED! => msg: 'Unexpected templating type error occurred on ({{ mysql_result.query_result | items2dict}}): list indices must be integers, not str'

I'm not just looking for an answer to my problem, but what fundamentals about ansible do I need to read through to better understand the fundamentals about the values returned and how to extract them? I've read through Ansible's online documentation, but that's not the best documentation for understanding their product.

This error -> list indices must be integers, not str, why not convert the data type to INTEGER type first and then it can handle them. In other words, x = 1 is not the same as x = "1", or plainly the value of 1 and the word "1" are two different data types. — easleyfixed, Jul 21 '23 at 21:20
I can't really answer your question because I'm clearly not understanding what is going on with the returned data. The data is returned data is: kvp1: some_value kvp2: some_value, etc. So, what I am expecting is that converting to a dictionary would automatically recognize the kvp and allow me to reference using dot notation or referencing the key. Basically, there are no numerical values to convert unless they are implicitly applied and I don't know how to reference them. — biker_steve, Jul 21 '23 at 21:35
Ok so when you converted it to a dictionary, each thing was given an index value, probably the order they were listed. You then use that number to look them up instead of the word, and this allows you to loop from 1 to last thing using the number values. The error message seems to indicate it was passed word "str/string" instead of the number value it wanted from the dictionary. You said reference the key, and I bet the key is excepting to be a number. — easleyfixed, Jul 21 '23 at 21:41

Vladimir Botka · Answer 1 · 2023-07-21T22:01:15.660

Use Jinja templates. For example, given the data

  mysql_result:
    query_result:
      - - associated_controller: NOT APPLICABLE
          datetime: null
          hostname: steve_ubuntu
          server_status: Up
          server_type: controller
        - associated_controller: NOT APPLICABLE
          datetime: null
          hostname: ATT_DELL
          server_status: Up
          server_type: controller

declare the keys

  qr_keys:
    - - - datetime
        - hostname
        - server_status
        - server_type
        - associated_controller

^{Note: You can get the keys dynamically if you accept the order
qr_keys: "{{ mysql_result.query_result|
map('json_query', '[].keys(@)') }}"}

Decide the format you'd like to store the data and create the template. For example, try CSV

    - debug:
        msg: |
          {% for query in mysql_result.query_result %}
          {% set keys=qr_keys[loop.index0][0] %}
          {{ keys|join(';') }}
          {% for i in query %}
          {% for key in keys %}
          {{ i[key]|d('null', true) }}{% if not loop.last %};{% endif %}{% endfor %}

          {% endfor %}
          {% endfor %}

gives

  msg: |-
    datetime;hostname;server_status;server_type;associated_controller
    null;steve_ubuntu;Up;controller;NOT APPLICABLE
    null;ATT_DELL;Up;controller;NOT APPLICABLE

If this is what you want create the file(s) on the controller

    - copy:
        dest: "/tmp/query_result-{{ inventory_hostname }}.csv"
        content: |
          {% for query in mysql_result.query_result %}
          {% set keys=qr_keys[loop.index0][0] %}
          {{ keys|join(';') }}
          {% for i in query %}
          {% for key in keys %}
          {{ i[key]|d('null', true) }}{% if not loop.last %};{% endif %}{% endfor %}

          {% endfor %}
          {% endfor %}
      delegate_to: localhost

will create the file

shell> cat /tmp/query_result-sql_server.csv 
datetime;hostname;server_status;server_type;associated_controller
null;steve_ubuntu;Up;controller;NOT APPLICABLE
null;ATT_DELL;Up;controller;NOT APPLICABLE

^{Example of a complete playbook for testing
- hosts: sql_server

vars:

mysql_result:
query_result:
- - associated_controller: NOT APPLICABLE
datetime: null
hostname: steve_ubuntu
server_status: Up
server_type: controller
- associated_controller: NOT APPLICABLE
datetime: null
hostname: ATT_DELL
server_status: Up
server_type: controller

qr_keys:
- - - datetime
- hostname
- server_status
- server_type
- associated_controller
# qr_keys: "{{ mysql_result.query_result|
# map('json_query', '[].keys(@)') }}"

tasks:

- debug:
msg: "{{ item }}"
loop: "{{ mysql_result.query_result }}"
loop_control:
label: "{{ item|json_query('[].hostname') }}"

- debug:
msg: |
{% for query in mysql_result.query_result %}
{% set keys=qr_keys[loop.index0][0] %}
{{ keys|join(';') }}
{% for i in query %}
{% for key in keys %}
{{ i[key]|d('null', true) }}{% if not loop.last %};{% endif %}{% endfor %}

{% endfor %}
{% endfor %}

- copy:
dest: "/tmp/query_result-{{ inventory_hostname }}.csv"
content: |
{% for query in mysql_result.query_result %}
{% set keys=qr_keys[loop.index0][0] %}
{{ keys|join(';') }}
{% for i in query %}
{% for key in keys %}
{{ i[key]|d('null', true) }}{% if not loop.last %};{% endif %}{% endfor %}

{% endfor %}
{% endfor %}
delegate_to: localhost}

After running a SQL query using the ansible mysql_query module, how do I extract the data returned?

1 Answers1