def search_db(keywords, limit=200):
sql = (
" SELECT * FROM `hindu` WHERE match(`title`, `link`) against('%s' IN BOOLEAN MODE) ORDER BY `id` desc LIMIT %d;"
% (keywords, limit))
query = Query()
res = query.search(sql)
query.close()
return res
def __init__(self):
self.mysql = MySQL()
self.influx = InfluxDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.influx.init_cfg(cfg)
self._con = self.mysql.get_connection()
self.query.init_db(self._con, self.influx.get_client())
def __init__(self):
self.mysql = MySQL()
self.mongo = MongoDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.mongo.init_cfg(cfg)
self._con = self.mysql.get_connection()
self._client = self.mongo.get_client()
self.query.init_db(self._con, self._client)
self._db = self._client[cfg.MONGODB_COLLECTION]
def iterate_repository_pagination(self, session, pagination, count):
"""Iterate on repository pagination"""
pages = int(count / 30)
for self.page in range(self.page, pages):
if check_exit(
{"all", "repository_crawler", "repository_crawler.py"}):
raise RuntimeError("Found .exit file. Exiting")
if folder_size(str(config.BASE_DIR)) > config.MAX_SIZE:
raise RuntimeError("Content folder is too big. Clean it up")
if config.VERBOSE > 1:
print("> Processing page {}".format(self.page))
repositories = pagination.get_page(self.page)
for repository in repositories:
load_repository(session, "github.com", repository.full_name)
session.commit()
self.status.count += 1
self.status.report()
query = Query(
name="repository",
query=self.query,
first_date=self.first_date,
last_date=self.last_date,
delta=self.delta,
count=count,
)
session.add(query)
session.commit()
if config.VERBOSE > 0:
print("> Finished query. ID={}".format(query.id))
def cached_query(address, provider):
address = re.sub(r"\s+", " ", address.upper())
session = Session(expire_on_commit=False)
provider_name = provider.__class__.__name__
hashcode = hashlib.md5(bytes(address, encoding="utf-8")).hexdigest()
cached = _query(session, hashcode, provider_name)
if not cached:
try:
response = provider.geocode(address)
except Exception as e:
print(e)
response = None
if response:
cached = Query(
hashcode=hashcode,
address=address,
latitude=response.latitude,
longitude=response.longitude,
provider=provider_name,
)
session.add(cached)
session.commit()
# session.expunge(cached)
# session.expunge_all()
session.close()
return cached
class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.influx = InfluxDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.influx.init_cfg(cfg)
self._con = self.mysql.get_connection()
self.query.init_db(self._con, self.influx.get_client())
def getQuery(self):
return self.query
def close(self):
return self.mysql.close()
def getallMAC(self, table):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT distinct("MAC") as MAC FROM "{}";'.format(
escape(table)))
return result.raw
def getallSensor(self, table, mac):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery('SELECT s,topic FROM "{}" WHERE MAC=$mac;'.format(
escape(table)),
bind_params=param)
return result.raw
def getallTopic(self, table, mac):
infQuery = self.query.getInfQuery()
result = infQuery('SELECT topic,MAC,status FROM "{}"'.format(
escape(table)))
for sensor in result.raw["series"][0]["values"]:
smac = sensor[2]
topic = sensor[1]
if (smac == mac):
return ['topic', topic, 'MAC', smac]
def getLastCT(self, sid, _type="ct"):
infQuery = self.query.getInfQuery()
param = {"s": sid}
result = infQuery('SELECT a FROM "{}" WHERE s=$s LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
def getLastPIR(self, mac, _type="pir"):
infQuery = self.query.getInfQuery()
param = {"mac": mac}
result = infQuery(
'SELECT status FROM "{}" WHERE MAC=$mac LIMIT 1'.format(
escape(_type)),
bind_params=param)
return result.raw
def query_gen_with_string_comp(self, param, comp, value):
string_comps = ['contains', "startsWith", "endsWith", "exact"]
if comp in string_comps:
if comp == 'contains':
value = f'\"%{value}%\" '
if comp == 'startsWith':
value = f'\"{value}%\"'
if comp == 'endsWith':
value = f'\"%{value}\"'
comp = 'LIKE'
return Query(param, comp, value)
def get_metric_values(self, eid, metric_queries, num_values):
queried_metrics = {q.param: q for q in metric_queries}
metric_rows = []
for met in self.metrics:
q = Query(met, 'IS NOT', 'NULL')
if met in queried_metrics:
q = queried_metrics[met]
metric_rows.append(list(self._db.metric_aggregate(eid, num_values, q)))
return metric_rows
def __init__(self, eid, hyperparameter_queries, metric_queries, num_values=100):
self._db = Database()
self.eid = eid
self.row_names = self.get_row_names()
self.metrics_queries = metric_queries
self.hyperparameter_queries = hyperparameter_queries
self.hyperparameter_queries.append(Query('eid', '=', eid))
hyperparam_headings = ['eid', 'rid', *self._db.experiment_hypers[self.eid]]
self.hyperparameters = Table("HyperParameter", hyperparameter_queries,
headings=hyperparam_headings)
self.metrics = self.get_metrics(eid)
self.metric_values = self.get_metric_values(eid, metric_queries, num_values)
self._rows = []
self.num_hyper = len(self.hyperparameters.col_names)
self._col_names = []
def landingPage():
t = Table(table="Experiment")
rows = t.rows
eids = []
number_of_runs = []
experiment_names = []
for row in rows:
experiment_names.append(row[1])
eids.append(row[0])
t_for_count = Table('Run', [Query('eid', '=', row[0])])
number_of_runs.append(len(t_for_count.rows))
template_args = {
"experiment_names": experiment_names,
"number_of_runs": number_of_runs,
"eid": eids,
}
return render_template('landing.html', **template_args)
def start_requests(self):
query = Query()
query.create()
query.close()
start_date = datetime.date(year=2017, month=2, day=1)
end_date = datetime.date(year=2017, month=3,
day=19) # datetime.date.today()
urls = []
for date in self.daterange(start_date, end_date):
self.date = date
date_formatted = date.strftime('%Y/%m/%d/')
url = self.base_url + date_formatted
yield scrapy.Request(url=url, callback=self.parse)
def insert(self, data):
sql = """INSERT INTO `hindu`(`title`,
`link`, `date`)
VALUES """
db_date = self.date.strftime('%Y-%m-%d')
f = open('/home/atul/sql.txt', 'w')
for row in data:
sql += '(\'' + row[0] + '\'' + ', ' + '\'' + row[
1] + '\'' + ', ' + '\'' + db_date + '\'),'
sql = sql.rstrip(',')
#f.write(sql)
query = Query()
query.execute(sql)
query.close()
def get_row_names(self):
_row_names = {}
q = self._db.run_query('Run', [Query('eid', '=', self.eid)])
for _, rid, run_name in q:
_row_names[rid] = run_name
return _row_names
class dbHandler():
def __init__(self):
self.mysql = MySQL()
self.mongo = MongoDB()
self.query = Query()
cfg = getConfig()
self.mysql.init_cfg(cfg)
self.mongo.init_cfg(cfg)
self._con = self.mysql.get_connection()
self._client = self.mongo.get_client()
self.query.init_db(self._con, self._client)
self._db = self._client[cfg.MONGODB_COLLECTION]
def getQuery(self):
return self.query
def getallMAC(self, table): #/
allmac = self._db[table].find().distinct("message.MAC")
return allmac
def getallSensor(self, table, mac): #/
result = self._db[table].find(
{"$and": [{
"message.MAC": mac
}, {
"message.s": {
"$exists": True
}
}]})
alltopic = result.distinct("topic")
allnum = result.distinct("message.s")
res = []
for s in allnum:
for topic in alltopic:
res.append({"s": s, "topic": topic})
return res
def getallTopic(self, table, mac): #/
result = self._db[table].find_one(
{"$or": [{
"message.MAC": mac
}, {
"message.s": mac
}]})
return ['topic', result["topic"], 'MAC', result["message"]["MAC"]]
def getLastVAL(self, sid, mac, _type="ct"):
createdAt = 0
res = None
query = {"message.MAC": mac}
if (_type.split("_") == "dm"):
query["message.s"] = sid
rawCT = self._db[_type].find(query).sort("created_at", -1).limit(1)
for CT in rawCT:
if (CT["created_at"] > createdAt):
res = CT
createdAt = CT["created_at"]
if (_type.split("_")[0] == "dm"):
return (res["message"]["AL1"] + res["message"]["AL2"] +
res["message"]["AL3"]) / 3
return res["message"]["a"]
def getLastPIR(self, mac, _type="pir"):
createdAt = 0
res = {}
for t in self.getTable({"sensor_type": _type}):
rawPIR = self._db[t].find({
"message.MAC": mac
}).sort("created_at", -1).limit(1)
for PIR in rawPIR:
if (PIR["created_at"] > createdAt):
res = PIR
createdAt = PIR["created_at"]
return res
def getTable(self, devFind={}): #/
res = []
for _type in self._db["iot_type"].find(devFind):
name = _type["sensor_type"]
if (_type["device_type"] != ''):
name = name + "_" + _type["device_type"]
res.append(name)
return res
from flask import escape
from config import Config, ProductionConfig, DevelopmentConfig
from db import MySQL, MongoDB, Query
mysql = MySQL()
mongoDB = MongoDB()
query = Query()
def html_escape(inp):
return str(escape(inp))
def toHourandMin(timeDel):
secs = timeDel.total_seconds()
hours = int(secs / 3600)
minutes = int(secs / 60) % 60
return "%02d:%02d" % (hours, minutes)
def getConfig():
cfg = ProductionConfig
if Config.ENABLE_DEV:
cfg = DevelopmentConfig
return cfg
