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 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 find_filter_param(self):
import simplesqlite
where_list = self.__get_filter_where_condition_list()
where_query = And(where_list)
table_name = TcSubCommand.FILTER.value
self.__logger.debug("find filter param: table={}, where={}".format(
table_name, where_query))
try:
result = self._parser.con.select_as_dict(
column_list=[
Tc.Param.FILTER_ID, Tc.Param.PRIORITY, Tc.Param.PROTOCOL
],
table_name=table_name,
where=where_query,
)
except simplesqlite.TableNotFoundError:
return None
if not result:
self.__logger.debug(
"find filter param: emptry result (table={}, where={})".format(
table_name, where_query))
return None
param = result[0]
self.__logger.debug(
"find filter param: result={}, table={}, where={}".format(
param, table_name, where_query))
return param
def find_parent(self):
where_list = self.__get_filter_where_condition_list()
table_name = TcSubCommand.FILTER.value
parent = self._parser.con.fetch_value(select=Tc.Param.FLOW_ID,
table_name=table_name,
where=And(where_list))
self.__logger.debug(
"find parent: result={}, table={}, where={}".format(
parent, table_name, where_list))
return parent
def find_filter_param(self):
where_query = And(self.__get_filter_conditions())
table_name = Filter.get_table_name()
self.__logger.debug("find filter param: table={}, where={}".format(
table_name, where_query))
for record in Filter.select(where=where_query):
return record.as_dict()
self.__logger.debug(
"find filter param: empty result (table={}, where={})".format(
table_name, where_query))
return None
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)
)
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):
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_parent(self):
for record in Filter.select(where=And(self.__get_filter_conditions())):
return record.flowid
return None
