Database Library in Robot Framework

Database Library contains utilities meant for the Robot Framework's usage. This can allow you to query your database after an action has been made to verify the results. This is compatible* with any Database API Specification 2.0 module.

If you go to the Database library official website, they have provided the Database API specification and the list of DB API.

To perform the database automation, first, we have to have Database Library, and the next, we have to have an Interface. This interface is a python API interface that connects the database. In this case, we are going to use the Microsoft SQL Server as a database, and the interface is pymssql 2.1.1.

To install the Database library through the pip command as pip install -U robotframework-databaselibrary

Go to the command prompt and enter the above command to install the database library into your system.

command-to-install-database-library

We have succesfully installed a database library.

To install the pymssql interface, navigate to the given link, install pymssql 2.1.1 and scroll down, you will see the pymssql-2.1.1.win-amd64-py2.7.exe (521.0 kB) file, click on it will download. Once it completes downloading, open the file, and by clicking on next, it will install into your system.

installing-pymssql

And the next one is, we have to install the Microsoft SQL Server, which is used as a database in this test case.

Navigate to the given URL Download Microsoft SQL Server and scroll down and select the Developer and click on Download now under the download of a special edition.

downalod-microsoft-sql-server

Once the software downloaded, open the file and select the Basic edition

select-basic-as-developer-edition

Once you choose the Basic, it will start downloading install packages

downaloding-install-package

Once the download completes, it will start installing

installing-packages

successfully-installed-microsoft-sql-server

And also download the SSMS by clicking on the Install SSMS(Microsoft SQL Server Management Studio)

Creating a New User in the Database and Assigning the Permission

Go to your start menu and select the Database engine Tuning advisor, Once the advisor page will open, go to tools and click on that, you can see two options SQL server profile and SQL server management studio.

Select the SQL Server Management Studio, and once the management studio page will open, you can see a connect to server dialog box, select the server name and click on connect.

connect-server-dialog-box1

Once you click on the connect button, on the left side you can see the admin account, which contains following

obect-explorer-admin-account

Select the Logins under the security and Right-click on it and select new login

select-new-login

As soon as you select the new login, it will ask you to enter the new login name, so enter the login name and switch to SQL Server Authentication and enter the password, you may also copy and save the login name and password for future use. And Uncheck enforce password policy.

creating-new-login-and-password

And go to server roles and make it has a sysadmin

server-roles-sysadmin

And then select the following things in the user mapping and click on the ok button.

user-mapping

Right-click on the main server and select the properties

select-properties-under-main-server

And goto security under server properties, by default the server has been selected Windows authentication, switched to SQL server and windows authentication mode. Once you change the authentication mode the server will ask you to restart, click ok and restart it.

sql-server-restarted1

Now, go back to the main server and right-click and disconnect it. and go back and right-click on the connect and select the Database Engine, the SQL Server dialog box will appear, select the SQL Server Authentication and enter the new Login name and password and click on connect.

You can see that, you have connected to the server by using the TestSQL and the password.

coonected-to-the-server-by-using-testsql

Creating Table in the Database

Once you logged in to the database by using your login name and password, When you click on the temp database under the system database, you will find an option called table, right-click on the table and select a new table.

select-new-table-under-table

Once the Table has been loaded, enter the Column name as FirstName and another Column name as the LastName and keep the Datatype as nchar() only.

table-with-column-name

Right-click on the FirstName and set as primary key

set-firstname-as-primary-key

When you try to close the table, the server will ask you to save the following changes and when you click on yes, it will ask you to enter the name of the table. Enter the name of the table and click on ok.

created-table-student

After the successful creation of the table, it will be visible under the Table on the left side of the Window.

successfully-created-table

By right-clicking on the dbo.Student and select the Edit Top 200 Row, you can able to enter the rows to the table.

edit-rows-of-student-table

I have created a database table with two values as shown below

complete-student-table

