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
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_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_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_normal(self, value, expected):
assert SqlQuery.to_table_str(value) == expected
def test_abnormal(self, value, expected):
with pytest.raises(expected):
SqlQuery.sanitize(value)
def test_normal(self, value, expected):
assert list(SqlQuery.to_value_str_list(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, 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, select, table, where, extra, expected):
with pytest.raises(expected):
SqlQuery.make_select(select, table, where, extra)
def test_exception(self, value, expected):
with pytest.raises(expected):
SqlQuery.to_attr_str_list(value)
def test_normal(self, select, table, where, extra, expected):
assert SqlQuery.make_select(select, table, where, extra) == 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_abnormal(self, value, expected):
with pytest.raises(expected):
SqlQuery.sanitize(value)
def test_exception(self, key, value, operation, expected):
with pytest.raises(expected):
SqlQuery.make_where(key, value, operation)
def test_exception(self, value, expected):
with pytest.raises(expected):
assert SqlQuery.to_table_str(value)
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_normal(self, table, set_query, where, expected):
assert SqlQuery.make_update(table, set_query, where) == expected
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, 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)
def test_exception(self, key, value, expected):
with pytest.raises(expected):
SqlQuery.make_where_not_in(key, value)