Working with databases

Suppose the data you need for your automation is contained in a database (and you have access to it). In that case, you are in luck: the Robocorp stack allows you to connect to and interact with various database systems.

Most, if not all, of the applications that power the enterprise are database-based, and, in some cases, a possible way to automate them is to get directly to their database. In other cases, a database can be used as a queue system for your automation: in this scenario, some other system populates a list of items that need processing (invoices, orders, anything), and your automation code will take care of marking items as done by updating a field or removing the item altogether.

These are only some examples, and as we know, software automation is a vast and varied field, and your process might use a database differently and innovatively!

Which automation library should you use?

The easiest way to interact with a database on the Robocorp stack is to use the RPA.Database library (part of RPA Framework), which provides keywords and methods to accomplish all main database operations.

In addition to the main library, you will need to get the appropriate module for the database system you interact with and add it to your robot's conda.yaml file.

Which database types are supported?

The RPA.Database library is compatible with all modules that adhere to the Python Database API Specification v2.0 for relational databases. There are a lot of these implementations, so pretty much any database is covered.

As there are so many choices, it is worth listing the ones we recommend as they are the most common:

DatabaseConnector module
MySQL and MariaDBpymysql
Microsoft SQL Serverpymssql
Oracle Databaseoracledb
IBM DB2 and IBM Informixibm_db
(built-in Python, no dependencies needed)

You can also connect via ODBC to just about any database. The recommended connector module for that is pyodbc. This covers all of the above listed and a few more databases.

Add the module for your target to conda.yaml and you are ready to connect.

Connect to a database server by using the Connect to database keyword, passing the needed information, including the module that you are using:

*** Tasks ***
    Connect To Database
    ...    pymysql
    ...    database
    ...    username
    ...    password
    ...    host
    ...    port

Remember to store your database access credentials safely. We recommend you to use the Vault feature of Control Room!

How do you manipulate the data?

Once you are connected to the database you can query it using the Get Rows keyword, or the more generic Query keyword.

The data returned by both of these keywords (and the corresponding Python methods) will be in a format compatible with the [RPA.Table(/libraries/rpa-framework/rpa-tables) library, which allows you to further manipulate the data, and to easily use it in a FOR loop:

*** Settings ***
Library     RPA.Database

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

What about non-relational databases?

The RPA.Database does not support non-relational databases, but that does not mean that you cannot interact with them in your automation projects.

For example, if you want to work with a MongoDB database in Robot Framework, you can add the 3rd party Robotframework-MongoDB-Library.

Otherwise, you can use the PyMongo Python module directly, and create your own custom library, or use the module directly in your custom Python code.

May 5, 2022