MySQL / MariaDB Connector

[grodgonfib]

Hi, I'm doing some playbooks and I always need to make a code snippet after a MySQL Run query connector (SELECT) to get

- If query returns some data

- Number of rows query returns

My code snippet is always like this.

if isinstance({{vars.steps.Query_CMDB.data}},list:
    print(len({{vars.steps.Query_CMDB.data}}))
else:
    print(0)

As I know when query returns data it always returns an array with N dicts inside (rows). But when there is no data, only return "success", so if I try with jinja to run 

{{vars.steps.Query_CMDB.data | count}} > 0

Always return > 0 because when its "success" it counts the letters... There is a better way to avoid my "length" step? and only have the MySQL query step and after that, the decision step (if rows > 0 -> then X, else Y)

[sahirrao]

Hi,

Could you please confirm the response format for the code snippet step? Specifically, when a query returns data, the response always contains an array with N dictionaries (rows). However, when no data is returned, the response contains only a "success" status.

[grodgonfib]

Hi, 

Query that has data > returns array inside data

Query that not found data > return status="success" inside data

Check the images, also in both cases it has an output.status = "Success"

[grodgonfib]

Maybe this is the way to do? Check if data.status exists ?  in block decision

vars.steps.Query_no_data.data["status"] is defined

if key exists > no data way 

if key does not exists > data returned

But this only solves if database returns data, but not how much rows, for example I have some steps that I need to know if there is only 1 result (cmdb).

If zero results > error no data found on cmdb

If more than 1 result > error duplicated data on cmdb

If 1 result > ok, continue with playbook

[sahirrao]

yes, If vars.steps.Query_no_data.data["status"] is defined, it indicates that no data was returned. Otherwise, the query returns data in the form of a list.

[sahirrao · Accepted solution]

Zero Results (No data)

{{ vars.steps.Query_no_data.data["status"] is defined }}

Meaning: The status key exists → database returned no data.

---

Exactly One Result

{{ vars.steps.Query_no_data.data["status"] is not defined and vars.steps.Query_no_data.data | length == 1 }}

Meaning: Data returned and the list contains exactly 1 record → OK to continue.

---

More Than One Result (Duplicate)

{{ vars.steps.Query_no_data.data["status"] is not defined and vars.steps.Query_no_data.data | length > 1 }}

Meaning: Data returned and list contains more than 1 record → duplicate error.