How to extract the values in ansible from the query_results of a SQL select?

admin管理员组
文章数量:1026989

I created a simple select to test the result of a MySQL installation in an ansible playbook:

 - name: Simple select query to db playground
   community.mysql.mysql_query:
     login_db: {{ database name }}
     login_user: {{ login_user }}
     login_password: {{ login_password }}
     query: SELECT * FROM equipment;
   register: r_query

I register the result of that task into a variable r_query.

When I debug the query_result of this variable I get the following:

ok: [db01] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db02] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db03] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}

I already checked some explanations how to extract the single values but nothing worked yet.

How can I access the second color value of the result of the first machine db01?

I created a simple select to test the result of a MySQL installation in an ansible playbook:

 - name: Simple select query to db playground
   community.mysql.mysql_query:
     login_db: {{ database name }}
     login_user: {{ login_user }}
     login_password: {{ login_password }}
     query: SELECT * FROM equipment;
   register: r_query

I register the result of that task into a variable r_query.

When I debug the query_result of this variable I get the following:

ok: [db01] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db02] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db03] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}

I already checked some explanations how to extract the single values but nothing worked yet.

How can I access the second color value of the result of the first machine db01?

Share Improve this question edited Nov 16, 2024 at 14:34 Dharman♦ 33.5k27 gold badges101 silver badges149 bronze badges asked Nov 16, 2024 at 9:22 Thomas 311 bronze badge

Add a comment |

1 Answer 1

Sorted by: Reset to default 1

Let's create the inventory below and change the second colors to red, green, and blue for testing

shell> cat hosts
all:
  hosts:
    db01:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: red, id: 4, quant: 2, type: whale}
    db02:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: green, id: 4, quant: 2, type: whale}
    db03:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: blue, id: 4, quant: 2, type: whale}

See hostvars. This dictionary keeps all variables for all hosts. For example,

    - debug:
        var: hostvars[inventory_hostname].r_query.0.1.color

gives

ok: [db01] => 
  hostvars[inventory_hostname].r_query.0.1.color: red
ok: [db02] => 
  hostvars[inventory_hostname].r_query.0.1.color: green
ok: [db03] => 
  hostvars[inventory_hostname].r_query.0.1.color: blue

To "access the second color value of the result of the first machine db01"

    - debug:
        var: hostvars.db01.r_query.0.1.color

gives

ok: [db01] => 
  hostvars.db01.r_query.0.1.color: red
ok: [db02] => 
  hostvars.db01.r_query.0.1.color: red
ok: [db03] => 
  hostvars.db01.r_query.0.1.color: red

A host doesn't need hostvars to access its variables, of course

    - debug:
        var: r_query.0.1.color

gives

ok: [db01] => 
  r_query.0.1.color: red
ok: [db02] => 
  r_query.0.1.color: green
ok: [db03] => 
  r_query.0.1.color: blue

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

tasks:

- debug:
var: hostvars[inventory_hostname].r_query.0.1.color

- debug:
var: hostvars.db01.r_query.0.1.color

- debug:
var: r_query.0.1.color

hostvars also help you to create reports for all hosts in a play. For example,
- debug:
msg: |
{% for h in ansible_play_hosts %}
{{ h }}: {{ hostvars[h].r_query.0.1.color }}
{% endfor %}
run_once: true

gives
msg: |-
db01: red
db02: green
db03: blue

You can extract certain attributes and declare a dictionary. For example,
colors_2nd: "{{ dict(ansible_play_hosts |
zip(ansible_play_hosts |
map('extract', hostvars, ['r_query', 0, 1, 'color']))) }}"

makes the access to the second color value trivial
colors_2nd:
db01: red
db02: green
db03: blue

To create the dictionary, you can also use Jinja. The declaration below gives the same result
colors_2nd: |
{% filter from_yaml %}
{% for h in ansible_play_hosts %}
{{ h }}: {{ hostvars[h].r_query.0.1.color }}
{% endfor %}
{% endfilter %}}

I created a simple select to test the result of a MySQL installation in an ansible playbook:

 - name: Simple select query to db playground
   community.mysql.mysql_query:
     login_db: {{ database name }}
     login_user: {{ login_user }}
     login_password: {{ login_password }}
     query: SELECT * FROM equipment;
   register: r_query

I register the result of that task into a variable r_query.

When I debug the query_result of this variable I get the following:

ok: [db01] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db02] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db03] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}

I already checked some explanations how to extract the single values but nothing worked yet.

How can I access the second color value of the result of the first machine db01?

I created a simple select to test the result of a MySQL installation in an ansible playbook:

 - name: Simple select query to db playground
   community.mysql.mysql_query:
     login_db: {{ database name }}
     login_user: {{ login_user }}
     login_password: {{ login_password }}
     query: SELECT * FROM equipment;
   register: r_query

I register the result of that task into a variable r_query.

When I debug the query_result of this variable I get the following:

ok: [db01] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db02] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}
ok: [db03] => {
    "msg": [
        [
            {
                "color": "white",
                "id": 2,
                "quant": 1,
                "type": "shark"
            },
            {
                "color": "grey",
                "id": 4,
                "quant": 2,
                "type": "whale"
            }
        ]
    ]
}

I already checked some explanations how to extract the single values but nothing worked yet.

How can I access the second color value of the result of the first machine db01?

Share Improve this question edited Nov 16, 2024 at 14:34 Dharman♦ 33.5k27 gold badges101 silver badges149 bronze badges asked Nov 16, 2024 at 9:22 Thomas 311 bronze badge

Add a comment |

1 Answer 1

Sorted by: Reset to default 1

Let's create the inventory below and change the second colors to red, green, and blue for testing

shell> cat hosts
all:
  hosts:
    db01:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: red, id: 4, quant: 2, type: whale}
    db02:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: green, id: 4, quant: 2, type: whale}
    db03:
      r_query:
        - - {color: white, id: 2, quant: 1, type: shark}
          - {color: blue, id: 4, quant: 2, type: whale}

See hostvars. This dictionary keeps all variables for all hosts. For example,

    - debug:
        var: hostvars[inventory_hostname].r_query.0.1.color

gives

ok: [db01] => 
  hostvars[inventory_hostname].r_query.0.1.color: red
ok: [db02] => 
  hostvars[inventory_hostname].r_query.0.1.color: green
ok: [db03] => 
  hostvars[inventory_hostname].r_query.0.1.color: blue

To "access the second color value of the result of the first machine db01"

    - debug:
        var: hostvars.db01.r_query.0.1.color

gives

ok: [db01] => 
  hostvars.db01.r_query.0.1.color: red
ok: [db02] => 
  hostvars.db01.r_query.0.1.color: red
ok: [db03] => 
  hostvars.db01.r_query.0.1.color: red

A host doesn't need hostvars to access its variables, of course

    - debug:
        var: r_query.0.1.color

gives

ok: [db01] => 
  r_query.0.1.color: red
ok: [db02] => 
  r_query.0.1.color: green
ok: [db03] => 
  r_query.0.1.color: blue

本文标签： How to extract the values in ansible from the queryresults of a SQL selectStack Overflow

版权声明：本文标题：How to extract the values in ansible from the query_results of a SQL select? - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人，转载请联系作者并注明出处：http://it.en369.cn/questions/1745661849a2161938.html，本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。

369IT编程

How to extract the values in ansible from the query_results of a SQL select? - Stack Overflow

1 Answer 1

1 Answer 1

更多相关文章