Python API

Database

class RPA.Database.Configuration

Bases: object

Class to handle configuration from config files and class init

all_but_empty()
get(param, default=None)
get_connection_parameters_as_string(conf=None)
parse_arguments(module_name, database, username, password, host, port, charset, config_file: str)
set_default_port(port)
set_val(param, value)
class RPA.Database.Database

Bases: object

Database is a library for handling different database operations.

All database operations are supported. Keywords Query and Get Rows return values by default in RPA.Table format.

Library is compatible with any Database API Specification 2.0 module.

References:

Examples

Robot Framework

*** Settings ***
Library         RPA.Database

*** Tasks ***
Get Orders From Database
    Connect To Database  pymysql  tester  user  password  127.0.0.1
    @{orders}            Query    Select * FROM incoming_orders
    FOR   ${order}  IN  @{orders}
        Handle Order  ${order}
    END

Python

from RPA.Database import Database
from RPA.Robocorp.Vault import FileSecrets

filesecrets = FileSecrets("secrets.json")
secrets = filesecrets.get_secret("databasesecrets")

db = Database()
db.connect_to_database('pymysql',
                    secrets["DATABASE"],
                    secrets["USERNAME"],
                    secrets["PASSWORD"],
                    '127.0.0.1'
                    )
orders = db.query("SELECT * FROM incoming_orders")
for order in orders:
    print(order)
ROBOT_LIBRARY_DOC_FORMAT = 'REST'
ROBOT_LIBRARY_SCOPE = 'GLOBAL'
call_stored_procedure(name: str, params: Optional[List[str]] = None, sanstran: Optional[bool] = False) → List[str]

Call stored procedure with name and params.

Parameters
  • name – procedure name

  • params – parameters for the procedure as a list, defaults to None

  • 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

Example:

@{params}     Create List   FirstParam   SecondParam   ThirdParam
@{results}    Call Stored Procedure   mystpr  ${params}
connect_to_database(module_name: Optional[str] = None, database: Optional[str] = None, username: Optional[str] = None, password: Optional[str] = None, host: Optional[str] = None, port: Optional[int] = None, charset: Optional[str] = None, config_file: Optional[str] = 'db.cfg', autocommit: Optional[bool] = False) → None

Connect to database using DB API 2.0 module.

Parameters
  • module_name – database module to use

  • database – name of the database

  • username – of the user accessing the database

  • password – of the user accessing the database

  • host – SQL server address

  • port – SQL server port

  • charset – for example, “utf-8”, defaults to None

  • config_file – location of configuration file, defaults to “db.cfg”

  • autocommit – set autocommit value for connect (only with pymssql atm)

Example:

Connect To Database  pymysql  database  username  password  host  port
Connect To Database  ${CURDIR}${/}resources${/}dbconfig.cfg
description(table: str) → list

Get description of the SQL table

Parameters

table – name of the SQL table

Returns

database descripton as a list

Example:

Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${db_description}      Description  mytable
disconnect_from_database() → None

Close connection to SQL database

Example:

Connect To Database    pymysql  mydb  user  pass  127.0.0.1
${result}              Query   Select firstname, lastname FROM table
Disconnect From Database
execute_sql_script(filename: str, sanstran: Optional[bool] = False, encoding: Optional[str] = 'utf-8') → None

Execute content of SQL script as SQL commands.

Parameters
  • filename – filepath to SQL script to execute

  • sanstran – Run the query without an implicit transaction commit or rollback if such additional action was detected. (turned off by default)

  • encoding – character encoding of file (utf-8 by default)

Example:

Execute SQL Script   script.sql
get_number_of_rows(table: str, conditions: Optional[str] = None) → int

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

Parameters
  • table – name of the SQL table

  • conditions – restrictions for selections, defaults to None

Returns

number or rows

Example:

${count}   Get Number Of Rows  tablename
${count}   Get Number Of Rows  tablename  column1=5 and column2='x'
get_rows(table, columns: Optional[str] = None, conditions: Optional[str] = None, as_table: Optional[bool] = True) → Union[List, Dict, RPA.Tables.Table, Any]

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

Parameters
  • table – name of the SQL table

  • columns – name of columns to return, defaults to None means that all columns are returned

  • conditions – limiting result by WHERE clause, defaults to None

  • 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

Example:

@{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(statement: str, assertion: Optional[str] = None, sanstran: Optional[bool] = False, as_table: Optional[bool] = True, returning: Optional[bool] = None) → Union[List, Dict, RPA.Tables.Table, Any]

Execute a SQL query and optionally return the execution result.

Parameters
  • statement – SQL statement to execute.

  • assertion – Assert on query result, row_count or columns. Works only for SELECT statements. (defaults to None)

  • 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)

  • as_table – If the result should be an instance of Table, otherwise a list will be returned. (defaults to True)

  • 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(autocommit: bool = True) → None

Set database auto commit mode.

Parameters

autocommit – boolean value for auto commit, defaults to True

Example:

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