Database Library in Robot Framework

Database Library contains utilities meant for the Robot Framework's usage. The robotFramework 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 and then 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 once the pymssql page will open scroll down, you will see the pymssql-2.1.1.win-amd64-py2.7.exe (521.0 kB) file.

select-pymssql-file-from-website

Click on it to 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 Once the Microsoft SQL server page will open, 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)

Web Services Automation in Robot Framework

Creating a New User in the Database and Assigning the Permission in Robot Framework

Go to the Start menu of your desktop/Laptop 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 the 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 the 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 theNew 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

Go to server roles and make it has a sysadmin

server-roles-sysadmin

And then select the following things in the user mapping.

user-mapping

Right-click on the main server and select the properties

select-properties-under-main-server1

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 .

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

Manual Testing Tools for API in Robot Framework

Creating Table in the Database in Robot Framework

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 a Table, right-click on the table and select a Table under New.

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-and-datatype

Right-click on the FirstName and select 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 as Student 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

Automation of REST API in Robot Framework

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 the 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

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.

Automation Testing Basics

Database Library Keywords in Robot Framework

Following are the imported Database Library Keywords:

Connect to Database :

keyword 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.

The Description keyword :

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 :

keyword 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:

keyword 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 :

keyword Checks 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 :

keyword Check if the table has given exists in the database or not.

The parameter is: tableName

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

Delete All Rows From Table :

keyword Deletes all the rows within a given table.

The parameter is: tableName

Check If Exists In Database :

keyword 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

Introduction & Installation of Robot Framework

Implementation of Database Library in the Flight Application in Robot Framework

By implementing the database library in the flight application, we will make the username and password stored in the database library, and we will make them as 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.

Installation of Selenium Library

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions

Subscribe to See Videos

Subscribe to my Youtube channel for new videos : Subscribe Now