Keyword that runs some SQL and checks result including multiple spaces

Robot Framework
1

Looking at Database Library and it seems you run a query and then check each element of an array, cell by cell, which to me seems a bit tedious. I’m trying to come up with some syntax like this:

*** Settings ***
Library    psql.py

*** Test Cases ***
Example
    SQL Should Return    SELECT prtran_hdr_type, count(*) FROM prtran_hdr WHERE pay_period_date='2022-01-01' GROUP BY 1;
    ...    | prtran_hdr_type  | count |
    ...    | Template   1     |   182 |
    ...    | Projected        |   182 |

I think this reads quite nicely.

So far I have implemented this in psql.py to turn the arguments that follow the query into a multiline string:

def sql_should_return(sql, *data):
    print(f"sql: {sql}")
    prev_data = ""
    result = ""
    for d in data:
        if prev_data:
            if d.startswith("|") and prev_data.endswith("|"):
                result += "\n"
            else:
                result += " "
        result += d
        prev_data = d
    print(f"result: {result}")

The print output is

sql: SELECT prtran_hdr_type, count(*) FROM prtran_hdr WHERE pay_period_date='112233' GROUP BY 1;
result: | prtran_hdr_type | count |
| Template 1 | 182 |
| Projected | 182 |

I obviously haven’t implemented the actual running of the SQL yet or checking the results. Just playing with the syntax for the keyword. I guess the next step is to break the result string up into rows and columns, run the query and compare the result.

Only thinking if using this for queries that return small amounts of data. For larger results I’d compare against an expected CSV file.

The main problem is it loses the three space gap between Template and 1. So I would need to strip multiple spaces from the SQL result, before comparison. Which is a shame, as sometimes I have found myself doing things like this were I need to check for a certain number of spaces in something:

    Editbox Should Equal    Description    05:37-11:07${SPACE} 11:58-14:58${SPACE}

That is so I can check there are two spaces between and a space on the end. I think it would be much nicer to read:

    Editbox Should Equal    Description    """05:37-11:07  11:58-14:58 """

Or alternatively:

    Editbox Should Equal    Description    ||05:37-11:07  11:58-14:58 ||

Similarly for the pipe format table, this would be nice:

    SQL Should Return    SELECT prtran_hdr_type, count(*) FROM prtran_hdr WHERE pay_period_date='2022-01-01' GROUP BY 1;
    ...    """ prtran_hdr_type  | count """
    ...    """ Template   1     |   182 """
    ...    """ Projected        |   182 """

or

    SQL Should Return    SELECT prtran_hdr_type, count(*) FROM prtran_hdr WHERE pay_period_date='2022-01-01' GROUP BY 1;
    ...    || prtran_hdr_type  | count ||
    ...    || Template   1     |   182 ||
    ...    || Projected        |   182 ||

With everything between a pair of || being passed through as an argument with spaces preserved.

Any thoughts?

1 Like
2

I’ve had this exact conversation with @pekkaklarck in relation to have embed SQL into suites / resource files. In my case it wasn’t about results but the actual query itself. I had some “unsanitized” data in warehouse. In that case i ended up using some placeholder character that would be reformatted before being sent to actual database.

And again, I asked Pekka about something similar to have the ability to mimic Python’s raw strings to having pre/post markers or using of non breaking space. It would have “semantic” meaning that even if there are multiple spaces, RF wouldn’t consider those spaces as argument separator.. Ofcourse this would still need the nbsp to be converted to normal space so not good…

At that time, Peke didn’t shoot the idea down immediately. Maybe this conversation could lead to something concrete ?

1 Like
3

Hi Kim,

My initial thought is what’s the goal of your test case?

  • Test the formatting of the output of the SQL query?
  • test the result values (count) of the individual result lines ensuring the count is the expected values and the expected rows are there?

if it’s the second goal, I would use Split To Lines, Split String and Strip String to get the values from the result.

Having said that database library’s Query can return you the result as a dictionary so that might be an easier approach?

Dave.

1 Like
4

I have made a standard script that puts data returned by a SQL-query into a csv-file or 2d-array. Column names as first row.

I use the “DataComparerLibrary” for comparing these actual results with expected results. Combinations of 2d-arrays, text-files and csv-files can be compared.

See the information and examples on pypi.org ===> “DataComparerLibrary”.

1 Like
5

My main goal is to test the result values, and for queries that only return a small amount of data, to have the expected query result, including multiple spaces, within the .robot file and not in an external file.

6

For that part, there are a couple more ways of dealing with multiple spaces mentioned here: Space and empty variables, the ${SPACE * 5} might be your best option.

Dave.

1 Like
7

Thanks @robot_phil. Comparison of the data was the next step, and I already have written some of my own functions for this. But I did check out your DataComparerLibrary as it looks to do something fairly similar to what I already have written myself but a bit differently.

I like you use of the tags in the expected file like {PRESENT}, {SKIP}, {DATETIME_FORMAT}. I might borrow that idea if that’s ok?

The data compare functions I wrote do something similar, but they allow a list of keyword arguments to be passed in like:

Result Should Equal Expected CSV    ${result}    ${expected_csv_filename}    FOO=123  BAR=abc

Then in the expected CSV file, before it does the comparison, it does a find replace on {FOO}, replacing it with 123, etc…

The other thing I do is present the differences in the test log.html as a HTML table with cell background and text colors to highlight the differences.

Are you planning to open your project for contributors?

1 Like
8

Thanks @kimfaint for your reaction. It is not quite clear to me why for queries that return a small amount of data you want to add spaces.

As I told I have written some general keywords. I have made a standard script that puts data returned by a SQL-query into a csv-file or 2d-array. Column names as first row.
For queries that return a small amount of data you can store the expectation in your robot file, as a 2d-array.
For queries that return a large amount of data you can store the expectation in a csv-file.

With the DataComparerLibrary you can compare 2d-arrays or csv-files. It is also possible to add a dictorary as argument with literals that should be replaced.

&{literal_dict}=  Create Dictionary  {lit_1}=${some_variable}   
                                                             {lit_2}='Text with space'
                                                             {version} = ${version}
                                                             {env} = ${env}

DataComparerLibrary.Compare Data Files  ${actual_input_file_template_literal}  ${expected_input_file_template_literal}  delimiter_actual_data=;  delimiter_expected_data=;  template_literals_dict=${literal_dict}

You triggered me that I should update the documentation with information about replacing literals. Also I should add an example of how to construct an expected 2d-array.

The idea of presenting information in a HTML-table sounds good. I will put it on my list.
I am always interested in new ideas.

1 Like