def __strip_empty_col(self): from simplesqlite import connect_sqlite_db_mem from simplesqlite.sqlquery import SqlQuery con = connect_sqlite_db_mem() tmp_table_name = "tmp" header_list = [ "a{:d}".format(i) for i in range(len(self.__all_values[0])) ] con.create_table_from_data_matrix(table_name=tmp_table_name, attr_name_list=header_list, data_matrix=self.__all_values) for col_idx, header in enumerate(header_list): result = con.select(select=SqlQuery.to_attr_str(header), table_name=tmp_table_name) if any([ typepy.is_not_null_string(record[0]) for record in result.fetchall() ]): break strip_header_list = header_list[col_idx:] if typepy.is_empty_sequence(strip_header_list): raise ValueError() result = con.select(select=",".join( SqlQuery.to_attr_str_list(strip_header_list)), table_name=tmp_table_name) self.__all_values = result.fetchall()
def query(self, tableName, whereDict, operation="=", extra=None): print len(whereDict.keys()) print "---------------" if len(whereDict.keys()) != 1: raise NotSupportParamError() return self.con.select(select="*", table_name=tableName,\ where=SqlQuery.make_where(key=whereDict.keys()[0], value=whereDict[whereDict.keys()[0]],operation=operation),extra=extra).fetchall()
def test_normal(self, con): table_name = TEST_TABLE_NAME where = SqlQuery.make_where("attr_b", 2) con.update( table_name=table_name, set_query="attr_a = 100", where=where) assert con.get_value( select="attr_a", table_name=table_name, where=where) == 100
class Test_SqlQuery_make_update: @pytest.mark.parametrize( ["table", "set_query", "where", "expected"], [ [ "A", "B=1", None, "UPDATE A SET B=1" ], [ "A", "B=1", SqlQuery.make_where("C", 1, ">"), "UPDATE A SET B=1 WHERE C > 1" ], ]) def test_normal(self, table, set_query, where, expected): assert SqlQuery.make_update(table, set_query, where) == expected @pytest.mark.parametrize( ["table", "set_query", "where", "expected"], [ [None, "B=1", None, ValueError], ["", "B=1", None, ValueError], ["A", None, None, ValueError], ["A", "", None, ValueError], ]) def test_exception(self, table, set_query, where, expected): with pytest.raises(expected): SqlQuery.make_update(table, set_query, where)
def update(self, tableName, set_queryDict, whereDict): print len(whereDict.keys()) print "---------------" if len(whereDict.keys()) != 1: raise NotSupportParamError() set_queryStr = "" for setKey, setValue in set_queryDict.items(): set_queryStr.join(setKey).join("=").join(setValue) return self.con.update(tableName,set_query ="", \ where=SqlQuery.make_where(key=whereDict.keys()[0], \ value=whereDict[whereDict.keys()[0]])).fetchall()
class Test_SqlQuery_make_select: @pytest.mark.parametrize( ["select", "table", "where", "extra", "expected"], [ [ "A", "B", None, None, "SELECT A FROM B" ], [ "A", "B B", None, None, "SELECT A FROM 'B B'" ], [ "A", "B-B", SqlQuery.make_where("C", 1), None, "SELECT A FROM [B-B] WHERE C = 1" ], [ "A", "B-B", SqlQuery.make_where("C", 1, ">"), "ORDER BY D", "SELECT A FROM [B-B] WHERE C > 1 ORDER BY D" ], ]) def test_normal(self, select, table, where, extra, expected): assert SqlQuery.make_select(select, table, where, extra) == expected @pytest.mark.parametrize( ["select", "table", "where", "extra", "expected"], [ ["A", None, None, None, InvalidTableNameError], ["", "B", None, None, ValueError], [None, None, None, None, InvalidTableNameError], [None, "B", None, None, ValueError], [nan, None, None, None, InvalidTableNameError], [nan, nan, None, None, TypeError], [nan, nan, nan, None, TypeError], [nan, nan, nan, nan, TypeError], ]) def test_exception(self, select, table, where, extra, expected): with pytest.raises(expected): SqlQuery.make_select(select, table, where, extra)
def _get_index_schema(self, table_name): self.__update_sqlite_master_db() try: result = self._con_sql_master.select( "sql", table_name=self._SQLITE_MASTER_TABLE_NAME, where=" AND ".join([ SqlQuery.make_where("tbl_name", table_name), SqlQuery.make_where("type", "index"), ])) except simplesqlite.TableNotFoundError as e: raise DataNotFoundError(e) try: return [ record[0] for record in result.fetchall() if typepy.is_not_empty_sequence(record[0]) ] except TypeError: raise DataNotFoundError( "index not found in '{}'".format(table_name))
def _get_attr_schema(self, table_name, schema_type): self._validate_table_existence(table_name) if table_name == "sqlite_sequence": return [] self.__update_sqlite_master_db() try: result = self._con_sql_master.select( "sql", table_name=self._SQLITE_MASTER_TABLE_NAME, where=" AND ".join([ SqlQuery.make_where("tbl_name", table_name), SqlQuery.make_where("type", schema_type), ])) except simplesqlite.TableNotFoundError: raise DataNotFoundError("table not found: '{}'".format( self._SQLITE_MASTER_TABLE_NAME)) error_message_format = "data not found in '{}' table" try: table_schema = result.fetchone()[0] except TypeError: raise DataNotFoundError( error_message_format.format(self._SQLITE_MASTER_TABLE_NAME)) match = self._RE_ATTR_DESCRIPTION.search(table_schema) if match is None: raise DataNotFoundError(error_message_format.format(table_schema)) return [ attr.strip() for attr in match.group().strip("()").split(",") if self._RE_FOREIGN_KEY.search(attr) is None ]
def to_table_data(self): from simplesqlite import SimpleSQLite from simplesqlite.sqlquery import SqlQuery con = SimpleSQLite(self._source_data, "r") for table in con.get_table_name_list(): self.__table_name = table attr_name_list = con.get_attr_name_list(table) data_matrix = con.select( select=",".join(SqlQuery.to_attr_str_list(attr_name_list)), table_name=table) yield TableData( table, attr_name_list, data_matrix)
def test_normal( self, tmpdir, attr_name_list, data_matrix, index_attr_list, expected_attr): p = tmpdir.join("tmp.db") con = SimpleSQLite(str(p), "w") table_name = TEST_TABLE_NAME con.create_table_with_data( table_name, attr_name_list, data_matrix, index_attr_list) con.commit() # check data --- result = con.select( select=",".join(SqlQuery.to_attr_str_list(attr_name_list)), table_name=table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 assert con.get_attr_type(table_name) == expected_attr
def create_tc_obj(options): from .parser.shaping_rule import TcShapingRuleParser from simplesqlite.sqlquery import SqlQuery if options.filter_id: ip_version = 6 if options.is_ipv6 else 4 shaping_rule_parser = TcShapingRuleParser( device=options.device, ip_version=ip_version, tc_command_output=options.tc_command_output, logger=logger) shaping_rule_parser.parse() result = shaping_rule_parser.con.select_as_dict( table_name=Tc.Subcommand.FILTER, column_list=[ Tc.Param.SRC_NETWORK, Tc.Param.DST_NETWORK, Tc.Param.SRC_PORT, Tc.Param.DST_PORT ], where=SqlQuery.make_where(Tc.Param.FILTER_ID, options.filter_id)) if not result: logger.error( "shaping rule not found associated with the id ({}).".format( options.filter_id)) sys.exit(1) filter_param = result[0] dst_network = filter_param.get(Tc.Param.DST_NETWORK) src_network = filter_param.get(Tc.Param.SRC_NETWORK) dst_port = filter_param.get(Tc.Param.DST_PORT) src_port = filter_param.get(Tc.Param.SRC_PORT) else: dst_network = options.dst_network src_network = options.src_network dst_port = options.dst_port src_port = options.src_port return TrafficControl(options.device, direction=options.direction, dst_network=dst_network, src_network=src_network, dst_port=dst_port, src_port=src_port, is_ipv6=options.is_ipv6)
def __get_filter_where_condition_list(self): if self.__tc.direction == TrafficDirection.OUTGOING: device = self._parser.device elif self.__tc.direction == TrafficDirection.INCOMING: device = self._parser.ifb_device return [ SqlQuery.make_where(Tc.Param.DEVICE, device), SqlQuery.make_where(Tc.Param.PROTOCOL, self.__tc.protocol), SqlQuery.make_where(Tc.Param.DST_NETWORK, self.__tc.dst_network), SqlQuery.make_where(Tc.Param.SRC_NETWORK, self.__tc.src_network), SqlQuery.make_where(Tc.Param.DST_PORT, self.__tc.dst_port), SqlQuery.make_where(Tc.Param.SRC_PORT, self.__tc.src_port), ]
def test_normal(self, tmpdir, attr_name_list, data_matrix, index_attr_list, expected_attr): p = tmpdir.join("tmp.db") con = SimpleSQLite(str(p), "w") table_name = TEST_TABLE_NAME con.create_table_from_data_matrix(table_name, attr_name_list, data_matrix, index_attr_list) con.commit() # check data --- result = con.select(select=",".join( SqlQuery.to_attr_str_list(attr_name_list)), table_name=table_name) result_matrix = result.fetchall() assert len(result_matrix) == 3 print("expected: {}".format(expected_attr)) print("actual: {}".format(con.get_attr_type(table_name))) assert con.get_attr_type(table_name) == expected_attr
def test_exception(self, value, expected): with pytest.raises(expected): SqlQuery.to_attr_str_list(value)
def test_normal(self, value, expected): assert list(SqlQuery.to_value_str_list(value)) == expected
def test_normal(self, value, expected): assert SqlQuery.sanitize(value) == expected
def test_exception(self, table, insert_tuple, is_isert_many, expected): with pytest.raises(expected): SqlQuery.make_insert(table, insert_tuple)
def test_normal(self, key, value, operation, expected): assert SqlQuery.make_where(key, value, operation) == expected
def test_exception(self, select, table, where, extra, expected): with pytest.raises(expected): SqlQuery.make_select(select, table, where, extra)
def test_normal(self, value, operation, expected): assert SqlQuery.to_attr_str(value, operation) == expected
def test_normal(self, value, expected): assert SqlQuery.to_table_str(value) == expected
def test_exception(self, value, expected): with pytest.raises(expected): assert SqlQuery.to_table_str(value)
def test_abnormal(self, value, expected): with pytest.raises(expected): SqlQuery.sanitize(value)
def test_normal(self, select, table, where, extra, expected): assert SqlQuery.make_select(select, table, where, extra) == expected
def test_normal(self, table, insert_tuple, is_isert_many, expected): assert SqlQuery.make_insert( table, insert_tuple, is_isert_many) == expected
def test_normal(self, table, insert_tuple, expected): assert SqlQuery.make_insert(table, insert_tuple) == expected
def test_exception(self, key, value, operation, expected): with pytest.raises(expected): SqlQuery.make_where(key, value, operation)
def test_normal(self, value, operation, expected): assert list(SqlQuery.to_attr_str_list(value, operation)) == expected
def test_normal(self, table, set_query, where, expected): assert SqlQuery.make_update(table, set_query, where) == expected
from simplesqlite import SimpleSQLite from simplesqlite.sqlquery import SqlQuery table_name = "sample_table" con = SimpleSQLite("sample.sqlite", "w") data_matrix = [ [1, "aaa"], [2, "bbb"], ] con.create_table_from_data_matrix( table_name, attr_name_list=["key", "value"], data_matrix=data_matrix) print("---- before update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record) print() con.update( table_name, set_query="value = 'ccc'", where=SqlQuery.make_where(key="key", value=1)) print("---- after update ----") for record in con.select(select="*", table_name=table_name).fetchall(): print(record)
def test_exception(self, table, set_query, where, expected): with pytest.raises(expected): SqlQuery.make_update(table, set_query, where)
def test_normal(self, key, value, expected): assert SqlQuery.make_where_not_in(key, value) == expected
def test_exception(self, key, value, expected): with pytest.raises(expected): SqlQuery.make_where_not_in(key, value)