Connecting Database to the Robot Framework

We have created a table in the database, now let us connect this database into the robot framework. To connect a database into the robot framework the database library has given a keyword called connect to database.

The parameters are: dbapiModuleName = pymasql

dbName =tempdb

dbUsername=TestSQL

dbPassword=test123

dbHost=localhost

dbPort=1433

dbConfigFile=./resources/db.cfg

Now, go back to the ride editor and create a new test suite as ConnectDatabase

creating-new-test-suite-connect-databse

And create a new test case as TC01_DatabaseAutomation

creating-test-case-databse-automation

Import the database library into the new test suite ConnectDatabse and write the data into the new test case as shown below

complete-test-case-tc01-database-automation

Now, select the test case and Run.

Database Library Keywords

Following are the imported Database Library Keywords:

Connect to Database : Loads the DB API 2.0 module given dbapiModuleName then uses it to connect to the database using dbName, DB username, and DB password.

Optionally, you can specify a dbConfigFile wherein it will load the default property values for dbapiModuleName, dbName DB username and DB password (note: specifying dbapiModuleName, dbName DB username or DB password directly will override the properties of the same key in dbConfigFile). If no dbConfigFile is specified, it defaults to ./resources/db.cfg.The dbConfigFile is useful if you don't want to check into your SCM your database credentials.

Description: Uses the input select statement to query a table in the DB which will be used to determine the description.

The parameters are: selectStatement

Let us work on the same test case, <TC01_DatabaseAutomation> and write as below by using the Description keyword.

test-case-using-description-keyword

Query: Uses the input selectStatement to query for the values that will be returned as a list of tuples.

Tip: Unless you want to log all column values of the specified rows, try specifying the column names in your select statements as much as possible to prevent any unnecessary surprises with schema changes and to easily see what your [] indexing is trying to retrieve.

The parameter is: selectStatement.

testcase-using-query-keyword

Row Count: Uses the input selectStatement to query the database and returns the number of rows from the query.

The parameter is: selectStatement

tescase-with-row-count-keyword

Row Count Is Equal To X: Check if the number of rows returned from selectStatement is equal to the value submitted. If not, then this will throw an AssertionError.

The parameters are: selectStatement, numRows

testcase-using-row-count-is-equal-to-x

Table Must Exist: Check if the table given exists in the database.

The parameter is: tableName

test-case-with-table-must-exist-keyword

Delete All Rows From Table : Delete all the rows within a given table.

The parameter is: tableName

Check If Exists In Database: Check if any row would be returned by given the input selectStatement. If there are no results, then this will throw an AssertionError.


testcase-with-check-if-exists-in-database

Implementation of Database Library in the Flight Application

By implementing the database library in the flight application, we will make username and password stored in the database library and we will make them input to the flight application.

Go to the SQL Server Management Studio and create a new Login as shown below

creating-new-login

Create a new table called Ngendigitall with the following data.

creating-ngendigital-table

Now, go back to the ride editor and create anew test case as TC02_DatabaseLibrary_with_flight_application

tc02-database-library-with-flight-application

The complete data of TC02_DatabaseLibrary_with_flight_application is:

Connect To Database pymssql tempdb Test Flightapplication test123
Table Must Exist Ngendigitall
@{queryResults} Query select Username from Ngendigital where id=1
@{Var5} Create List @{queryResults}
@{queryResults} Query select Username from Ngendigital where id=1
@{Var6} Create List @{queryResults}
Open Browser https://ngendigital.com/demo-application chrome
Maximize Browser Window
Select Frame id=iframe-015
Wait Until Keyword Succeeds 2 1 Input Text xpath://input[@type='email'] @{Var5}
Wait Until Keyword Succeeds 2 1 Input Text xpath://input[@type='password'] @{Var6}
Wait Until Keyword Succeeds 2 1 Input Text xpath://div[@name='Sign In']

Now, select the test case and run.

















Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions