Skip to content

LocalDB

LocalDB actions create and manage local SQLite databases on the robot host, including insert, select, update, delete, raw SQL execution, and CSV/Excel import.

Return values become robot state variables (for example $success, $rows, $result).

Index

Actions

localdb.create_database

Creates or opens a SQLite database.

If the file does not exist yet, it is created. If it already exists, the action simply opens it.

Parameters:

database_name (optional) - path or name of the SQLite database file (default="marvin.db").

Return:

success - True when the database is created or opened successfully; False on failure.

database - path or name of the created/opened database (only on success).

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.create_database("data.db")
prompt.alert(str($success))
prompt.alert(str($database))

localdb.insert

Inserts one or multiple records into a database table.

Accepts a single dictionary or a list of dictionaries. When pk is provided, that column is used as the primary key for the insert.

Parameters:

database_name - path or name of the SQLite database file.

table - name of the table where the data will be inserted.

data - data to insert. Accepts a single dictionary or a list of dictionaries.

pk (optional) - column name to use as primary key.

Return:

success - True when the insert succeeds; False on failure.

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.create_database("data.db")
localdb.execute(
    "data.db",
    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)"
)
localdb.insert(
    "data.db",
    "users",
    {"name": "Ana", "age": 25},
    pk="id"
)
prompt.alert(str($success))

localdb.select_table

Retrieves records from a database table.

You can filter with a WHERE condition and positional arguments for the placeholders.

Parameters:

database_name - path or name of the SQLite database file.

table - name of the table to query.

where (optional) - SQL WHERE condition used to filter records. Example: "age = ?".

where_args (optional) - values used to replace placeholders in the WHERE condition. Example: [25].

Return:

rows - list of dictionaries with the matching records (only on success).

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.select_table("data.db", "users", where="age = ?", where_args=[25])
prompt.alert(str($rows))

localdb.execute

Executes a raw SQL command on the database.

Supports commands such as DELETE, UPDATE, INSERT, CREATE TABLE, and ALTER TABLE.

Parameters:

database_name - path or name of the SQLite database file.

query_str - SQL command to execute.

Return:

success - True when the command runs successfully; False on failure.

rows_affected - number of rows affected by the execution (only on success).

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.execute(
    "data.db",
    "CREATE TABLE IF NOT EXISTS products (id INTEGER PRIMARY KEY, name TEXT, price REAL)"
)
prompt.alert(str($success))
prompt.alert(str($rows_affected))

localdb.query

Executes a SQL query that returns data.

Usually used with SELECT and PRAGMA.

Parameters:

database_name - path or name of the SQLite database file.

query_str - SQL query command to execute. Examples: SELECT * FROM users, PRAGMA table_info(users).

Return:

result - list with the records returned by the query (only on success).

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.query("data.db", "SELECT * FROM users")
prompt.alert(str($result))

localdb.update

Updates an existing record in a database table using the primary key value.

Parameters:

database_name - path or name of the SQLite database file.

table - name of the table containing the record.

pk_value - primary key value of the record to update.

data - dictionary containing the fields and new values.

Return:

success - True when the update succeeds; False on failure.

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.update("data.db", "users", 1, {"name": "Ana Silva", "age": 26})
prompt.alert(str($success))

localdb.delete

Deletes a record from a database table using the primary key value.

Parameters:

database_name - path or name of the SQLite database file.

table - name of the table containing the record.

pk_value - primary key value of the record to delete.

Return:

success - True when the delete succeeds; False on failure.

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.delete("data.db", "users", 1)
prompt.alert(str($success))

localdb.import_table

Imports data from CSV or Excel files (.csv, .xlsx) into a database table.

Parameters:

database_name - path or name of the SQLite database file.

file_path - path of the file to import (.csv or .xlsx).

table_name - destination table name.

delimiter (optional) - CSV separator (default=",").

sheet_name (optional) - Excel worksheet name. Defaults to the active worksheet.

has_pk (optional) - whether the table has a primary key (default=False).

pk_name (optional) - primary key column name (default="id").

first_col_id (optional) - whether the first column should become "id" (default=False).

Return:

success - True when the import succeeds; False on failure.

rows_inserted - number of rows inserted (only on success).

error - error message (only on failure).

Exceptions:

This action returns no Exception.

Usage example
script.mvn
localdb.import_table(
    "data.db",
    "C:/assets/users.csv",
    "users",
    delimiter=",",
    has_pk=True,
    pk_name="id"
)
prompt.alert(str($success))
prompt.alert(str($rows_inserted))