Library version:2.1.3
Library scope:GLOBAL

Introduction

The Database Library for Robot Framework allows you to query a database and verify the results. It requires an appropriate Python module to be installed separately - depending on your database, like e.g. oracledb or pymysql.

Table of contents

Requirements

  • Python
  • Robot Framework
  • Python database module you're going to use - e.g. oracledb

Installation

pip install robotframework-databaselibrary

Don't forget to install the required Python database module!

Basic usage examples

* Settings *
Library       DatabaseLibrary
Test Setup    Connect To My Oracle DB

* Keywords *
Connect To My Oracle DB
    Connect To Database
    ...    oracledb
    ...    db_name=db
    ...    db_user=my_user
    ...    db_password=my_pass
    ...    db_host=127.0.0.1
    ...    db_port=1521

* Test Cases *
Get All Names
    ${Rows}=    Query    select FIRST_NAME, LAST_NAME from person
    Should Be Equal    ${Rows}[0][0]    Franz Allan
    Should Be Equal    ${Rows}[0][1]    See
    Should Be Equal    ${Rows}[1][0]    Jerry
    Should Be Equal    ${Rows}[1][1]    Schneider

Person Table Contains Expected Records
    ${sql}=    Catenate    select LAST_NAME from person
    Check Query Result    ${sql}    contains    See
    Check Query Result    ${sql}    equals      Schneider    row=1

Wait Until Table Gets New Record
    ${sql}=    Catenate    select LAST_NAME from person
    Check Row Count    ${sql}    >    2    retry_timeout=5s

Person Table Contains No Joe
    ${sql}=    Catenate    SELECT id FROM person
    ...                    WHERE FIRST_NAME= 'Joe'
    Check Row Count    ${sql}   ==    0

Handling multiple database connections

The library can handle multiple connections to different databases using aliases. An alias is set while creating a connection and can be passed to library keywords in a corresponding argument.

Example

* Settings *
Library          DatabaseLibrary
Test Setup       Connect To All Databases
Test Teardown    Disconnect From All Databases

* Keywords *
Connect To All Databases
    Connect To Database
    ...    psycopg2
    ...    db_name=db
    ...    db_user=db_user
    ...    db_password=pass
    ...    db_host=127.0.0.1
    ...    db_port=5432
    ...    alias=postgres
    Connect To Database
    ...    pymysql
    ...    db_name=db
    ...    db_user=db_user
    ...    db_password=pass
    ...    db_host=127.0.0.1
    ...    db_port=3306
    ...    alias=mysql

* Test Cases *
Using Aliases
    ${names}=    Query    select LAST_NAME from person    alias=postgres
    Execute Sql String    drop table XYZ                  alias=mysql

Switching Default Alias
    Switch Database    postgres
    ${names}=    Query    select LAST_NAME from person
    Switch Database    mysql
    Execute Sql String    drop table XYZ

Connection examples for different DB modules

Oracle (oracle_db)

# Thin mode is used by default
Connect To Database
...    oracledb
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1521

# Thick mode with default location of the Oracle Instant Client
Connect To Database
...    oracledb
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1521
...    oracle_driver_mode=thick

# Thick mode with custom location of the Oracle Instant Client
Connect To Database
...    oracledb
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1521
...    oracle_driver_mode=thick,lib_dir=C:/instant_client_23_5

PostgreSQL (psycopg2)

Connect To Database
...    psycopg2
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=5432

Microsoft SQL Server (pymssql)

# UTF-8 charset is used by default
Connect To Database
...    pymssql
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1433

# Specifying a custom charset
Connect To Database
...    pymssql
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1433
...    db_charset=cp1252

MySQL (pymysql)

# UTF-8 charset is used by default
Connect To Database
...    pymysql
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=3306

# Specifying a custom charset
Connect To Database
...    pymysql
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=3306
...    db_charset=cp1252

IBM DB2 (ibm_db)

Connect To Database
...    ibm_db_dbi
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=50000

MySQL via ODBC (pyodbc)

# ODBC driver name is required
# ODBC driver itself has to be installed
Connect To Database
...    pyodbc
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=3306
...    odbc_driver={MySQL ODBC 9.2 ANSI Driver}

# Specifying a custom charset if needed
Connect To Database
...    pyodbc
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=3306
...    odbc_driver={MySQL ODBC 9.2 ANSI Driver}
...    db_charset=latin1

Oracle via JDBC (jaydebeapi)

# Username and password must be set as a dictionary
VAR  &{CREDENTIALS}  user=db_user  password=pass

# JAR file with Oracle JDBC driver is required
# Jaydebeapi is not "natively" supported by the Database Library,
# so using the custom parameters
Connect To Database
...    jaydebeapi
...    jclassname=oracle.jdbc.driver.OracleDriver
...    url=jdbc:oracle:thin:@127.0.0.1:1521/db
...    driver_args=${CREDENTIALS}
...    jars=C:/ojdbc17.jar

# Set if getting error 'Could not commit/rollback with auto-commit enabled'
Set Auto Commit    False

# Set for automatically removing trailing ';' (might be helpful for Oracle)
Set Omit Trailing Semicolon    True

SQLite (sqlite3)

# Using custom parameters required
Connect To Database
...    sqlite3
...    database=./my_database.db
...    isolation_level=${None}

Teradata (teradata)

Connect To Database
...    teradata
...    db_name=db
...    db_user=db_user
...    db_password=pass
...    db_host=127.0.0.1
...    db_port=1025

Using configuration file

The Connect To Database keyword allows providing the connection parameters in two ways:

  • As keyword arguments
  • In a configuration file - a simple list of key=value pairs, set inside an alias section.

You can use only one way or you can combine them:

  • The keyword arguments are taken by default
  • If no keyword argument is provided, a parameter value is searched in the config file

Along with commonly used connection parameters, named exactly as keyword arguments, a config file can contain any other DB module specific parameters as key/value pairs. If same custom parameter is provided both as a keyword argument and in config file, the keyword argument value takes precedence.

The path to the config file is set by default to ./resources/db.cfg. You can change it using an according parameter in the Connect To Database keyword.

A config file must contain at least one section name - the connection alias, if used (see Handling multiple database connections), or [default] if no aliases are used.

Config file examples

Config file with default alias (equal to using no aliases at all)

[default]
db_module=psycopg2
db_name=yourdbname
db_user=yourusername
db_password=yourpassword
db_host=yourhost
db_port=yourport

Config file with a specific alias

[myoracle]
db_module=oracledb
db_name=yourdbname
db_user=yourusername
db_password=yourpassword
db_host=yourhost
db_port=yourport

Config file with some params only

[default]
db_password=mysecret

Config file with some custom DB module specific params

[default]
my_custom_param=value

Inline assertions

Keywords, that accept arguments assertion_operator <AssertionOperator> and expected_value, perform a check according to the specified condition - using the Assertion Engine.

Examples:

Check Row Count SELECT id FROM person == 2
Check Query Result SELECT first_name FROM person contains Allan

Retry mechanism

Assertion keywords, that accept arguments retry_timeout and retry_pause, support waiting for assertion to pass.

Setting the retry_timeout argument enables the mechanism - in this case the SQL request and the assertion are executed in a loop, until the assertion is passed or the retry_timeout is reached. The pause between the loop iterations is set using the retry_pause argument.

The argument values are set in Robot Framework time format - e.g. 5 seconds.

The retry mechanism is disabled by default - retry_timeout is set to 0.

Examples:

Check Row Count SELECT id FROM person == 2 retry_timeout=10 seconds
Check Query Result SELECT first_name FROM person contains Allan retry_timeout=5s retry_pause=1s

Logging query results

Keywords, that fetch results of a SQL query, print the result rows as a table in RF log.

  • A log head limit of 50 rows is applied, other table rows are truncated in the log message.
  • The limit and the logging in general can be adjusted any time in your tests using the Keyword Set Logging Query Results.

You can also setup the limit or disable the logging during the library import. Examples:

* Settings *
# Default behavior - logging of query results is enabled, log head is 50 rows.
Library    DatabaseLibrary

# Logging of query results is disabled, log head is 50 rows (default).
Library    DatabaseLibrary    log_query_results=False

# Logging of query results is enabled (default), log head is 10 rows.
Library    DatabaseLibrary    log_query_results_head=10

# Logging of query results is enabled (default), log head limit is disabled (log all rows).
Library    DatabaseLibrary    log_query_results_head=0

Commit behavior

While creating a database connection, the library doesn't explicitly set the autocommit behavior - so the default value of the Python DB module is used. According to Python DB API specification it should be disabled by default - which means each SQL transaction (even a simple SELECT) must contain a dedicated commit statement, if necessary.

The library manages it for you - keywords like Query or Execute SQL String perform automatically a commit after running the query (or a rollback in case of error).

You can turn off this automatic commit/rollback behavior using the no_transaction parameter. See docs of a particular keyword.

It's also possible to explicitly set the autocommit behavior on the Python DB module level - using the Set Auto Commit keyword. This has no impact on the automatic commit/rollback behavior in library keywords (described above).

Omitting trailing semicolon behavior

Some databases (e.g. Oracle) throw an exception, if you leave a semicolon (;) at the SQL string end. However, there are exceptional cases, when you need it even for Oracle - e.g. at the end of a PL/SQL block.

The library can handle it for you and remove the semicolon at the end of the SQL string. By default, it's decided based on the current database module in use:

  • For oracle_db and cx_Oracle, the trailing semicolon is removed
  • For other modules, the trailing semicolon is left as it is

You can also set this behavior explicitly:

Database modules compatibility

The library is basically compatible with any Python Database API Specification 2.0 module.

However, the actual implementation in existing Python modules is sometimes quite different, which requires custom handling in the library. Therefore, there are some modules, which are "natively" supported in the library - and others, which may work and may not.

Python modules currently "natively" supported

Oracle

oracledb

  • Both thick and thin client modes are supported - you can select one using the oracle_driver_mode parameter.
  • However, due to current limitations of the oracledb module, it's not possible to switch between thick and thin modes during a test execution session - even in different suites.

cx_Oracle

MySQL

PostgreSQL

MS SQL Server

SQLite

Teradata

IBM DB2

  • The Python package to be installed is ibm_db. It includes two modules - ibm_db and ibm_db_dbi.
  • Using ibm_db_dbi is highly recommended as only this module is Python DB API 2.0 compatible. See official docs.

ODBC

Kingbase

  • ksycopg2

Importing

Arguments

log_query_results
= True
log_query_results_head
= 50
warn_on_connection_overwrite
= True

Documentation

The library can be imported without any arguments:

* Settings *
Library    DatabaseLibrary

Use optional library import parameters:

  • log_query_results and log_query_results_head to disable Logging query results or setup the log head
  • warn_on_connection_overwrite to disable the warning about overwriting an existing connection

Keywords

Call Stored Procedure

Arguments

procedure_name str procedure_params
= None
List | None no_transaction
= False
bool alias
= None
str | None additional_output_params
= None
List | None 🏷 spName
= None
str | None 🏷 spParams
= None
List | None 🏷 sansTran
= None
bool | None

Documentation

Calls a stored procedure procedure_name with the procedure_params - a list of parameters the procedure requires. Returns two lists - the parameter values and the result sets.

Use the special CURSOR value for OUT params, which should receive result sets - relevant only for some databases (e.g. Oracle or PostgreSQL).

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use the additional_output_params list for OUT params of a procedure in MSSQL.

Some parameters were renamed in version 2.0

The old parameters spName, spParams and sansTran are deprecated, please use new parameters procedure_name, procedure_params and no_transaction instead.

The old parameters will be removed in future versions.

Handling parameters and result sets

Handling the input and output parameters and the result sets is very different depending on the database itself and on the Python database driver - i.e. how it implements the cursor.callproc() function.

Common case (e.g. MySQL)

Generally a procedure call requires all parameter values (IN and OUT) put together in a list - procedure_params.

Calling the procedure returns two lists:

  • Param values - the copy of procedure parameters (modified, if the procedure changes the OUT params). The list is empty, if procedures receives no params.
  • Result sets - the list of lists, each of them containing results of some query, if the procedure returns them.

Oracle (oracledb, cx_Oracle)

Oracle procedures work fine with simple IN and OUT params, but require some special handling of result sets.

Simple case with IN and OUT params (no result sets)

Consider the following procedure:

          CREATE OR REPLACE PROCEDURE
          get_second_name (person_first_name IN VARCHAR, person_second_name OUT VARCHAR) AS
          BEGIN
            SELECT last_name
            INTO person_second_name
            FROM person
            WHERE first_name = person_first_name;
          END;

Calling the procedure in Robot Framework:

          @{params}=         Create List    Jerry    OUTPUT
          # Second parameter value can be anything, it will be replaced anyway
          
          ${param values}    ${result sets}=    Call Stored Procedure    get_second_name    ${params}
          # ${param values} = ['Jerry', 'Schneider']
          # ${result sets} = []

Oracle procedure returning a result set

If a procedure in Oracle should return a result set, it must take OUT parameters of a special type - SYS_REFCURSOR.

Consider the following procedure:

          get_all_second_names (second_names_cursor OUT SYS_REFCURSOR) AS
          BEGIN
            OPEN second_names_cursor for
            SELECT LAST_NAME FROM person;
          END;

Calling the procedure in Robot Framework requires the special value CURSOR for the OUT parameters, they will be converted to appropriate DB variables before calling the procedure.

          @{params}=    Create List    CURSOR
          # The parameter must have this special value CURSOR
          
          ${param values}    ${result sets}=    Call Stored Procedure    get_all_second_names    ${params}
          # ${param values} = [<oracledb.Cursor on <oracledb.Connection ...>>]
          # ${result sets} = [[('Franz Allan',), ('Jerry',)], [('See',), ('Schneider',)]]

Oracle procedure returning multiple result sets

If a procedure takes multiple OUT parameters of the SYS_REFCURSOR type, they all must have the special CURSOR value when calling the procedure:

          @{params} =        Create List         CURSOR    CURSOR
          ${param values}    ${result sets} =    Call Stored Procedure    Get_all_first_and_second_names    ${params}
          # ${param values} = [<oracledb.Cursor on <oracledb.Connection ...>>, <oracledb.Cursor on <oracledb.Connection ...>>]
          # ${result sets}  = [[('Franz Allan',), ('Jerry',)], [('See',), ('Schneider',)]]

PostgreSQL (psycopg2, psycopg3)

PostgreSQL doesn't return single values as params, only as result sets. It also supports special handling of result sets over OUT params of a special type (like Oracle).

Simple case with IN and OUT params (no CURSOR parameters)

Consider the following procedure:

          CREATE FUNCTION
          get_second_name (IN person_first_name VARCHAR(20),
          OUT person_second_name VARCHAR(20))
          LANGUAGE plpgsql
          AS
          '
          BEGIN
            SELECT LAST_NAME INTO person_second_name
            FROM person
            WHERE FIRST_NAME = person_first_name;
          END
          ';

Calling the procedure in Robot Framework:

          @{params}=    Create List    Jerry
          ${param values}    ${result sets}=    Call Stored Procedure    get_second_name    ${params}
          # ${param values} = ['Jerry']
          # ${result sets} = [[('Schneider',)]]

PostgreSQL procedure with CURSOR parameters

If a procedure in PostgreSQL should return a proper result set, it must take OUT parameters of a special type - refcursor.

Consider the following procedure:

          CREATE FUNCTION
          get_all_first_and_second_names(result1 refcursor, result2 refcursor)
          RETURNS SETOF refcursor
          LANGUAGE plpgsql
          AS
          '
          BEGIN
            OPEN result1 FOR SELECT FIRST_NAME FROM person;
            RETURN NEXT result1;
            OPEN result2 FOR SELECT LAST_NAME FROM person;
            RETURN NEXT result2;
          END
          ';

