Is it possible to read different database Columns entries returned by SQL query in respective variables in Ansible ?

Archives Ansible Project
1

Below is my playbook with a single SQL query that fetches 3 columns from MYSQL database.

I wish to read the values returned if any in three different variables.

`

  • name: “Fetch from Database”
    tags: validateA
    command: >
    mysql --user=root --password=mypass@d deployment
    –host=localhost -Ns -e “SELECT status,layer,environment FROM my_database WHERE num LIKE ‘{{ Number }}’ AND status LIKE ‘Deploy’”
    failed_when: (‘{{ Number }}’ not in command_result.stdout) or command_result.rc != 0
    `

Thus, variable1 should have the output of status
variable2 should have the output of layer
variable3 should have the output of environment

This will help me get the results with one query fired instead of Running three seperate queries one for each column.

Please let me know if and how is it possible ?

2

At a high level I would look at adjusting the query so that it returns json:
https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html

Register the output, and then set your values in a set_fact task.

And I would try to change the authorization setup so you don’t have to echo throw root passwords around in shell commands.

Dick