Call Stored Procedure

Call stored procedure with name and params.

Arguments

Argument Type Default value Description
namestrnullprocedure name
paramsList[str], NoneNoneparameters for the procedure as a list, defaults to None
sanstranbool, NoneFalseRun the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default)
param name:procedure name
param params:parameters for the procedure as a list, defaults to None
param sanstran:Run the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default)
returns:list of results

Examples

@{params}     Create List   FirstParam   SecondParam   ThirdParam
@{results}    Call Stored Procedure   mystpr  ${params}

Connect To Database

Connect to database using DB API 2.0 module.

Arguments

Argument Type Default value Description
module_namestr, NoneNonedatabase module to use
databasestr, NoneNonename of the database
usernamestr, NoneNoneof the user accessing the database
passwordstr, NoneNoneof the user accessing the database
hoststr, NoneNoneSQL server address
portint, NoneNoneSQL server port
charsetstr, NoneNonefor example, "utf-8", defaults to None
config_filestr, Nonedb.cfglocation of configuration file, defaults to "db.cfg"
autocommitbool, NoneFalseset autocommit value for connect (only with pymssql atm)

Note. The SSL support had been added for mysql module in rpaframework==17.7.0. The extra configuration parameters can be given via configuration file. Extra parameters are:

  • ssl_ca
  • ssl_cert
  • ssl_key
  • client_flags

Example configuration file:

[default]
host=hostname.mysql.database.azure.com
port=3306
username=username@hostname
database=databasename
client_flags=SSL,FOUND_ROWS
ssl_ca=DigiCertGlobalRootG2.crt.pem
param module_name:
 database module to use
param database:name of the database
param username:of the user accessing the database
param password:of the user accessing the database
param host:SQL server address
param port:SQL server port
param charset:for example, "utf-8", defaults to None
param config_file:
 location of configuration file, defaults to "db.cfg"
param autocommit:
 set autocommit value for connect (only with pymssql atm)

Examples

Connect To Database  pymysql  database  username  password  host  port
Connect To Database  ${CURDIR}${/}resources${/}dbconfig.cfg

${secrets}=    Get Secret    azuredb
Connect To Database
...    mysql.connector
...    password=${secrets}[password]
...    config_file=${CURDIR}${/}azure.cfg

Description

Get description of the SQL table

Arguments

Argument Type Default value Description
tablestrnullname of the SQL table
param table:name of the SQL table
returns:database descripton as a list

Examples

Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${db_description}      Description  mytable

Disconnect From Database

Close connection to SQL database

Examples

Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${result}              Query   Select firstname, lastname FROM table
Disconnect From Database

Execute Sql Script

Execute content of SQL script as SQL commands.

Arguments

Argument Type Default value Description
filenamestrnullfilepath to SQL script to execute
sanstranbool, NoneFalseRun the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default)
encodingstr, Noneutf-8character encoding of file (utf-8 by default)
param filename:filepath to SQL script to execute
param sanstran:Run the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default)
param encoding:character encoding of file (utf-8 by default)

Examples

Execute SQL Script   script.sql

Get Number Of Rows

Get number of rows in a table. Conditions can be given as arguments for WHERE clause.

Arguments

Argument Type Default value Description
tablestrnullname of the SQL table
conditionsstr, NoneNonerestrictions for selections, defaults to None
param table:name of the SQL table
param conditions:
 restrictions for selections, defaults to None
returns:number or rows

Examples

${count}   Get Number Of Rows  tablename
${count}   Get Number Of Rows  tablename  column1=5 and column2='x'

Get Rows

Get rows from table. Columns and conditions can be set to filter result.

Arguments

Argument Type Default value Description
tablenullname of the SQL table
columnsstr, NoneNonename of columns to return, defaults to None means that all columns are returned
conditionsstr, NoneNonelimiting result by WHERE clause, defaults to None
as_tablebool, NoneTrueif result should be instance of Table, defaults to True False means that return type would be list
param table:name of the SQL table
param columns:name of columns to return, defaults to None means that all columns are returned
param conditions:
 limiting result by WHERE clause, defaults to None
param as_table:if result should be instance of Table, defaults to True False means that return type would be list
returns:table or list based on param as_table arguement

Examples

@{res}   Get Rows  tablename  arvo
@{res}   Get Rows  tablename  arvo  columns=id,name
@{res}   Get Rows  tablename  columns=id  conditions=column1='newvalue'
@{res}   Get Rows  tablename  conditions=column2='updatedvalue'

Query

Execute a SQL query and optionally return the execution result.

Arguments

Argument Type Default value Description
statementstrnullSQL statement to execute.
assertionstr, NoneNoneAssert on query result, row_count or columns. Works only for SELECT statements. (defaults to None)
sanstranbool, NoneFalseRun the query without an implicit transaction commit or rollback if such additional action was detected and this is set to True. (turned off by default, meaning that commit is performed on successful queries and rollback on failing ones automatically)
as_tablebool, NoneTrueIf the result should be an instance of Table, otherwise a list will be returned. (defaults to True)
returningbool, NoneNoneSet this to True if you want to have rows explicitly returned (instead of the query result), False otherwise. (by default a heuristic detects if it should return or not)
param statement:
 SQL statement to execute.
param assertion:
 Assert on query result, row_count or columns. Works only for SELECT statements. (defaults to None)
param sanstran:Run the query without an implicit transaction commit or rollback if such additional action was detected and this is set to True. (turned off by default, meaning that commit is performed on successful queries and rollback on failing ones automatically)
param as_table:If the result should be an instance of Table, otherwise a list will be returned. (defaults to True)
param returning:
 Set this to True if you want to have rows explicitly returned (instead of the query result), False otherwise. (by default a heuristic detects if it should return or not)
returns:Fetched rows when returning is True or if the heuristic decides that the statement should return (raw rows or as Table if as_table is True), otherwise the object produced by the execution is returned.

Examples

Robot Framework

*** Settings ***
Library    RPA.Database

*** Tasks ***
Select Values From Table
    @{rows} =    Query   SELECT id,value FROM table
    FOR  ${row}  IN  @{rows}
        Log   ${row}
    END
    @{res} =    Query   Select * FROM table   row_count > ${EXPECTED}
    @{res} =    Query   Select * FROM table   'value' in columns
    @{res} =    Query   Select * FROM table   columns == ['id', 'value']

Python

from RPA.Database import Database

lib = Database()

def insert_and_return_names():
    lib.connect_to_database("sqlite3", "sqlite.db")
    lib.query("DROP TABLE IF EXISTS orders;")
    lib.query("CREATE TABLE orders(id INTEGER PRIMARY KEY, name TEXT);")
    rows = lib.query(
        'INSERT INTO orders(id, name) VALUES(1, "my-1st-order"),'
        '(2, "my-2nd-order") RETURNING name;'
    )
    print([row["name"] for row in rows])  # ['my-1st-order', 'my-2nd-order']

Set Auto Commit

Set database auto commit mode.

Arguments

Argument Type Default value Description
autocommitboolTrueboolean value for auto commit, defaults to True
param autocommit:
 boolean value for auto commit, defaults to True

Examples

Set Auto Commit             # auto commit is set on
Set Auto Commit   False     # auto commit is turned off