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