Robot Framework Database Library

Some databases (like Oracle) may throw exceptions if a semicolon (;) is left at the end of a SQL string. The library can automatically remove it.

Default behavior:

• For oracledb and cx_Oracle: The trailing semicolon is removed automatically.
• For other modules: The semicolon is left as is.

Manual control:

• Use the Set Omit Trailing Semicolon keyword to change the global behavior.
• Use the omit_trailing_semicolon parameter in the Execute Sql String keyword for a single call.

You can manage multiple database connections by assigning an alias during the Connect To Database call. You can then target a specific connection by passing the alias argument to keywords, or by using Switch Database to change the active default connection.

*** Test Cases ***
Using Aliases
    ${names}=    Query    select LAST_NAME from person    alias=postgres
    Execute Sql String    drop table XYZ                  alias=mysql

Switching Default Alias
    Switch Database    postgres
    ${names}=    Query    select LAST_NAME from person
    Switch Database    mysql
    Execute Sql String    drop table XYZ

Some databases (like Oracle) throw exceptions if a SQL string ends with a semicolon (;). The library can automatically remove trailing semicolons to prevent these errors.

Default Behavior:

• For oracle_db and cx_Oracle: The trailing semicolon is removed automatically.
• For all other modules: The trailing semicolon is left as is.

Manual Configuration:

• Use the Set Omit Trailing Semicolon keyword to set a global preference.
• Use the omit_trailing_semicolon parameter within the Execute SQL String keyword for per-call control.

The library automatically performs a commit after running keywords like Query or Execute Sql String, and a rollback if an error occurs. This behavior is managed for you.

Key controls:

• Disable automatic commit/rollback: Use the no_transaction parameter in specific keywords.
• Explicit Autocommit: Use the Set Auto Commit keyword to set the behavior on the Python DB module level. Note that this does not affect the library's automatic commit/rollback logic.
• Oracle JDBC: If using jaydebeapi, you may need to use Set Auto Commit False to avoid errors.

By default, the library manages transactions for you: keywords like Query or Execute SQL String automatically perform a commit after a successful query or a rollback if an error occurs. This behavior is independent of the underlying Python DB module's autocommit setting.

To change this behavior, you have two options:

1. Disable automatic commit/rollback for a specific keyword: Use the no_transaction parameter within the keyword call.
2. Set the underlying module's autocommit mode: Use the Set Auto Commit keyword. Note that this does not affect the library's internal automatic commit/rollback logic described above.

Install the library using pip. Note that you must also install the specific Python database module required for your target database (e.g., oracledb, pymysql, psycopg2, etc.) separately.

pip install robotframework-databaselibrary

When using the oracledb module, you can specify the driver mode using the oracle_driver_mode parameter.

Supported Values:

• thin (default): Uses the Oracle Thin driver.
• thick: Uses the Oracle Thick driver (requires Oracle Instant Client installed on the system).
• thick,lib_dir=<PATH_TO_ORACLE_CLIENT>: Uses the Oracle Thick driver and specifies the path to the Oracle client libraries.

Example:

| Connect To Database | oracledb | ... | oracle_driver_mode=thick,lib_dir=/opt/oracle/instantclient |

Instead of long keyword arguments, you can use a configuration file containing key=value pairs. The default path is ./resources/db.cfg. You can specify a custom path using the config_file parameter in Connect To Database.

A config file must contain at least one section: [default] for a single connection, or a named section matching a connection alias.

Config file examples:

Default alias:

[default]
db_module=psycopg2
db_name=yourdbname
db_user=yourusername
db_password=yourpassword
db_host=yourhost
db_port=yourport

Specific alias:

[myoracle]
db_module=oracledb
db_name=yourdbname
db_user=yourusername
db_password=yourpassword
db_host=yourhost
db_port=yourport

Precedence: If a parameter is provided both as a keyword argument and in the config file, the keyword argument takes precedence.

[default]
db_password=mysecret

The library can print query results as a table in the Robot Framework log.

• By default, logging is enabled with a limit of 50 rows.
• You can disable logging or change the head limit using log_query_results and log_query_results_head during library import or via Set Logging Query Results during tests.
• Setting log_query_results_head=0 disables the limit and logs all rows.

*** Settings ***
# Disable logging
Library    DatabaseLibrary    log_query_results=False

# Set head limit to 10 rows
Library    DatabaseLibrary    log_query_results_head=10

# Disable limit (log all rows)
Library    DatabaseLibrary    log_query_results_head=0

When executing SQL statements, some databases (such as Oracle) may fail if a trailing semicolon (;) is present. The library can automatically decide whether to omit the semicolon based on the database module in use, or you can provide explicit instructions.

Use the omit_trailing_semicolon parameter (boolean) to force the removal of the trailing semicolon from your sql_statement.

By default, the library prints query results as a table in the Robot Framework log. A limit of 50 rows is applied to the log head; subsequent rows are truncated.

Library Import Options:

• log_query_results=False: Disables logging of query results.
• log_query_results_head=10: Sets the log head limit to 10 rows.
• log_query_results_head=0: Disables the limit and logs all rows.

Runtime Adjustment: You can adjust these settings during test execution using the Set Logging Query Results keyword.

*** Settings ***
Library    DatabaseLibrary    log_query_results=False
Library    DatabaseLibrary    log_query_results_head=10