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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

*** Settings ***
Documentation Database Testing in Robot Framework
Library DatabaseLibrary

*** Variables ***
${DBName} database-name
${DBUser} username
${DBPassword} password
${DBHost} db4free.net
${DBPort} 3306

*** Test Cases ***
Verify Successful creation of Table
[documentation] This test case verifies that user is able to create a table successfully
Connect DB
${output}= Execute SQL String CREATE TABLE Persons (PersonID int, FirstName varchar(255), Address varchar(255), City varchar(255));
Should Be Equal As Strings ${output} None

Verify Data Insertion in Table
[documentation] This test case verifies that user is able to insert data in the table successfully
${output}= Execute SQL Script ./Resources/DB Data/insert.sql
Should Be Equal As Strings ${output} None

Verify Data Update in Table
[documentation] This test case verifies a user can Update a Table record
${output}= Execute SQL String UPDATE Persons SET FirstName = "Kevin" WHERE City="Vantaa";
Should Be Equal As Strings ${output} None

Verify a certain record is present or Not
[documentation] This test case verifies that in Persons Table we have the data for Dwight
Check If Exists In Database SELECT PersonID FROM Persons WHERE FirstName="Dwight"

Verify a Table is present or Not
[documentation] This test case verifies whether Persons table exist in the database or not
Table Must Exist Persons

Verify that the row count is 1
[documentation] This test case verifies that there is only one row in the table where the city is Espoo
Row Count Is Equal To X SELECT PersonID FROM Persons WHERE CITY="Espoo" 1

Verify that you can Delete a Table
[documentation] This test case verifies that a user can delete a Table
${output}= Execute SQL String DROP TABLE Persons;
Should Be Equal As Strings ${output} None
Disconnect DB

*** Keywords ***
Connect DB
Connect To Database pymysql ${DBName} ${DBUser} ${DBPassword} ${DBHost} ${DBPort}

Disconnect DB
Disconnect From Database

1
2
3

*** Settings ***
Documentation Database Testing in Robot Framework
Library DatabaseLibrary

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

1
2
3
4
5
6

*** Variables ***
${DBName} database-name
${DBUser} username
${DBPassword} password
${DBHost} db4free.net
${DBPort} 3306

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

1
2
3
4
5
6

*** Keywords ***
Connect DB
Connect To Database pymysql ${DBName} ${DBUser} ${DBPassword} ${DBHost} ${DBPort}

Disconnect DB
Disconnect From Database

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

*** Test Cases ***
Verify Successful creation of Table
[documentation] This test case verifies that user is able to create a table successfully
Connect DB
${output}= Execute SQL String CREATE TABLE Persons (PersonID int, FirstName varchar(255), Address varchar(255), City varchar(255));
Should Be Equal As Strings ${output} None

Verify Data Insertion in Table
[documentation] This test case verifies that user is able to insert data in the table successfully
${output}= Execute SQL Script ./Resources/DB Data/insert.sql
Should Be Equal As Strings ${output} None

Verify Data Update in Table
[documentation] This test case verifies a user can Update a Table record
${output}= Execute SQL String UPDATE Persons SET FirstName = "Kevin" WHERE City="Vantaa";
Should Be Equal As Strings ${output} None

Verify a certain record is present or Not
[documentation] This test case verifies that in Persons Table we have the data for Dwight
Check If Exists In Database SELECT PersonID FROM Persons WHERE FirstName="Dwight"

Verify a Table is present or Not
[documentation] This test case verifies whether Persons table exist in the database or not
Table Must Exist Persons

Verify that the row count is 1
[documentation] This test case verifies that there is only one row in the table where the city is Espoo
Row Count Is Equal To X SELECT PersonID FROM Persons WHERE CITY="Espoo" 1

Verify that you can Delete a Table
[documentation] This test case verifies that a user can delete a Table
${output}= Execute SQL String DROP TABLE Persons;
Should Be Equal As Strings ${output} None
Disconnect DB

– 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’.