def get_dataloader_error_table(self) -> Table:
"Return Dataloader error entries as `RPA.Table`"
return Table(self.dataloader_errors)
def test_columns_and_index_without_data():
data = []
columns = ["one", "two", "three"]
index = ["one", "two", "three"]
table = Table(data, columns=columns, index=index)
assert table.dimensions == (3, 3)
def get_dataloader_success_table(self) -> Table:
"Return Dataloader success entries as `RPA.Table`"
return Table(self.dataloader_success)
def table(request):
data, columns = DATA_FIXTURE[request.param]
return Table(data, columns)
def test_index_without_data():
data = []
index = ["one", "two", "three"]
table = Table(data, index=index)
assert table.dimensions == (3, 0)
def test_table_none_columns():
with pytest.raises(ValueError):
Table([{"one": 1, "two": 2, None: 3}, {"one": 1, None: 3, "four": 4}])
def database_query_result_as_table(self, query_string: str) -> Table:
"""Make database query and return result as `RPA.Table`.
``query_string`` SQL query string
"""
return Table(self.query(query_string, returnAsDict=True))
def test_table_compare(table):
assert table == Table(DATA_NAMEDTUPLE)
assert table != "not-comparable"
def test_create_table_1d_dict():
data = {"a": 1, "b": 2, "c": 3}
table = Table(data)
assert len(table) == 1
assert table.columns == ["a", "b", "c"]
def test_table_duplicate_columns():
with pytest.raises(ValueError):
Table(DATA_NAMEDTUPLE, columns=["two", "four", "two"])
def test_table_append_row():
data = {"a": [1], "b": [2], "c": [3]}
table = Table(data)
table.append_row()
def test_table_too_short_columns():
with pytest.raises(ValueError):
Table(DATA_LIST_LIST, columns=["two", "four"])
def test_table_invalid_data():
with pytest.raises(TypeError):
Table("cool")
def query(self, statement, assertion=None, sanstran=False, as_table=True):
"""Make a SQL query.
:param statement: SQL statement to execute
:param assertion: assert on query result, row_count or columns.
Works only for SELECT statements Defaults to None.
:param sanstran: run command without an explicit transaction commit or rollback,
defaults to False
:param as_table: if result should be instance of ``Table``, defaults to `True`
`False` means that return type would be `list`
Example:
.. code-block:: robotframework
@{res} Query Select firstname, lastname FROM table
FOR ${row} IN @{RES}
Log ${row}
END
@{res} Query Select * FROM table row_count > ${EXPECTED}
@{res} Query Select * FROM table 'arvo' in columns
@{res} Query Select * FROM table columns == ['id', 'arvo']
"""
rows = None
columns = None
result = None
cursor = None
try:
cursor = self._dbconnection.cursor()
self.logger.info("Executing : Query | %s ", statement)
result = self.__execute_sql(cursor, statement)
if statement.lower().startswith(
"select") or statement.lower().startswith("describe"):
rows = cursor.fetchall()
columns = [c[0] for c in cursor.description]
# pylint: disable=unused-variable
row_count = len(rows) # noqa: F841
if assertion:
available_locals = {
"row_count": row_count,
"columns": columns,
}
# pylint: disable=W0123
valid = eval(assertion, {"__builtins__": None},
available_locals)
if not valid:
raise AssertionError(
"Query assertion %s failed. Facts: %s" %
(assertion, available_locals))
if as_table:
return Table(rows, columns)
return rows
else:
if result is not None:
if not sanstran:
self._dbconnection.commit()
if not sanstran:
self._dbconnection.commit()
finally:
if cursor:
if not sanstran:
self._dbconnection.rollback()
return result
def test_table_range_columns():
table = Table(DATA_LIST)
assert table.columns == [0, 1, 2, 3]
def test_create_table_1d_list():
data = [1, 2, 3]
table = Table(data)
assert len(table) == 3
def test_table_named_columns():
table = Table(DATA_NAMEDTUPLE, columns=["two", "four"])
assert table.columns == ["two", "four"]
assert table.index == [0, 1, 2, 3, 4, 5]
assert table[0] == [2, None]
assert table[4] == [2, 4]
def test_columns_without_data():
data = []
columns = ["one", "two", "three"]
table = Table(data, columns=columns)
assert table.dimensions == (0, 3)
def test_set_cell_empty_table():
table = Table()
table.set_cell(0, 0, "value")
assert table.dimensions == (1, 1)
assert table[0, 0] == "value"
def query(
self,
statement: str,
assertion: Optional[str] = None,
sanstran: Optional[bool] = False,
as_table: Optional[bool] = True,
returning: Optional[bool] = None,
) -> Union[List, Dict, Table, Any]:
"""Execute a SQL query and optionally return the execution result.
:param statement: SQL statement to execute.
:param assertion: Assert on query result, row_count or columns.
Works only for `SELECT` statements. (defaults to `None`)
:param sanstran: Run the query without an implicit transaction commit or
rollback if such additional action was detected and this is set to `True`.
(turned off by default, meaning that *commit* is performed on successful
queries and *rollback* on failing ones automatically)
:param as_table: If the result should be an instance of `Table`, otherwise a
`list` will be returned. (defaults to `True`)
:param returning: Set this to `True` if you want to have rows explicitly
returned (instead of the query result), `False` otherwise. (by default a
heuristic detects if it should return or not)
:returns: Fetched rows when `returning` is `True` or if the heuristic decides
that the statement should return (raw rows or as `Table` if `as_table` is
`True`), otherwise the object produced by the execution is returned.
**Examples**
**Robot Framework**
.. code-block:: robotframework
*** Settings ***
Library RPA.Database
*** Tasks ***
Select Values From Table
@{rows} = Query SELECT id,value FROM table
FOR ${row} IN @{rows}
Log ${row}
END
@{res} = Query Select * FROM table row_count > ${EXPECTED}
@{res} = Query Select * FROM table 'value' in columns
@{res} = Query Select * FROM table columns == ['id', 'value']
**Python**
.. code-block:: python
from RPA.Database import Database
lib = Database()
def insert_and_return_names():
lib.connect_to_database("sqlite3", "sqlite.db")
lib.query("DROP TABLE IF EXISTS orders;")
lib.query("CREATE TABLE orders(id INTEGER PRIMARY KEY, name TEXT);")
rows = lib.query(
'INSERT INTO orders(id, name) VALUES(1, "my-1st-order"),'
'(2, "my-2nd-order") RETURNING name;'
)
print([row["name"] for row in rows]) # ['my-1st-order', 'my-2nd-order']
"""
cursor = None
try:
self.logger.info("Executing query: %s", statement)
cursor = self._dbconnection.cursor()
result = self.__execute_sql(cursor, statement)
should_return = (returning is True) or (
returning is None and self._is_returnable_statement(statement)
)
if should_return:
rows = [tuple(row) for row in cursor.fetchall()]
columns = [col[0] for col in (cursor.description or [])]
self._result_assertion(rows, columns, assertion)
if as_table:
result = Table(rows, columns)
else:
result = rows
except Exception as exc:
# Implicitly rollback when error occurs.
self.logger.error(exc)
if cursor and not sanstran:
self._dbconnection.rollback()
raise
else:
if not sanstran:
self._dbconnection.commit()
return result
