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 databaseselect id from person where first_name = 'Franz Allan' and last_name = 'See' # Drive UI to do some action Go Tohttp://localhost/person/form.html # From selenium libraryInput Textname=first_nameFranz Allan# From selenium libraryInput Textname=last_nameSee# From selenium libraryClick ButtonSave # From selenium library# Log results @{queryResults}Queryselect * from person Log Many@{queryResults} # Verify if persisted in the database Check if exists in databaseselect 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,
dbNamedbUsername 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
