Database Testing in Robot Framework

In this article, we will look into detail how we can perform Database testing in Robot Framework. To further deep will be doing a few common database operations in our tests.

We would be using one external library for our test – DatabaseLibrary. To install DatabaseLibrary we will be using the command pip3 install robotframework-DatabaseLibrary.

Here all the information required to connect to our database are stored in different variables.

Connect To Database Loads the DB API 2.0 module given in dbapiModuleName and then uses it to connect to the database using dbName, dbAddress, dbUsername, and dbPassword. Since we are having a sql database we are using pymysql. PyMySQL is an interface for connecting to a MySQL database server from Python. To install pymysql we will use the command python3 -m pip install PyMySQL. Here we are creating a user-defined keyword ‘Connect DB’ which will connect to our database.

Similarly, to disconnect from the database we are creating another user-defined keyword ‘Disconnect DB’. Disconnect From Database as the name suggests Disconnects from the database.

– In the test case Verify Successful creation of Table we are creating a table using the query CREATE TABLE Persons (PersonID int, FirstName varchar(255), Address varchar(255), City varchar(255));. Execute SQL String executes the sqlString as SQL commands. We are saving the result of the sql script execution in the ${output} variable. Then using Should Be Equal As Strings we are validating the string saved in ${output} variable to be ‘None’.

– In the test case, Verify Data Insertion in Table we are using Execute SQL Script to execute the content of the sqlScriptFileName as SQL commands and saving the output in ${output} variable. We have our ‘insert.sql’ file under ‘Resources/DB Data/’. Then using Should Be Equal As Strings we are validating the string saved in ${output} variable to be ‘None’.

– In the test case, Verify Data Update in Table we are using the Execute SQL String to update the table using the query UPDATE Persons SET FirstName = “Kevin” WHERE City=”Vantaa” and saving the output in ${output} variable. Then using Should Be Equal As Strings we are validating the string saved in ${output} variable to be ‘None’.

– In the test case, Verify a certain record is present or Not we are validating the presence of a certain record in the table. Check If Exists In Database checks if any row would be returned by the given input selectStatement. If there are no results, then this will throw an AssertionError.

– In the test case, Verify a Table is present or Not we are validating the presence of a table in the database. Table Must Exist checks if the table given exists in the database.

– In the test case, Verify that the row count is 1 we are using Row Count Is Equal To X to validate that there is exactly one row in the table where city name is Espoo. Row Count Is Equal To X checks whether the number of rows returned from the select statement is equal to the value submitted. The value that we are submitting is ‘1’.

– In the test case, Verify that you can Delete a Table we are using Execute SQL String to drop a table from the database using the query DROP TABLE Persons; and saving the output in ${output} variable. Then using Should Be Equal As Strings we are validating the string saved in ${output} variable to be ‘None’. Then using Disconnect DB we are terminating our connection to the database.

Upon Execution, we should get a ‘PASS’.

Do check out 🙂

Github: https://github.com/alapanme/Robot-Framework
All Robot Framework Articles: https://testersdock.com/robot-framework-tutorial/