How to build a simple test data loader

Intro

SQL projects are not very popular among the testing family. Test Engineers usually prefer to work with UI or API. But there are a lot of projects where business logic lies in relational databases or data warehouses and sooner or later you will need to do some testing on DB/DW. 

In those projects, the same way as in other ones, manual testing is still a valid approach and requires preparation of multiple test data configurations. This can be painful when working with multiple test data sql scripts, plenty of DB objects and DB schemas. In this article, I’ll show you how to build a simple test data loader.

User Interface

We will be using Python and a SQL Server as data storage. Firstly let’s build a simple UI for a desktop app. I assume all libraries are already installed, and if not, then “pip install [package]”

Setting Window

import sys
import random
from PyQt4.QtCore import pyqtSlot,SIGNAL,SLOT
from PyQt4.QtGui import *
from PyQt4.QtCore import *
import datetime

       app = QApplication(sys.argv)
       w = QWidget()
       w.setWindowTitle('Test Data Generator')
       w.resize(180, 240)
       w.setFixedSize(800, 460)

       w.setStyleSheet("background-color: white;")

Code Block 1. Setting window.

Starting with an empty window as a widget. 

Progress Bar

Now let’s add a progress bar to our test data loader. It will tell us when loading or deleting data is over. The initial value is obviously set to 0.

class QProgBar(QProgressBar):

       value = 0

   @pyqtSlot()
   def increaseValue(progressBar):
           progressBar.setValue(progressBar.value)
           progressBar.value = progressBar.value+1

bar = QProgBar(w)
bar.resize(320,30)
bar.setValue(0)
bar.move(460,400)

Code Block 2. Setting the progress bar

Code Block 2. contains a few things to be explained:

• increaseValue – a method that will increase the progress bar value
• QProgBar(w) – The QProgressBar widget provides the progress bar

Labels

We need labels for buttons, drop downs, input fields etc.

lName = QLabel(w)
{...}

lName.setText("Name")
lName.move(60,60)
{...}

Code Block 3. Setting labels

And the explanation of Code Block 3.

• {…} – Obviously, I will not put all the code, so from now on I will use this {…} to inform “code continuation here”.
• QLabel(w) -The QLabel widget provides a text

Buttons, checkboxes and input fields

Let’s go through some more elements in our app, starting with action buttons.

btnDelete = QPushButton('Delete Test Data', w)
btnLoad = QPushButton('Load Test Data', w)
{...}

schema = QComboBox(w)
schema.addItem("Test Schema")
schema.move(200,10)
schema.resize(120,25)

database = QLineEdit(w)
database.move(30, 10)
database.resize(120,25)
database.setPlaceholderText("DB Name")

name1 = QCheckBox('Name 1', w)
name1.move(30, 85)
name1.setChecked(True)
{...}

Code Block 4. Setting labels

The app elements defined in Code Block 4 are:

• QPushButton(‘’) – The QPushButton widget provides a button
• QComboBox(w) – The QComboBox widget is a drop-down list
• QLineEdit(w) – The QLineEdit widget is a one-line text input.
• QCheckBox – The QCheckBox widget provides a checkbox with a text label

Actions

Now comes the fun part. We will create the actions and connect signals with slots.

@pyqtSlot()
def on_click_loadData():
       bar.setValue(25)
       nameList = []
       {...}

       db = str(database.text())
       {...}

       if(name1.isChecked()==True):
       nameList.append("Name 1")
       {...}
       if(len(nameList)>0):
       Name = str(nameList[randomValueGenerator(len(nameList))-1])

       bar.setValue(50)
       if(str(schema.currentText())=='Test Schema'):
       addTestData(db, 'Test', Name, {...})
       {...}
       bar.setValue(75)
       bar.setValue(100)

def on_click_deleteData():
       bar.setValue(25)
       db = str(database.text())
       bar.setValue(50)
       if(str(schema.currentText())=='Test Schema'):
       deleteTestData(db, 'Test')
       {...}
       bar.setValue(75)
       bar.setValue(100)

{...}

def randomValueGenerator(len):
       return random.randint(1,len)

btnStructure.clicked.connect(on_click_createStructure)
btnStructure.move(20, 400)
btnStructure.resize(120,30)

btnLoad.clicked.connect(on_click_loadData)
btnLoad.move(160, 400)
btnLoad.resize(120,30)

btnDelete.clicked.connect(on_click_deleteData)
btnDelete.move(300, 400)
btnDelete.resize(120,30)