Calling the procedure in Robot Framework requires the special value CURSOR for the OUT parameters, they will be converted to appropriate DB variables before calling the procedure.

          @{params}=    Create List    CURSOR    CURSOR
          # The parameters must have this special value CURSOR
          
          ${param values}    ${result sets}=    Call Stored Procedure    get_all_first_and_second_names    ${params}
          # ${param values} = ['CURSOR_0', 'CURSOR_1']
          # ${result sets} = [[('Franz Allan',), ('Jerry',)], [('See',), ('Schneider',)]

MS SQL Server (pymssql)

The pymssql driver doesn't natively support getting the OUT parameter values after calling a procedure.

  • This requires special handling of OUT parameters using the additional_output_params argument.
  • Furthermore, it's not possible to fetch the OUT parameter values for a procedure, which returns a result set AND has OUT parameters.

Simple case with IN and OUT params (no result sets)

Consider the following procedure:

          CREATE PROCEDURE
          return_out_param_without_result_sets
          @my_input VARCHAR(20),
          @my_output INT OUTPUT
          AS
          BEGIN
           IF @my_input = 'give me 1'
              BEGIN
                  SELECT @my_output = 1;
              END
              ELSE
              BEGIN
                  SELECT @my_output = 0;
              END
          END;

Calling the procedure in Robot Framework requires putting the IN parameters as usual in the procedure_params argument, but the sample values of OUT parameters must be put in the argument additional_output_params.

          @{params}=    Create List    give me 1
          @{out_params}=    Create List    ${9}
          ${param values}    ${result sets}=    Call Stored Procedure    return_out_param_without_result_sets
          ...    ${params}    additional_output_params=${out_params}
          # ${result sets} = []
          # ${param values} = ('give me 1', 1)

The library uses the sample values in the additional_output_params list to determine the number and the type of OUT parameters - so they are type-sensitive, the type must be the same as in the procedure itself.

MS SQL procedure returning a result set (no OUT params)

If a procedure doesn't have any OUT params and returns only result sets, they are handled in a normal way. Consider the following procedure:

          CREATE PROCEDURE get_all_first_and_second_names
          AS
          BEGIN
            SELECT FIRST_NAME FROM person;
            SELECT LAST_NAME FROM person;
            RETURN;
          END;

Calling the procedure in Robot Framework:

          ${param values}    ${result sets}=    Call Stored Procedure    get_all_first_and_second_names
          ${param values} = ()
          ${result sets} = [[('Franz Allan',), ('Jerry',)], [('See',), ('Schneider',)]]

MS SQL procedure returning result sets AND OUT params

This case is not fully supported by the library - the OUT params won't be fetched.

Check If Exists In Database

Arguments

select_statement str 🏷 no_transaction
= False
bool 🏷 msg
= None
str | None 🏷 alias
= None
str | None 🏷 parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

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

Set optional input no_transaction to True to run command without an explicit transaction commit or rollback.

The default error message can be overridden with the msg argument.

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

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' msg=my error message
Check If Exists In Database SELECT id FROM person WHERE first_name = 'Franz Allan' alias=my_alias
Check If Exists In Database SELECT id FROM person WHERE first_name = 'John' no_transaction=True
@{parameters} Create List John
Check If Exists In Database SELECT id FROM person WHERE first_name = %s parameters=${parameters}

Check If Not Exists In Database

Arguments

selectStatement str sansTran
= False
bool msg
= None
str | None alias
= None
str | None parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

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.

Set optional input sansTran to True to run command without an explicit transaction commit or rollback.

The default error message can be overridden with the msg argument.

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

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' msg=my error message
Check If Not Exists In Database SELECT id FROM person WHERE first_name = 'Franz Allan' alias=my_alias
Check If Not Exists In Database SELECT id FROM person WHERE first_name = 'John' sansTran=True
@{parameters} Create List John
Check If Not Exists In Database SELECT id FROM person WHERE first_name = %s parameters=${parameters}

Check Query Result

Arguments

select_statement str assertion_operator AssertionOperator expected_value Any row
= 0
col
= 0
assertion_message
= None
str | None no_transaction
= False
bool alias
= None
str | None parameters
= None
Tuple | None retry_timeout
= 0 seconds
retry_pause
= 0.5 seconds
🏷 selectStatement
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Check value in query result returned from select_statement using assertion_operator and expected_value. The value position in results can be adjusted using row and col parameters (0-based). See Inline assertions for more details.

The assertion in this keyword is type sensitive! The expected_value is taken as a string, no argument conversion is performed. Use RF syntax like ${1} for numeric values.

Use optional assertion_message to override the default error message.

Set no_transaction to True to run command without explicit transaction rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Use retry_timeout and retry_pause parameters to enable waiting for assertion to pass. See Retry mechanism for more details.

Some parameters were renamed in version 2.0

The old parameters selectStatement and sansTran are deprecated, please use new parameters select_statement and no_transaction instead.

The old parameters will be removed in future versions.

Examples

Check Query Result SELECT first_name FROM person contains Allan
Check Query Result SELECT first_name, last_name FROM person == Schneider row=1 col=1
Check Query Result SELECT id FROM person WHERE first_name = 'John' == 2 # Fails, if query returns an integer value
Check Query Result SELECT id FROM person WHERE first_name = 'John' == ${2} # Works, if query returns an integer value
Check Query Result SELECT first_name FROM person equal Franz Allan assertion_message=my error message
Check Query Result SELECT first_name FROM person inequal John alias=my_alias
Check Query Result SELECT first_name FROM person contains Allan no_transaction=True
@{parameters} Create List John
Check Query Result SELECT first_name FROM person contains Allan parameters=${parameters}

Check Row Count

Arguments

select_statement str assertion_operator AssertionOperator expected_value int assertion_message
= None
str | None no_transaction
= False
bool alias
= None
str | None parameters
= None
Tuple | None retry_timeout
= 0 seconds
retry_pause
= 0.5 seconds
🏷 selectStatement
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Check the number of rows returned from select_statement using assertion_operator and expected_value. See Inline assertions for more details.

Use assertion_message to override the default error message.

Set no_transaction to True to run command without explicit transaction rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Use retry_timeout and retry_pause parameters to enable waiting for assertion to pass. See Retry mechanism for more details.

Some parameters were renamed in version 2.0

The old parameters selectStatement and sansTran are deprecated, please use new parameters select_statement and no_transaction instead.

The old parameters will be removed in future versions.

Examples

Check Row Count SELECT id FROM person WHERE first_name = 'John' == 1
Check Row Count SELECT id FROM person WHERE first_name = 'John' >= 2 assertion_message=my error message
Check Row Count SELECT id FROM person WHERE first_name = 'John' inequal 3 alias=my_alias
Check Row Count SELECT id FROM person WHERE first_name = 'John' less than 4 no_transaction=True
@{parameters} Create List John
Check Row Count SELECT id FROM person WHERE first_name = %s equals 5 parameters=${parameters}

Connect To Database

Arguments

db_module
= None
str | None db_name
= None
str | None db_user
= None
str | None db_password
= None
str | None db_host
= None
str | None db_port
= None
int | None db_charset
= None
str | None odbc_driver
= None
str | None config_file
= None
str | None oracle_driver_mode
= None
str | None alias
= default
str ** custom_connection_params

Documentation

Creates a database connection using the DB API 2.0 db_module and the parameters provided. Along with listed commonly used arguments (db_name, db_host etc.) you can set any other DB module specific parameters as key/value pairs.

Use config_file to provide a path to configuration file with connection parameters to be used along with / instead of keyword arguments. If no specified, it defaults to ./resources/db.cfg. See Using configuration file for more details.

All params are optional, although db_module must be set - either as keyword argument or in config file. If some of the listed keyword arguments (db_name, db_host etc.) are not provided (i.e. left on default value None), they are normally not passed to the Python DB module at all, except:

  • db_port - commonly used port number for known databases is set as fallback
  • db_charset - UTF8 is used as fallback for pymysql, pymssql and pyodbc
  • oracle_driver_mode - thin is used as fallback for oracledb

Other custom params from keyword arguments and config file are passed to the Python DB module as provided - normally as arguments for the connect() function. However, when using pyodbc or ibm_db_dbi, the connection is established using a connection string - so all the custom params are added into it instead of function arguments.

Set alias for Handling multiple database connections. If the same alias is given twice, then previous connection will be overridden.

The oracle_driver_mode is used to select the oracledb client mode. Allowed values are:

  • thin (default if omitted)
  • thick
  • thick,lib_dir=<PATH_TO_ORACLE_CLIENT>

By default, there is a warning when overwriting an existing connection (i.e. not closing it properly). This can be disabled by setting the warn_on_connection_overwrite parameter to False in the library import.

Some parameters were renamed in version 2.0

The old parameters dbapiModuleName, dbName, dbUsername, dbPassword, dbHost, dbPort, dbCharset, dbDriver, dbConfigFile and driverMode are deprecated, please use new parameters db_module, db_name, db_user, db_password, db_host, db_port, db_charset, odbc_driver, config_file and oracle_driver_mode instead.

The old parameters will be removed in future versions.

Basic examples

Connect To Database psycopg2 my_db user pass 127.0.0.1 5432
Connect To Database psycopg2 my_db user pass 127.0.0.1 5432 my_custom_param=value
Connect To Database psycopg2 my_db user pass 127.0.0.1 5432 alias=my_alias
Connect To Database config_file=my_db_params.cfg

See Connection examples for different DB modules.

Connect To Database Using Custom Connection String

Arguments

db_module
= None
str | None db_connect_string
=
str alias
= default
str 🏷 dbapiModuleName
= None
str | None

Documentation

Loads the DB API 2.0 module given db_module then uses it to connect to the database using the db_connect_string (parsed as single connection string or URI).

Use Connect To Database for passing custom connection params as named arguments.

Some parameters were renamed in version 2.0

The old parameter dbapiModuleName is deprecated, please use new parameter db_module instead.

The old parameter will be removed in future versions.

Example usage:

Connect To Database Using Custom Connection String psycopg2 postgresql://postgres:s3cr3t@tiger.foobar.com:5432/my_db_test
Connect To Database Using Custom Connection String oracledb username/pass@localhost:1521/orclpdb

Connect To Database Using Custom Params

Arguments

db_module
= None
str | None db_connect_string
=
str alias
= default
str 🏷 dbapiModuleName
= None
str | None

Documentation

DEPRECATED Use new Connect To Database keyword with custom parameters instead. The deprecated keyword will be removed in future versions.

Loads the DB API 2.0 module given db_module then uses it to connect to the database using the map string db_connect_string (parsed as a list of named arguments).

Use connect_to_database_using_custom_connection_string for passing all params in a single connection string or URI.

Some parameters were renamed in version 2.0

The old parameter dbapiModuleName is deprecated, please use new parameter db_module instead.

The old parameter will be removed in future versions.

Examples

Connect To Database Using Custom Params psycopg2 database='my_db_test', user='postgres', password='s3cr3t', host='tiger.foobar.com', port=5432
Connect To Database Using Custom Params jaydebeapi 'oracle.jdbc.driver.OracleDriver', 'my_db_test', 'system', 's3cr3t'
Connect To Database Using Custom Params oracledb user="username", password="pass", dsn="localhost/orclpdb"
Connect To Database Using Custom Params sqlite3 database="./my_database.db", isolation_level=None

Delete All Rows From Table

Arguments

table_name str no_transaction
= False
bool alias
= None
str | None 🏷 tableName
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Deletes all rows from table with table_name.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Some parameters were renamed in version 2.0

The old parameters tableName and sansTran are deprecated, please use new parameters table_name and no_transaction instead.

The old parameters will be removed in future versions.

Examples

Delete All Rows From Table person
Delete All Rows From Table person no_transaction=True
Delete All Rows From Table person alias=my_alias

Description

Arguments

select_statement str no_transaction
= False
bool alias
= None
str | None parameters
= None
Tuple | None 🏷 selectStatement
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Runs a query with the select_statement to determine the table description.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Some parameters were renamed in version 2.0

The old parameters selectStatement and sansTran are deprecated, please use new parameters select_statement and no_transaction instead.

The old parameters will be removed in future versions.

Examples

${Person table description}= Description select LAST_NAME from person
${Person table description}= Description select LAST_NAME from person no_transaction=True
${Person table description}= Description select LAST_NAME from person alias=postgres
@{parameters} Create List person
${Person table description}= Description SELECT * FROM %s parameters=${parameters}

Disconnect From All Databases

Documentation

Disconnects from all the databases - useful when testing with multiple database connections (aliases).

Disconnect From Database

Arguments

error_if_no_connection
= False
bool alias
= None
str | None

Documentation

Disconnects from the database.

By default, it's not an error if there was no open database connection - suitable for usage as a teardown. However, you can enforce it using the error_if_no_connection parameter.

Use alias to specify what connection should be closed if Handling multiple database connections.

Examples

Disconnect From Database
Disconnect From Database alias=postgres

Execute Sql Script

Arguments

script_path str no_transaction
= False
bool alias
= None
str | None split
= True
bool 🏷 sqlScriptFileName
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Executes the content of the SQL script file loaded from script_path as SQL commands.

SQL commands are expected to be delimited by a semicolon (';') - they will be split and executed separately. Set split to False to disable this behavior - in this case the entire script content will be passed to the database module for execution as a single command.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Some parameters were renamed in version 2.0

The old parameters sqlScriptFileName and sansTran are deprecated, please use new parameters script_path and no_transaction instead.

The old parameters will be removed in future versions.

Examples

Execute SQL Script insert_data_in_person_table.sql
Execute SQL Script insert_data_in_person_table.sql no_transaction=True
Execute SQL Script insert_data_in_person_table.sql alias=postgres
Execute SQL Script insert_data_in_person_table.sql split=False

Execute Sql String

Arguments

sql_string str no_transaction
= False
bool alias
= None
str | None parameters
= None
Tuple | None omit_trailing_semicolon
= None
bool | None 🏷 sqlString
= None
str | None 🏷 sansTran
= None
bool | None 🏷 omitTrailingSemicolon
= None
bool | None

Documentation

Executes the sql_string as a single SQL command.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Set the omit_trailing_semicolon to explicitly control the Omitting trailing semicolon behavior for the command.

Some parameters were renamed in version 2.0

The old parameters sqlString, sansTran and omitTrailingSemicolon are deprecated, please use new parameters sql_string, no_transaction and omit_trailing_semicolon instead.

The old parameters will be removed in future versions.

Examples

Execute Sql String DELETE FROM person_employee_table; DELETE FROM person_table
Execute Sql String DELETE FROM person_employee_table; DELETE FROM person_table no_transaction=True
Execute Sql String DELETE FROM person_employee_table; DELETE FROM person_table alias=my_alias
Execute Sql String CREATE PROCEDURE proc AS BEGIN DBMS_OUTPUT.PUT_LINE('Hello!'); END; omit_trailing_semicolon=False
@{parameters} Create List person_employee_table
Execute Sql String DELETE FROM %s parameters=${parameters}

Query

Arguments

select_statement str no_transaction
= False
bool return_dict
= False
bool alias
= None
str | None parameters
= None
Tuple | None 🏷 selectStatement
= None
str | None 🏷 sansTran
= None
bool | None 🏷 returnAsDict
= None
bool | None

Documentation

Runs a query with the select_statement and returns the result as list of rows. The type of row values depends on the database module - usually they are tuples or tuple-like objects.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Set return_dict to True to explicitly convert the return values into list of dictionaries.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Some parameters were renamed in version 2.0

The old parameters selectStatement, sansTran and returnAsDict are deprecated, please use new parameters select_statement, no_transaction and return_dict instead.

The old parameters will be removed in future versions.

Examples

${Results}= Query select LAST_NAME from person
${Results}= Query select LAST_NAME from person no_transaction=True
${Results}= Query select LAST_NAME from person return_dict=True
${Results}= Query select LAST_NAME from person alias=postgres
@{parameters} Create List person
${Results}= Query SELECT * FROM %s parameters=${parameters}

Row Count

Arguments

select_statement str no_transaction
= False
bool alias
= None
str | None parameters
= None
Tuple | None 🏷 selectStatement
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Runs a query with the select_statement and returns the number of rows in the result.

Set no_transaction to True to run command without explicit transaction commit or rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Some parameters were renamed in version 2.0

The old parameters selectStatement and sansTran are deprecated, please use new parameters select_statement and no_transaction instead.

The old parameters will be removed in future versions.

Examples

${Rows}= Row Count select LAST_NAME from person
${Rows}= Row Count select LAST_NAME from person no_transaction=True
${Rows}= Row Count select LAST_NAME from person alias=postgres
@{parameters} Create List person
${Rows}= Row Count SELECT * FROM %s parameters=${parameters}

Row Count Is 0

Arguments

selectStatement str sansTran
= False
bool msg
= None
str | None alias
= None
str | None parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

Check if any rows are returned from the submitted selectStatement. If there are, then this will throw an AssertionError.

Set optional input sansTran to True to run command without an explicit transaction commit or rollback.

The default error message can be overridden with the msg argument.

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

Row Count is 0 SELECT id FROM person WHERE first_name = 'Franz Allan'
Row Count is 0 SELECT id FROM person WHERE first_name = 'Franz Allan' msg=my error message
Row Count is 0 SELECT id FROM person WHERE first_name = 'John' alias=my_alias
Row Count is 0 SELECT id FROM person WHERE first_name = 'John' sansTran=True
@{parameters} Create List John
Row Count is 0 SELECT id FROM person WHERE first_name = %s parameters=${parameters}

Row Count Is Equal To X

Arguments

selectStatement str numRows str sansTran
= False
bool msg
= None
str | None alias
= None
str | None parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

Check if the number of rows returned from selectStatement is equal to the value submitted. If not, then this will throw an AssertionError.

Set optional input sansTran to True to run command without an explicit transaction commit or rollback.

The default error message can be overridden with the msg argument.

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

Row Count Is Equal To X SELECT id FROM person 1
Row Count Is Equal To X SELECT id FROM person 3 msg=my error message
Row Count Is Equal To X SELECT id FROM person WHERE first_name = 'John' 0 alias=my_alias
Row Count Is Equal To X SELECT id FROM person WHERE first_name = 'John' 0 sansTran=True
@{parameters} Create List John
Row Count Is Equal To X SELECT id FROM person WHERE first_name = %s 0 parameters=${parameters}

Row Count Is Greater Than X

Arguments

selectStatement str numRows str sansTran
= False
bool msg
= None
str | None alias
= None
str | None parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

Check if the number of rows returned from selectStatement is greater than the value submitted. If not, then this will throw an AssertionError.

Set optional input sansTran to True to run command without an explicit transaction commit or rollback.

The default error message can be overridden with the msg argument.

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

Row Count Is Greater Than X SELECT id FROM person WHERE first_name = 'John' 0
Row Count Is Greater Than X SELECT id FROM person WHERE first_name = 'John' 0 msg=my error message
Row Count Is Greater Than X SELECT id FROM person WHERE first_name = 'John' 0 alias=my_alias
Row Count Is Greater Than X SELECT id FROM person 1 sansTran=True
@{parameters} Create List John
Row Count Is Greater Than X SELECT id FROM person WHERE first_name = %s 0 parameters=${parameters}

Row Count Is Less Than X

Arguments

selectStatement str numRows str sansTran
= False
bool msg
= None
str | None alias
= None
str | None parameters
= None
Tuple | None

Documentation

DEPRECATED Use new Check Row Count keyword with assertion engine instead. The deprecated keyword will be removed in future versions.

Check if the number of rows returned from selectStatement is less than the value submitted. If not, then this will throw an AssertionError.

Set optional input sansTran to True to run command without an explicit transaction commit or rollback.

Using optional msg to override the default error message:

Use optional alias parameter to specify what connection should be used for the query if you have more than one connection open.

Use parameters for query variable substitution (variable substitution syntax may be different depending on the database client).

Examples:

Row Count Is Less Than X SELECT id FROM person WHERE first_name = 'John' 1
Row Count Is Less Than X SELECT id FROM person WHERE first_name = 'John' 2 msg=my error message
Row Count Is Less Than X SELECT id FROM person WHERE first_name = 'John' 3 alias=my_alias
Row Count Is Less Than X SELECT id FROM person WHERE first_name = 'John' 4 sansTran=True
@{parameters} Create List John
Row Count Is Less Than X SELECT id FROM person WHERE first_name = %s 5 parameters=${parameters}

Set Auto Commit

Arguments

auto_commit
= True
bool alias
= None
str | None 🏷 autoCommit
= None
bool | None

Documentation

Explicitly sets the autocommit behavior of the database connection to auto_commit. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Some parameters were renamed in version 2.0

The old parameter autoCommit is deprecated, please use new parameter auto_commit instead.

The old parameter will be removed in future versions.

Examples

          Set Auto Commit
          Set Auto Commit | False |
          Set Auto Commit | True  | alias=postgres |

Set Logging Query Results

Arguments

enabled
= None
bool | None log_head
= None
int | None

Documentation

Allows to enable/disable logging of query results and to adjust the log head value.

Examples:

