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  ·  Connect To Database Using Custom Params  ·  Delete All Rows From Table  ·  Description  ·  Disconnect From Database  ·  Execute Sql Script  ·  Query  ·  Row Count  ·  Row Count Is 0  ·  Row Count Is Equal To X  ·  Row Count Is Greater Than X  ·  Row Count Is Less Than X  ·  Table Must Exist

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, dbHost=localhost, dbPort=5432, 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 | tiger.foobar.com | 5432

# 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
Connect To Database Using Custom Params dbapiModuleName=None, db_connect_string= Loads the DB API 2.0 module given dbapiModuleName then uses it to connect to the database using the map string db_custom_param_string.

Example usage:
# for psycopg2
Connect To Database Using Custom Params psycopg2 database='my_db_test', user='postgres', password='s3cr3t', host='tiger.foobar.com', port=5432

# for JayDeBeApi
Connect To Database Using Custom Params JayDeBeApi 'oracle.jdbc.driver.OracleDriver', 'my_db_test', 'system', 's3cr3t'
Delete All Rows From Table tableName Delete all the rows within a given table.

For example, given we have a table person in a database

When you do the following:
Delete All Rows From Table person

If all the rows can be successfully deleted, then you will get:
Delete All Rows From Table person # PASS
If the table doesn't exist or all the data can't be deleted, then you will get:
Delete All Rows From Table first_name # FAIL
Description selectStatement Uses the input selectStatement to query a table in the db which will be used to determine the description.

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} Description select * from person
Log Many @{queryResults}

You will get the following: [Column(name='id', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)] [Column(name='first_name', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)] [Column(name='last_name', type_code=1043, display_size=None, internal_size=255, precision=None, scale=None, null_ok=None)]
Disconnect From Database Disconnects from the database.

For example:
Disconnect From Database # disconnects from current connection to the database
Execute Sql Script sqlScriptFileName Executes the content of the sqlScriptFileName as SQL commands. Useful for setting the database to a known state before running your tests, or clearing out your test data after running each a test.

SQL commands are expected to be delimited by a semi-colon (';').

For example: delete from person_employee_table; delete from person_table; delete from employee_table;

Also, the last SQL command can optionally omit its trailing semi-colon.

For example: delete from person_employee_table; delete from person_table; delete from employee_table

Given this, that means you can create spread your SQL commands in several lines.

For example: delete from person_employee_table; delete from person_table; delete from employee_table

However, lines that starts with a number sign (#) are treated as a commented line. Thus, none of the contents of that line will be executed.

For example: # Delete the bridging table first... delete from person_employee_table; # ...and then the bridged tables. delete from person_table; delete from employee_table
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
Row Count selectStatement Uses the input selectStatement to query the database and returns the number of rows from the query.

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

When you do the following:
${rowCount} Row Count select * from person
Log ${rowCount}

You will get the following: 2

Also, you can do something like this:
${rowCount} Row Count select * from person where id = 2
Log ${rowCount}

And get the following 1
Row Count Is 0 selectStatement Check if any rows are returned from the submitted selectStatement. If there are, 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
Row Count is 0 select id from person where first_name = 'Franz Allan'
Row Count is 0 select id from person where first_name = 'John'

Then you will get the following:
Row Count is 0 select id from person where first_name = 'Franz Allan' # FAIL
Row Count is 0 select id from person where first_name = 'John' # PASS
Row Count Is Equal To X selectStatement, numRows Check if the number of rows returned from selectStatement is equal to the value submitted. If not, 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
2 Jerry Schneider

When you have the following assertions in your robot
Row Count Is Equal To X select id from person 1
Row Count Is Equal To X select id from person where first_name = 'John' 0

Then you will get the following:
Row Count Is Equal To X select id from person 1 # FAIL
Row Count Is Equal To X select id from person where first_name = 'John' 0 # PASS
Row Count Is Greater Than X selectStatement, numRows Check if the number of rows returned from selectStatement is greater than the value submitted. If not, 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
2 Jerry Schneider

When you have the following assertions in your robot
Row Count Is Greater Than X select id from person 1
Row Count Is Greater Than X select id from person where first_name = 'John' 0

Then you will get the following:
Row Count Is Greater Than X select id from person 1 # PASS
Row Count Is Greater Than X select id from person where first_name = 'John' 0 # FAIL
Row Count Is Less Than X selectStatement, numRows Check if the number of rows returned from selectStatement is less than the value submitted. If not, 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
2 Jerry Schneider

When you have the following assertions in your robot
Row Count Is Less Than X select id from person 3
Row Count Is Less Than X select id from person where first_name = 'John' 1

Then you will get the following:
Row Count Is Less Than X select id from person 3 # PASS
Row Count Is Less Than X select id from person where first_name = 'John' 1 # FAIL
Table Must Exist tableName Check if the table given exists in the database.

For example, given we have a table person in a database

When you do the following:
Table Must Exist person

Then you will get the following:
Table Must Exist person # PASS
Table Must Exist first_name # FAIL