w.show()
app.exec_()

Code Block 5. Setting labels

It’s a pretty long piece of code. Let’s take a closer look at what we have just implemented:

• on_click_loadData() – we call addTestData() function and make use of 

                                                         btn.clicked.connect() function

• on_click_deleteData() – we call deleteTestData() function and make use of 

                                                          btn.clicked.connect() function

• randomValueGenerator() – returns random int value from the defined range
• btn.clicked.connect() – we connect signal with slot
• w.show() – show widget
• app.exec_() -execute an application

DB actions

Our app needs SQL actions connected with button actions. We will use the pyodbc connector to connect to SQL Server DB. I assume DB schema is already present and we do not need to create DB objects like tables etc.

Add test data

The addTestData function takes values from the UI and passes them to the SQL query. But let’s go through the entire code:

• Opening connection to SQL Server DB by defining dbAddress
• Setting id value – if table id is not an auto increment one, we need to know the next value of id to be used
• SQL Query definition. We will pass a few values from the UI.

import pyodbc
import ConfigParser

config = ConfigParser.RawConfigParser()
config.read('../resources/env.properties')
list = []
login = 'myFancyLogin'

def addTestData(db, schema, Name {...}):
   try:
      dbAddress = "Driver={SQL Server};Server=localhost\SQLEXPRESS; 
                   Database="+db+";Trusted_Connection=yes; 
                   uid="+login+";pwd="
      cnx = pyodbc.connect(dbAddress)
      cursor = cnx.cursor()

      id = "SELECT top 1 ID FROM ["+schema+"].[candidates] ORDER BY ID 
            DESC"
      id = returnValue(cnx, cursor, id)
      Id = str(id + 1)

      schema = str(schema)

      testQuery = 'SELECT DB_NAME() AS [Current Database];'
      candidates = "INSERT INTO ["+schema+"].[candidates]      
                    VALUES("+Id+",'"+Name+"',{...}")"
      returnDBName(cnx, cursor, testQuery)

      list = [candidates]
      executeQuery(cnx, cursor, list)

   except pyodbc.Error as e:
      print(e)
      print 'errors in addTestData function'
   else:
      cnx.close()

Code Block 6. Add test data method

Delete test data

Deletion of test data is handled by the deleteTestData(db,schema) function. It has just 2 parameters (db,schema). It means that we want to clear the entire table without carrying what is inside.

def deleteTestData(db, schema):
   try:
      dbAddress = "Driver={SQL Server};Server=localhost\SQLEXPRESS;
                   Database="+db+";Trusted_Connection=yes;
                   uid="+login+";pwd="
      cnx = pyodbc.connect(dbAddress)
      cursor = cnx.cursor()
 
      schema = str(schema)
 
      testQuery = 'SELECT DB_NAME() AS [Current Database];'
      candidates = "DELETE FROM ["+schema+"].[candidates]"
      candidatesProcessed = "DELETE FROM 
                             ["+schema+"].[candidatesProcessed]"
 
      returnDBName(cnx, cursor, testQuery)
 
      list = [candidates, candidatesProcessed]
      executeQuery(cnx, cursor, list)

   except:
      print 'errors in deleteTestData function'
   else:
      cnx.close()

Code Block 7. Delete test data method

Utils

And some utils function used by addTestData() and deleteTestData() functions:

def executeQuery(cnx, cursor, list):
   for i in list:
   cursor.execute(i)
   cnx.commit()

def returnDBName(cnx, cursor, dbQuery):
   cursor.execute(dbQuery)
   Value = cursor.fetchone()
   Value = Value[0]
   Value = str(Value)

Code Block 8. Util functions

Exe File

Currently, we can use our code if Python is installed and we can compile the code, but what if we just want to have an executable file? The py2exe library allows to create executable file from Python code (Code Block 9):

from distutils.core import setup
import py2exe
      
setup(windows=[name of the file with widget],
   data_files = data_files,
   options={ 'py2exe': {
      "includes":['sip'],
      "dll_excludes": ['MSVFW32.dll',
      'AVIFIL32.dll',
      'AVICAP32.dll',
      'ADVAPI32.dll',
      'CRYPT32.dll',
      'WLDAP32.dll',
      'MSVCP90.dll']
      }
   })

Code Block 9. Creating .exe file

The result

And here’s the outcome of our work. Hope you enjoyed the tutorial!