RPA.Database
Call stored procedure with name and params.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| name | str | null | procedure name |
| params | List[str], None | None | parameters for the procedure as a list, defaults to None |
| sanstran | bool, None | False | Run the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default) |
| as_table | bool, None | True | If the result should be an instance of Table, otherwise a list will be returned. (defaults to True) |
| multiple | bool, None | False | Return results for one result set (default False) or multiple results from all result sets (set this parameter to True) |
| 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) |
| param as_table: | If the result should be an instance of Table, otherwise a list will be returned. (defaults to True) |
| param multiple: | Return results for one result set (default False) or multiple results from all result sets (set this parameter to True) |
| returns: | list of results |
Examples
@{params} Create List FirstParam SecondParam ThirdParam
@{results} Call Stored Procedure mystpr ${params}
Connect to database using DB API 2.0 module.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| module_name | str, None | None | database module to use |
| database | str, None | None | name of the database |
| username | str, None | None | of the user accessing the database |
| password | str, None | None | of the user accessing the database |
| host | str, None | None | SQL server address |
| port | int, None | None | SQL server port |
| charset | str, None | None | for example, "utf-8", defaults to None |
| config_file | str, None | db.cfg | location of configuration file, defaults to "db.cfg" |
| autocommit | bool, None | False | set autocommit value for connect |
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 | |
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
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 content of SQL script as SQL commands.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| filename | str | null | filepath to SQL script to execute |
| sanstran | bool, None | False | Run the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default) |
| encoding | str, None | utf-8 | character 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 in a table. Conditions can be given as arguments for WHERE clause.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| table | str | null | name of the SQL table |
| conditions | str, None | None | restrictions 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 from table. Columns and conditions can be set to filter result.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| table | null | name of the SQL table | |
| columns | str, None | None | name of columns to return, defaults to None means that all columns are returned |
| conditions | str, None | None | limiting result by WHERE clause, defaults to None |
| as_table | bool, None | True | if 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'
Execute a SQL query and optionally return the execution result.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| statement | str | null | SQL statement to execute. |
| assertion | str, None | None | Assert on query result, row_count or columns. Works only for SELECT statements. (defaults to None) |
| sanstran | bool, None | False | 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) |
| as_table | bool, None | True | If the result should be an instance of Table, otherwise a list will be returned. (defaults to True) |
| returning | bool, None | None | 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) |
| data | Dict, Tuple, None | None | The data to use if the SQL statement is parameterized |
Security Warning: In order to safely include untrusted data in SQL queries it is advisable to use parameterized queries. For more information about formatting for specific databases, please see https://bobby-tables.com/python
| 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) | |
| param data: | The data to use if the SQL statement is parameterized |
| 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']
@{res} = Query Select * FROM table WHERE value = ? data=("${d}", )
# Calling Stored Procedure with Query keyword requires that parameter
# 'returning' is set to 'True'
@{res} = Query Exec stored_procedure returning=True
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);")
data1 = "my-1st-order"
data2 = "my-2nd-order"
lib.query(
'INSERT INTO orders(id, name) VALUES(1, ?), (2, ?);',
data=(data1, data2)
)
rows = lib.query(
'SELECT * FROM orders'
)
print([row["name"] for row in rows]) # ['my-1st-order', 'my-2nd-order']
Set database auto commit mode.
Arguments
| Argument | Type | Default value | Description |
|---|---|---|---|
| autocommit | bool | True | boolean 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