RPA.Database

module RPA.Database

class RPA.Database.Database

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.

Workaround for inserting large JSON data for Call Stored Procedure

Workaround is to use instead Query keyword. At the moment there is no known fix for the Call Stored Procedure keyword as it fails if JSON string is more than 8000 characters long.

Robot Framework

References:

Examples

Robot Framework

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)

variable ROBOT_LIBRARY_DOC_FORMAT

ROBOT_LIBRARY_DOC_FORMAT = 'REST'

variable ROBOT_LIBRARY_SCOPE

ROBOT_LIBRARY_SCOPE = 'GLOBAL'

method call_stored_procedure

call_stored_procedure(name: str, params: Optional[List[str]] = None, sanstran: Optional[bool] = False, as_table: Optional[bool] = True, multiple: Optional[bool] = False)

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)
  • as_table – If the result should be an instance of Table, otherwise a list will be returned. (defaults to True)
  • 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

method connect_to_database

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)

Connect to database using DB API 2.0 module.

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

method description

description(table: str)

Get description of the SQL table

  • Parameters: table – name of the SQL table
  • Returns: database descripton as a list

method disconnect_from_database

disconnect_from_database()

Close connection to SQL database

method execute_sql_script

execute_sql_script(filename: str, sanstran: Optional[bool] = False, encoding: Optional[str] = 'utf-8')

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)

method get_number_of_rows

get_number_of_rows(table: str, conditions: Optional[str] = None)

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

method get_rows

get_rows(table, columns: Optional[str] = None, conditions: Optional[str] = None, as_table: Optional[bool] = True)

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

method query

query(statement: str, assertion: Optional[str] = None, sanstran: Optional[bool] = False, as_table: Optional[bool] = True, returning: Optional[bool] = None, data: Optional[Union[Dict, Tuple]] = None)

Execute a SQL query and optionally return the execution result.

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

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

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']

method set_auto_commit

set_auto_commit(autocommit: bool = True)

Set database auto commit mode.

  • Parameters: autocommit – boolean value for auto commit, defaults to True