DatabaseLibrary

Scope: global
Named arguments: supported

Introduction

Database Library contains utilities meant for 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.

References:

+ Database API Specification 2.0 - http://www.python.org/dev/peps/pep-0249/

+ Lists of DB API 2.0 - http://wiki.python.org/moin/DatabaseInterfaces

+ Python Database Programming - http://wiki.python.org/moin/DatabaseProgramming/

Notes:

compatible* - or at least theoretically it should be compatible. Currently tested only with postgresql (using psycopg2).

Example Usage:

# Setup
Connect to Database
# Guard assertion (verify that test started in expected state).
Check if not exists in database select id from person where first_name = 'Franz Allan' and last_name = 'See'
# Drive UI to do some action
Go To http://localhost/person/form.html # From selenium library
Input Text name=first_name Franz Allan # From selenium library
Input Text name=last_name See # From selenium library
Click Button Save # From selenium library
# Log results
@{queryResults} Query select * from person
Log Many @{queryResults}
# Verify if persisted in the database
Check if exists in database select id from person where first_name = 'Franz Allan' and last_name = 'See'
# Teardown
Disconnect from Database

Shortcuts

Check If Exists In Database  ·  Check If Not Exists In Database  ·  Connect To Database  ·  Disconnect From Database  ·  Query

Keywords

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

For example, given we have a table person with the following data:
id first_name last_name
1 Franz Allan See

When you have the following assertions in your robot
Check If Exists In Database select id from person where first_name = 'Franz Allan'
Check If Exists In Database select id from person where first_name = 'John'

Then you will get the following:
Check If Exists In Database select id from person where first_name = 'Franz Allan' # PASS
Check If Exists In Database select id from person where first_name = 'John' # FAIL
Check If Not Exists In Database selectStatement This is the negation of check_if_exists_in_database.

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

For example, given we have a table person with the following data:
id first_name last_name
1 Franz Allan See

When you have the following assertions in your robot
Check If Not Exists In Database select id from person where first_name = 'John'
Check If Not Exists In Database select id from person where first_name = 'Franz Allan'

Then you will get the following:
Check If Not Exists In Database select id from person where first_name = 'John' # PASS
Check If Not Exists In Database select id from person where first_name = 'Franz Allan' # FAIL
Connect To Database dbapiModuleName=None, dbName=None, dbUsername=None, dbPassword=None, dbConfigFile=./resources/db.cfg Loads the DB API 2.0 module given dbapiModuleName then uses it to connect to the database using dbName, dbUsername, and dbPassword.

Optionally, you can specify a dbConfigFile wherein it will load the default property values for dbapiModuleName, dbName dbUsername and dbPassword (note: specifying dbapiModuleName, dbName dbUsername or dbPassword 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.

Example usage:
# explicitly specifies all db property values
Connect To Database psycopg2 my_db postgres s3cr3t

# loads all property values from default.cfg
Connect To Database dbConfigFile=default.cfg

# loads all property values from ./resources/db.cfg
Connect To Database

# uses explicit dbapiModuleName and dbName but uses the dbUsername and dbPassword in 'default.cfg'
Connect To Database psycopg2 my_db_test dbConfigFile=default.cfg

# uses explicit dbapiModuleName and dbName but uses the dbUsername and dbPassword in './resources/db.cfg'
Connect To Database psycopg2 my_db_test
Disconnect From Database Disconnects from the database.

For example:
Disconnect From Database # disconnects from current connection to the database
Query selectStatement 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 (i.e. instead of "select * from my_table", try "select id, col_1, col_2 from my_table").

For example, given we have a table person with the following data:
id first_name last_name
1 Franz Allan See

When you do the following:
@{queryResults} Query select * from person
Log Many @{queryResults}

You will get the following: [1, 'Franz Allan', 'See']

Also, you can do something like this:
${queryResults} Query select first_name, last_name from person
Log ${queryResults[0][1]}, ${queryResults[0][0]}

And get the following See, Franz Allan