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:
| Database | Connector module |
|---|---|
| MySQL and MariaDB | pymysql |
| PostgreSQL | psycopg2 and psycopg3 |
| Microsoft SQL Server | pymssql |
| Oracle Database | oracledb |
| IBM DB2 and IBM Informix | ibm_db |
| Teradata | teradata |
| SQLite | sqlite3 (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:
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 manipulate the data further and to easily use it in a FOR loop:
What about non-relational databases?
The RPA.Database does not support non-relational databases, but that does not mean you cannot interact with them in your automation projects.
The official MongoDB python library is available from conda-forge and PyPI.
There are also some Robot Framework wrapper libraries out there, but at the moment (01/2023), those seem to be missing contributors, so they are a bit hard for us to recommend. If you are interested, the Robot Framework community Slack is also a great place to ask and possibly even contribute to the open-source efforts around this.
In any case, you can use the PyMongo Python module directly and create your custom library, or use the module directly in your custom Python code.