Set Logging Query Results enabled=False
Set Logging Query Results enabled=True log_head=0
Set Logging Query Results log_head=10

Set Omit Trailing Semicolon

Arguments

omit_trailing_semicolon
= True
alias
= None
str | None

Documentation

Set the omit_trailing_semicolon to control the Omitting trailing semicolon behavior for the connection.

Use alias to specify what connection should be used if Handling multiple database connections.

Examples:

Set Omit Trailing Semicolon True
Set Omit Trailing Semicolon False alias=my_alias

Switch Database

Arguments

alias str

Documentation

Switch the default database connection to alias.

Examples:

Switch Database my_alias
Switch Database alias=my_alias

Table Must Exist

Arguments

table_name str no_transaction
= False
bool msg
= None
str | None alias
= None
str | None 🏷 tableName
= None
str | None 🏷 sansTran
= None
bool | None

Documentation

Check if the table with table_name exists in the database.

Use msg for custom error message.

Set no_transaction to True to run command without explicit transaction rollback in case of error. See Commit behavior for details.

Use alias to specify what connection should be used if Handling multiple database connections.

Some parameters were renamed in version 2.0

The old parameters tableName and sansTran are deprecated, please use new parameters table_name and no_transaction instead.

The old parameters will be removed in future versions.

Examples

Table Must Exist person
Table Must Exist person msg=my error message
Table Must Exist person alias=my_alias
Table Must Exist person no_transaction=True

Data types

Any (Standard)

Documentation

Any value is accepted. No conversion is done.

Converted Types

  • Any

Usages

AssertionOperator (Enum)

Documentation

Currently supported assertion operators are:

Operator Alternative Operators Description Validate Equivalent
== equal, equals, should be Checks if returned value is equal to expected value. value == expected
!= inequal, should not be Checks if returned value is not equal to expected value. value != expected
> greater than Checks if returned value is greater than expected value. value > expected
>= Checks if returned value is greater than or equal to expected value. value >= expected
< less than Checks if returned value is less than expected value. value < expected
<= Checks if returned value is less than or equal to expected value. value <= expected
*= contains Checks if returned value contains expected value as substring. expected in value
not contains Checks if returned value does not contain expected value as substring. expected in value
^= should start with, starts Checks if returned value starts with expected value. re.search(f"^{expected}", value)
$= should end with, ends Checks if returned value ends with expected value. re.search(f"{expected}$", value)
matches Checks if given RegEx matches minimum once in returned value. re.search(expected, value)
validate Checks if given Python expression evaluates to True.
evaluate then When using this operator, the keyword does return the evaluated Python expression.

Currently supported formatters for assertions are:

Formatter Description
normalize spaces Substitutes multiple spaces to single space from the value
strip Removes spaces from the beginning and end of the value
case insensitive Converts value to lower case before comparing
apply to expected Applies rules also for the expected value

Formatters are applied to the value before assertion is performed and keywords returns a value where rule is applied. Formatter is only applied to the value which keyword returns and not all rules are valid for all assertion operators. If apply to expected formatter is defined, then formatters are then formatter are also applied to expected value.

Allowed Values

  • equal
  • equals
  • ==
  • should be
  • inequal
  • !=
  • should not be
  • less than
  • <
  • greater than
  • >
  • <=
  • >=
  • contains
  • not contains
  • *=
  • starts
  • ^=
  • should start with
  • ends
  • should end with
  • $=
  • matches
  • validate
  • then
  • evaluate

Converted Types

  • string

Usages

boolean (Standard)

Documentation

Strings TRUE, YES, ON and 1 are converted to Boolean True, the empty string as well as strings FALSE, NO, OFF and 0 are converted to Boolean False, and the string NONE is converted to the Python None object. Other strings and other accepted values are passed as-is, allowing keywords to handle them specially if needed. All string comparisons are case-insensitive.

Examples: TRUE (converted to True), off (converted to False), example (used as-is)

Converted Types

  • string
  • integer
  • float
  • None

Usages

integer (Standard)

Documentation

Conversion is done using Python's int built-in function. Floating point numbers are accepted only if they can be represented as integers exactly. For example, 1.0 is accepted and 1.1 is not.

Starting from RF 4.1, it is possible to use hexadecimal, octal and binary numbers by prefixing values with 0x, 0o and 0b, respectively.

Starting from RF 4.1, spaces and underscores can be used as visual separators for digit grouping purposes.

Examples: 42, -1, 0b1010, 10 000 000, 0xBAD_C0FFEE

Converted Types

  • string
  • float

Usages

list (Standard)

Documentation

Strings must be Python list literals. They are converted to actual lists using the ast.literal_eval function. They can contain any values ast.literal_eval supports, including lists and other containers.

If the type has nested types like list[int], items are converted to those types automatically. This in new in Robot Framework 6.0.

Examples: ['one', 'two'], [('one', 1), ('two', 2)]

Converted Types

  • string
  • Sequence

Usages

tuple (Standard)

Documentation

Strings must be Python tuple literals. They are converted to actual tuples using the ast.literal_eval function. They can contain any values ast.literal_eval supports, including tuples and other containers.

If the type has nested types like tuple[str, int, int], items are converted to those types automatically. This in new in Robot Framework 6.0.

Examples: ('one', 'two'), (('one', 1), ('two', 2))

Converted Types

  • string
  • Sequence

Usages

DatabaseLibrary

image/svg+xml