def select_veth(self, container_name):
for container_record in IfIndex.select(
where=Where("host", container_name)):
yield from IfIndex.select(where=And([
Where("host", self.__host_name),
Where("ifindex", container_record.peer_ifindex),
]))
def test_normal(self, con):
table_name = TEST_TABLE_NAME
where = Where("attr_b", 2)
con.update(table_name=table_name,
set_query="attr_a = 100",
where=where)
assert con.fetch_value(select="attr_a",
table_name=table_name,
where=where) == 100
assert (con.fetch_value(select="attr_a",
table_name=table_name,
where=Where("attr_b", 4)) == 3)
def test_orm():
con = connect_memdb()
Hoge.attach(con, is_hidden=True)
Hoge.create()
assert Hoge.fetch_num_records() == 0
hoge_inputs = [Hoge(hoge_id=10, name="a"), Hoge(hoge_id=20, name="b")]
for hoge_input in hoge_inputs:
Hoge.insert(hoge_input)
Foo.attach(con)
Foo.create()
foo_inputs = [Foo(foo_id=11, name="aq", value=0.1), Foo(foo_id=22, name="bb", value=1.11)]
for foo_input in foo_inputs:
Foo.insert(foo_input)
assert Hoge.fetch_num_records() == 2
for record, hoge_input in zip(Hoge.select(), hoge_inputs):
assert record == hoge_input
for record, foo_input in zip(Foo.select(), foo_inputs):
assert record == foo_input
result = Hoge.select(where=Where("hoge_id", 999))
assert len(list(result)) == 0
def _fetch_source_id(self, source_info):
where_list = [
Where("base_name", source_info.base_name),
Where("format_name", source_info.format_name),
]
if source_info.dir_name:
where_list.append(Where("dir_name", source_info.dir_name))
if source_info.size is not None:
where_list.append(Where("size", source_info.size))
if source_info.mtime is not None:
where_list.append(Where("mtime", source_info.mtime))
return self._con.fetch_value(
select=Attr("source_id"), table_name=SourceInfo.get_table_name(), where=And(where_list)
)
def CheckDB_by_Symbol_in_timeframe(self,
symbol=None,
condition=None,
timedelta=None):
"""
Args:
condiction:'opening','grid'
symbol:'XBTUSD','ETHUSD'
timedelta:3600 minutes
Return:
if founded conditicon db,return True,else return False.
Example:
https://github.com/thombashi/SimpleSQLite
pip install SimpleSQLite
modify sqlquery.py in sites-packages/
con = SimpleSQLite('bitmex','a')
table_name = 'bitmex_bar_1h'
where_list = And([Where("symbol", "XBTUSD"), Where("id", 1, "<=")])
result = con.select(select="symbol,close",table_name=table_name,where=where_list)
for s in result.fetchall():
print(s)
con.fetch_num_records(table_name,where_list)
con.update(table_name, set_query="symbol = 'XBTUSD',close=3300", where=where_list)
"""
d = datetime.datetime.now().replace(
second=0, microsecond=0) + datetime.timedelta(minutes=-timedelta)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, ">")
])
table_name = 'bitmex_orderhistory'
ordercount_in_one_day = self.con.fetch_num_records(
table_name, where_list)
where_list = And([
Where("symbol", symbol, '='),
Where("ordertype", condition, "="),
Where("date", d, "<=")
])
orderids_before_one_day = self.con.select(select="orderid",
table_name=table_name,
where=where_list)
for orderid in orderids_before_one_day.fetchall():
while True:
try:
self.ex.cancel_order(orderid[0])
self.con.delete(table_name,
where=Where("orderid", orderid[0], "="))
break
except Exception as e:
print(e)
time.sleep(1)
return ordercount_in_one_day
def __create_tc_obj(self, tc_target):
from .parser.shaping_rule import TcShapingRuleParser
from simplesqlite.query import Where
options = self._options
if options.filter_id:
ip_version = 6 if options.is_ipv6 else 4
shaping_rule_parser = TcShapingRuleParser(
device=tc_target,
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=TcSubCommand.FILTER.value,
columns=[
Tc.Param.SRC_NETWORK,
Tc.Param.DST_NETWORK,
Tc.Param.SRC_PORT,
Tc.Param.DST_PORT,
],
where=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]
if len(result) > 1 and options.direction == 'incoming':
filter_param = result[1]
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 = self._extract_dst_network()
src_network = self._extract_src_network()
dst_port = options.dst_port
src_port = options.src_port
return TrafficControl(
tc_target,
direction=options.direction,
dst_network=dst_network,
src_network=src_network,
dst_port=dst_port,
src_port=src_port,
is_ipv6=options.is_ipv6,
tc_command_output=options.tc_command_output,
)
def __create_tc_obj(self, tc_target):
from simplesqlite.query import Where
from .parser.shaping_rule import TcShapingRuleParser
options = self._options
if options.filter_id:
ip_version = 6 if options.is_ipv6 else 4
shaping_rule_parser = TcShapingRuleParser(
device=tc_target,
ip_version=ip_version,
tc_command_output=options.tc_command_output,
logger=logger,
)
shaping_rule_parser.parse()
for record in Filter.select(where=Where(Tc.Param.FILTER_ID,
options.filter_id), ):
dst_network = record.dst_network
src_network = record.src_network
dst_port = record.dst_port
src_port = record.src_port
break
else:
logger.error(
"shaping rule not found associated with the id ({}).".
format(options.filter_id))
sys.exit(1)
else:
dst_network = self._extract_dst_network()
src_network = self._extract_src_network()
dst_port = options.dst_port
src_port = options.src_port
return TrafficControl(
tc_target,
direction=options.direction,
dst_network=dst_network,
src_network=src_network,
dst_port=dst_port,
src_port=src_port,
is_ipv6=options.is_ipv6,
tc_command_output=options.tc_command_output,
)
def __get_filter_conditions(self):
if self.__tc.direction == TrafficDirection.OUTGOING:
device = self._parser.device
elif self.__tc.direction == TrafficDirection.INCOMING:
device = self._parser.ifb_device
return [
Where(Tc.Param.DEVICE, device),
Where(Tc.Param.PROTOCOL, self.__tc.protocol),
Where(Tc.Param.DST_NETWORK, self.__tc.dst_network),
Where(Tc.Param.SRC_NETWORK, self.__tc.src_network),
Where(Tc.Param.DST_PORT, self.__tc.dst_port),
Where(Tc.Param.SRC_PORT, self.__tc.src_port),
]
def main():
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"],
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=Where(key="key", value=1))
print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
class Test_SqlQuery_make_update(object):
@pytest.mark.parametrize(
["table", "set_query", "where", "expected"],
[
["A", "B=1", None, "UPDATE A SET B=1"],
[
"A", "B=1",
Where("C", 1, ">").to_query(), "UPDATE A SET B=1 WHERE C > 1"
],
["A", "B=1",
Where("C", 1, ">"), "UPDATE A SET B=1 WHERE C > 1"],
[
"A",
"B=1",
And([Where("C", 1, ">"), Where("D", 10)]),
"UPDATE A SET B=1 WHERE C > 1 AND D = 10",
],
[
"A",
"B=1",
Or([Where("C", 1, ">"), Where("D", 10)]),
"UPDATE A SET B=1 WHERE C > 1 OR D = 10",
],
],
)
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 __get_shaping_rule(self, device):
from simplesqlite.query import Where
if typepy.is_null_string(device):
return {}
self.__parse_device(device)
where_query = Where(Tc.Param.DEVICE, device)
try:
class_param_list = self.__con.select_as_dict(
table_name=TcSubCommand.CLASS.value, where=where_query)
except simplesqlite.TableNotFoundError:
class_param_list = []
try:
filter_param_list = self.__con.select_as_dict(
table_name=TcSubCommand.FILTER.value, where=where_query)
except simplesqlite.TableNotFoundError:
filter_param_list = []
try:
qdisc_param_list = self.__con.select_as_dict(
table_name=TcSubCommand.QDISC.value, where=where_query)
except simplesqlite.TableNotFoundError:
qdisc_param_list = []
shaping_rule_mapping = {}
for filter_param in filter_param_list:
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.FILTER, filter_param))
shaping_rule = {}
filter_key = self.__get_filter_key(filter_param)
if typepy.is_null_string(filter_key):
self.__logger.debug(
"empty filter key: {}".format(filter_param))
continue
for qdisc_param in qdisc_param_list:
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.QDISC, qdisc_param))
if qdisc_param.get(Tc.Param.PARENT) not in (
filter_param.get(Tc.Param.FLOW_ID),
filter_param.get(Tc.Param.CLASS_ID),
):
continue
shaping_rule[Tc.Param.FILTER_ID] = filter_param.get(
Tc.Param.FILTER_ID)
# shaping_rule[Tc.Param.PRIORITY] = filter_param.get(
# Tc.Param.PRIORITY)
shaping_rule.update(
self.__strip_param(
qdisc_param,
[Tc.Param.DEVICE, Tc.Param.PARENT, Tc.Param.HANDLE]))
for class_param in class_param_list:
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.CLASS, class_param))
if class_param.get(Tc.Param.CLASS_ID) not in (
filter_param.get(Tc.Param.FLOW_ID),
filter_param.get(Tc.Param.CLASS_ID),
):
continue
shaping_rule[Tc.Param.FILTER_ID] = filter_param.get(
Tc.Param.FILTER_ID)
# shaping_rule[Tc.Param.PRIORITY] = filter_param.get(
# Tc.Param.PRIORITY)
shaping_rule.update(
self.__strip_param(class_param,
[Tc.Param.DEVICE, Tc.Param.CLASS_ID]))
if not shaping_rule:
self.__logger.debug(
"shaping rule not found for '{}'".format(filter_param))
continue
self.__logger.debug("shaping rule found: {} {}".format(
filter_key, shaping_rule))
shaping_rule_mapping[filter_key] = shaping_rule
return shaping_rule_mapping
#!/usr/bin/env python
# encoding: utf-8
from __future__ import print_function
from simplesqlite import SimpleSQLite
from simplesqlite.query import Where
table_name = "sample_table"
con = SimpleSQLite("sample.sqlite", "w")
data_matrix = [[1, "aaa"], [2, "bbb"]]
con.create_table_from_data_matrix(table_name, ["key", "value"], 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=Where(key="key", value=1))
print("---- after update ----")
for record in con.select(select="*", table_name=table_name).fetchall():
print(record)
def __get_shaping_rule(self, device):
if typepy.is_null_string(device):
return ({}, [])
self.__parse_device(device)
where_dev_query = Where(Tc.Param.DEVICE, device)
try:
class_params = self.__con.select_as_dict(
table_name=TcSubCommand.CLASS.value, where=where_dev_query)
except TableNotFoundError:
class_params = []
try:
filter_params = Filter.select(where=where_dev_query)
except TableNotFoundError:
filter_params = []
shaping_rule_mapping = {}
shaping_rules = []
for filter_param in filter_params:
filter_param = filter_param.as_dict()
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.FILTER, filter_param))
shaping_rule = {}
filter_key, rule_with_keys = self.__get_filter_key(filter_param)
if typepy.is_null_string(filter_key):
self.__logger.debug(
"empty filter key: {}".format(filter_param))
continue
qdisc_id = filter_param.get(Tc.Param.FLOW_ID)
if qdisc_id is None:
qdisc_id = filter_param.get(Tc.Param.CLASS_ID)
try:
qdisc_params = Qdisc.select(where=And(
[where_dev_query,
Where(Tc.Param.PARENT, qdisc_id)]))
except TableNotFoundError:
qdisc_params = []
for qdisc_param in qdisc_params:
qdisc_param = qdisc_param.as_dict()
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.QDISC, qdisc_param))
if self.is_parse_filter_id:
shaping_rule[Tc.Param.FILTER_ID] = filter_param.get(
Tc.Param.FILTER_ID)
# shaping_rule[Tc.Param.PRIORITY] = filter_param.get(
# Tc.Param.PRIORITY)
shaping_rule.update(
self.__strip_param(
qdisc_param,
[
Tc.Param.DEVICE, Tc.Param.PARENT, Tc.Param.HANDLE,
"direct_qlen"
],
))
for class_param in class_params:
self.__logger.debug("{:s} param: {}".format(
TcSubCommand.CLASS, class_param))
if class_param.get(Tc.Param.CLASS_ID) not in (
filter_param.get(Tc.Param.FLOW_ID),
filter_param.get(Tc.Param.CLASS_ID),
):
continue
if self.is_parse_filter_id:
shaping_rule[Tc.Param.FILTER_ID] = filter_param.get(
Tc.Param.FILTER_ID)
# shaping_rule[Tc.Param.PRIORITY] = filter_param.get(
# Tc.Param.PRIORITY)
shaping_rule.update(
self.__strip_param(class_param,
[Tc.Param.DEVICE, Tc.Param.CLASS_ID]))
if not shaping_rule:
self.__logger.debug(
"shaping rule not found for '{}'".format(filter_param))
continue
self.__logger.debug("shaping rule found: {} {}".format(
filter_key, shaping_rule))
rule_with_keys.update(shaping_rule)
shaping_rules.append(rule_with_keys)
shaping_rule_mapping[filter_key] = shaping_rule
return (shaping_rule_mapping, shaping_rules)
def find_qdisc_handle(self, parent):
for qdisc in Qdisc.select(where=Where(Tc.Param.PARENT, parent)):
return qdisc.handle
return None
def find_qdisc_handle(self, parent):
return self._parser.con.fetch_value(
select=Tc.Param.HANDLE,
table_name=TcSubCommand.QDISC.value,
where=Where(Tc.Param.PARENT, parent),
)