Working with databases

If the data you need for your automation is contained in a database (and you have access to it), you are in luck: the Robocorp stack allows you to connect to and interact with a variety of 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 in a different and innovative way!

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 are interacting with and add it to your robot's conda.yaml file.

Which database types are supported?

The library is compatible with all modules that adhere to the Python Database API Specification v2.0 for relational databases.

The list includes (but is not limited to):

Once you have added the module needed, you can 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
  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 Robocorp Cloud!

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  127.0.0.1
    @{orders}            Query    Select * FROM incoming_orders
    FOR   ${order}  IN  @{orders}
        Handle Order  ${order}
    END

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.

Learn more about the libraries mentioned on this page: