UI Automation, Database Connections and SQL

Context:

Databases have been the backbone of data truth for many years and years to come. Database have come into existence to be able to retrieve and persist data in an efficient manner. There are lot of database vendors and the ones I have worked with are Microsoft SQL, Oracle, MySQL, Postgre SQL, sqlite3 and so on. These days we hear Nosql databases like MongoDB too. Anyways, we are not going into the details of databases here. Web Applications typically interact with application layer and application layer might either directly interact with database of some layer in between that translates eventually to SQL queries in a relational database environment. So data gets served to the web application ultimately maps to a database [and tables, rows, columns thereof]

Relevance to Automation:

While we validate scenarios on web layer, we need “expected data” that can be compared to actual data that appears on web UI. The expected data truth generally lies in a database. By that we mean, instead of manually extracting data from database and then using those string values in our scenarios, can’t we directly make a database query (SQL query) real-time while the Selenium test is executing and compare the values of fields on the browser with the column values returned from the SQL query ? Yes we can ! That is what we are going to demonstrate in this section. The converse is also true, meaning sometimes we have to look up data like city’s , countries [in short it is called look up data] which are stored in databases and validate them on the UI layer, that the set of values are indeed correct or is it being mangled in the layers between web UI and database [potentially a defect in the application layer]. Anyways, with that background, we will see how we can make SQL queries using activerecord gem in Ruby. Active Record gem was originally designed for ruby on rails application development and it recommends a convention to follow to interact with the database, however it also allows to execute free form SQL. We will use whatever applies to us and we will see both examples in this topic.

Agenda:

  • sqlite3 firefox plugin
  • create a simple database
  • activerecord and sqlite3 gem
  • Important lines of code
  • connect dots between selenium operations and sql queries

1) sqlite3 firefox plugin:

Open firefox browser and go to Settings and then “Add-ons”

firefox_addons

 

Search for “sqlite3” and install it. Restart the firefox browser

sqlite3_firefoxaddon

 

Add the sqlite3 manager (blue icon) to the top bar by going to Settings – Customize and drag drop the icon to the top bar.

2) Create a simple database:

You need not do this step as I have provided the database with tables populated with data below, however I would recommend that you do it as you can create your own database and tables if needed. The overall db architecture for this 3 tables are depicted as in this picture.

Open sqlite manager and create new database and name it ‘practiceselenium’ as follows:

sqlite3_newdb

fans table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

products table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

product_categories table:

Copy the below sql statement in “Execute SQL” tab in sqlite3 manager and hit ‘Run SQL’. There should not be any errors.

You can skip creating the tables and download the database from here directly. It is a single file. The overall architecture is depicted below:

overall_db_architecture

3) sqlite3 is part of Python standard library

The sqlite3 that we will be using throughout this tutorial is part of the Python Standard Library and is a nice and easy interface to SQLite databases

 

 

3) Important lines of code.

Create a database connection

We point the database to our database file. In my case I placed the practiceselenium.sqlite in the path as shown below. If the path doesn’t exist, please go ahead and create it. The connection object is held in conn connection object.

The default cursor context.c is retrieved.

Execute SQL

Execute arbitrary sql on the connection object. This will be referred to as traditional sql below. As we can see, we can execute any SQL queries we might have built over years for maintenance projects.

Loop through result set

The result set has rows through which we can loop through by specifying column names.

Return Python dicts instead of tuples

The default cursor returns the rows at tuples. When we use dictionary cursor, data is sent in the form of python dictionaries

 

 

 

Update a row 

 

Retrieve a column and compare it with web element

Entire Cucumber Feature:

Step Definitions:

Output:

  • First scenario (out of 6 total) retrieves values from products table and prints it
  • The last two scenarios are very helpful in understanding how we can apply this concept to building a script that is more end-end test automation script.
  • The browser pops up for all scenarios, but that is okay, please ignore it.

Closing Thoughts:

We have looked at one database example i.e. sqlite3 because it is easier to set up and demonstrate, however in large enterprise setting, we generally find Microsoft SQL or Oracle et al. vendors.  For example, to connect with oracle, we will need oracle specific drivers and clients installed on the box where the script runs.

The databases that we have experimented with are as follows:

  1. Oracle
  2. Mysql
  3. DB2
  4. sql-server

Note: If you are using Jython, then life is much easier with database connections because jdbc libraries have already done the heavy lifting of abstracting out too many specific details on connecting to multiple database vendors.