def analyse_raw_data():
conn = DBUtil.getConnection("192.168.245.128", "slf", "slf1234", "SLF_DB")
sql = "SELECT trem_No,blue_Num,red_Num FROM TBL_Lottery_Raw_Data"
data = DBUtil.getDataBySQL(sql, conn)
if len(data) > 0:
final = []
for rawData in data:
tmp = [rawData[0]]
blue = (rawData[1].strip(",")).split(",")
red = (rawData[2].strip(",")).split(",")
if len(blue) == 5 and len(red) == 2:
blue.sort()
red.sort()
tmp += blue
tmp += red
# 计算每一组中奖数据的离散情况
ls_blue = _get_lsval(blue)
ls_red = _get_lsval(red)
tmp.append(ls_blue)
tmp.append(ls_red)
final.append(tmp)
if len(final) > 0:
# 这边对连接进行关闭
persistData.saveAnalysedData(final)
return len(final)
def __init__(self, db_name):
Storage.__init__(self)
self._db_name = db_name
DBUtil.execute_command(
db_name,
"CREATE TABLE IF NOT EXISTS Results (name TEXT PRIMARY KEY, value BLOB, started DATETIME, runtime FLOAT)"
)
def _update_worker(self, hostname, active=0):
name = hostname.split("@")[1]
heartbeat = time.time()
if active > 0:
DBUtil.execute_command(self._config.db_name, "UPDATE Workers SET heartbeat = ?, active = ? WHERE name = ?", (heartbeat, heartbeat, name))
else:
DBUtil.execute_command(self._config.db_name, "UPDATE Workers SET heartbeat = ? WHERE name = ?", (heartbeat, name))
def _delete_worker(self, workerid):
if self.get_number_of_workers() > self.get_min_number_of_workers():
try:
DBUtil.execute_command(self._db_name, "DELETE FROM Workers WHERE id = ?", (workerid,))
self._nc.servers.find(id=workerid).delete()
except NotFound:
print "worker is not-existent"
def _delete_worker_by_hostname(self, hostname):
"""
Internal method to delete worker from database by using its hostname
:param hostname:
:return:
"""
name = hostname.split("@")[1]
DBUtil.execute_command(self._config.db_name, "DELETE FROM Workers WHERE name = ?", (name,))
def saveDltData(dltdatas):
conn = DBUtil.getConnection("192.168.245.128", "slf", "slf1234", "SLF_DB")
for row in dltdatas:
sql = '''INSERT INTO TBL_Lottery_Raw_Data(trem_No,blue_Num,red_num) VALUES('{0}','{1}','{2}')'''.format(*row)
DBUtil.saveData(sql, conn)
conn.commit()
DBUtil.closeConnection(conn)
def __init__(self, config, db_name):
WorkerManager.__init__(self)
self._db_name = db_name
DBUtil.execute_command(db_name,
"CREATE TABLE IF NOT EXISTS Workers " +
"(id text PRIMARY KEY, name text, initialized boolean, started DATETIME, starttime float, heartbeat DATETIME, active DATETIME)")
self._config = config
self._nc = Client('2', **config.nova_config)
def task_started(self, event):
self._state.event(event)
hash_key = event['uuid']
started = event['timestamp']
print "task started"
DBUtil.execute_command(
self._config.db_name,
"UPDATE Results SET started = ? WHERE name = ?",
(started, hash_key))
def saveAnalysedData(analyseDatas):
conn = DBUtil.getConnection("192.168.245.128", "slf", "slf1234", "SLF_DB")
for row in analyseDatas:
sql = '''INSERT INTO TBL_Lottery_analysed_Data(trem_No,blue_No1,blue_No2,blue_No3,blue_No4,blue_No5,red_No1, red_No2,ls_blue,ls_red)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')'''.format(*row)
DBUtil.saveData(sql, conn)
conn.commit()
DBUtil.closeConnection(conn)
def __init__(self, config, db_name):
WorkerManager.__init__(self)
self._db_name = db_name
DBUtil.execute_command(
db_name, "CREATE TABLE IF NOT EXISTS Workers " +
"(id text PRIMARY KEY, name text, initialized boolean, started DATETIME, starttime float, heartbeat DATETIME, active DATETIME)"
)
self._config = config
self._nc = Client('2', **config.nova_config)
def _delete_worker_by_hostname(self, hostname):
"""
Internal method to delete worker from database by using its hostname
:param hostname:
:return:
"""
name = hostname.split("@")[1]
DBUtil.execute_command(self._config.db_name,
"DELETE FROM Workers WHERE name = ?", (name, ))
def _delete_worker(self, workerid):
if self.get_number_of_workers() > self.get_min_number_of_workers():
try:
DBUtil.execute_command(self._db_name,
"DELETE FROM Workers WHERE id = ?",
(workerid, ))
self._nc.servers.find(id=workerid).delete()
except NotFound:
print "worker is not-existent"
def batchSaveLotteryNumberPoolRed(red_nums_list):
# 根据两个参数进行全连接进行数据的保存
conn = DBUtil.getConnection("192.168.245.128", "slf", "slf1234", "SLF_DB")
# 循环保存所有可能的组合
for x in red_nums_list:
sql = '''insert into TBL_Lottery_Number_pool_red(red_No1,red_No2)
VALUES ('{0}','{1}') '''.format(*x)
DBUtil.saveData(sql, conn)
conn.commit()
DBUtil.closeConnection(conn)
return None
def worker_online(self, event):
"""
Event handler when worker comes online.
:param event:
"""
self._state.event(event)
name = event['hostname'].split("@")[1]
init_time = event['timestamp']
result = DBUtil.execute_command(self._config.db_name, "SELECT started FROM Workers WHERE name = ?", (name,), "ONE")
if result is not None:
starttime = init_time - result[0]
print "STARTTIME: " + str(starttime)
DBUtil.execute_command(self._config.db_name, "UPDATE Workers SET initialized = 'true', starttime = ?, heartbeat = ?, active = ? WHERE name = ? ", (starttime, time.time(), time.time(), name))
def _update_worker(self, hostname, active=0):
name = hostname.split("@")[1]
heartbeat = time.time()
if active > 0:
DBUtil.execute_command(
self._config.db_name,
"UPDATE Workers SET heartbeat = ?, active = ? WHERE name = ?",
(heartbeat, heartbeat, name))
else:
DBUtil.execute_command(
self._config.db_name,
"UPDATE Workers SET heartbeat = ? WHERE name = ?",
(heartbeat, name))
def delete_inactive_workers(self):
results = DBUtil.execute_command(
self._db_name,
"SELECT id FROM Workers WHERE initialized = 'true' AND active < ?",
(time.time() - 60.0, ), "ALL")
for result in results:
self._delete_worker(result[0])
def _start_workers(self, num):
# basic parameters
image = self._nc.images.find(name="G14Worker")
flavor = self._nc.flavors.find(name="m1.medium")
cloud_init = "#!/bin/bash \n" + \
" cd /home/ubuntu/airfoil-cloud-simulator \n" + \
"git reset --hard && git pull \n" + \
"echo '" + self._config.key + "' >> key.aes\n" + \
"echo '" + self._config.iv + "' >> iv.txt\n"\
" su -c 'celery -A workertasks worker -b amqp://cloudworker:worker@" + \
server_ip() + "//' ubuntu"
for i in xrange(0, int(num)):
name = "g14worker" + str(uuid.uuid1())
server = self._nc.servers.create(name, image, flavor, userdata=cloud_init)
DBUtil.execute_command(self._db_name,
"INSERT INTO Workers(id, name, initialized, started, active) VALUES (?,?, 'false', ?, ?)",
(server.id, name, time.time(), time.time()))
def save_result_hash(self, hash_key, result, started=None, endtime=None):
precheck_result = DBUtil.execute_command(
self._db_name, "SELECT started FROM Results WHERE name = (?)",
(hash_key, ), "ONE")
if precheck_result is not None:
if started is not None:
DBUtil.execute_command(
self._db_name,
"UPDATE Results SET started = ?, value = ? WHERE name = ?",
(started, hash_key))
if endtime is not None:
if precheck_result[0] is not None:
runtime = endtime - precheck_result[0]
else:
runtime = -1
print "RUNTIME: " + str(runtime)
print "HASHKEY: " + str(hash_key)
DBUtil.execute_command(
self._db_name,
"UPDATE Results SET runtime = ?, value = ? WHERE name = ?",
(str(runtime), result, str(hash_key)))
print "result already in database"
return True
if endtime is not None and started is not None:
runtime = endtime - started
else:
runtime = None
DBUtil.execute_command(
self._db_name,
"INSERT INTO Results(name, value, started, runtime) VALUES (?,?,?,?)",
(hash_key, result, started, runtime))
return True
def worker_online(self, event):
"""
Event handler when worker comes online.
:param event:
"""
self._state.event(event)
name = event['hostname'].split("@")[1]
init_time = event['timestamp']
result = DBUtil.execute_command(
self._config.db_name, "SELECT started FROM Workers WHERE name = ?",
(name, ), "ONE")
if result is not None:
starttime = init_time - result[0]
print "STARTTIME: " + str(starttime)
DBUtil.execute_command(
self._config.db_name,
"UPDATE Workers SET initialized = 'true', starttime = ?, heartbeat = ?, active = ? WHERE name = ? ",
(starttime, time.time(), time.time(), name))
def _get_avg_task_time(self):
results = DBUtil.execute_command(
self._config.db_name,
"SELECT runtime FROM Results WHERE runtime IS NOT NULL AND runtime != -1",
None, "ALL")
if len(results) > 0:
result_sum = 0.0
for result in results:
result_sum += result[0]
return result_sum / float(len(results))
else:
return 0.0
def has_result(self, model_params, compute_params):
"""
This method returns whether for the set of parameters passed an entry is found.
:param model.ModelParameters.ModelParameters model_params: ModelParameters
:param model.ComputeParameters.ComputeParameters compute_params: ComputeParameters
:return: bool true if an entry is found, false otherwise
"""
hash_key = self.generate_hash(model_params, compute_params)
result = DBUtil.execute_command(self._db_name, "SELECT value FROM Results WHERE name = (?)", (hash_key,), "ONE")
print result
return result is not None and result[0] != 'null'
def _start_workers(self, num):
# basic parameters
image = self._nc.images.find(name="G14Worker")
flavor = self._nc.flavors.find(name="m1.medium")
cloud_init = "#!/bin/bash \n" + \
" cd /home/ubuntu/airfoil-cloud-simulator \n" + \
"git reset --hard && git pull \n" + \
"echo '" + self._config.key + "' >> key.aes\n" + \
"echo '" + self._config.iv + "' >> iv.txt\n"\
" su -c 'celery -A workertasks worker -b amqp://cloudworker:worker@" + \
server_ip() + "//' ubuntu"
for i in xrange(0, int(num)):
name = "g14worker" + str(uuid.uuid1())
server = self._nc.servers.create(name,
image,
flavor,
userdata=cloud_init)
DBUtil.execute_command(
self._db_name,
"INSERT INTO Workers(id, name, initialized, started, active) VALUES (?,?, 'false', ?, ?)",
(server.id, name, time.time(), time.time()))
def _get_average_worker_startup_time(self):
results = DBUtil.execute_command(
self._config.db_name,
"SELECT starttime FROM Workers WHERE starttime IS NOT NULL", None,
"ALL")
if len(results) > 0:
result_sum = 0.0
for result in results:
result_sum += result[0]
return result_sum / float(len(results))
else:
return 0.0
def has_result(self, model_params, compute_params):
"""
This method returns whether for the set of parameters passed an entry is found.
:param model.ModelParameters.ModelParameters model_params: ModelParameters
:param model.ComputeParameters.ComputeParameters compute_params: ComputeParameters
:return: bool true if an entry is found, false otherwise
"""
hash_key = self.generate_hash(model_params, compute_params)
result = DBUtil.execute_command(
self._db_name, "SELECT value FROM Results WHERE name = (?)",
(hash_key, ), "ONE")
print result
return result is not None and result[0] != 'null'
def get_result(self, model_params, compute_params):
"""
Returns the result associated with the set of parameters, None otherwise.
:param model.ModelParameters.ModelParameters model_params: ModelParameters
:param model.ComputeParameters.ComputeParameters compute_params: ComputeParameters
:rtype model.ComputeResult.ComputeResult|None
"""
hash_key = self.generate_hash(model_params, compute_params)
result = DBUtil.execute_command(self._db_name,
"SELECT value, runtime, started FROM Results WHERE name = (?)",
(hash_key,), "ONE")
print "result: " + str(result[0])
print "runtime: " + str(result[1])
print "started: " + str(result[2])
return result
def get_result(self, model_params, compute_params):
"""
Returns the result associated with the set of parameters, None otherwise.
:param model.ModelParameters.ModelParameters model_params: ModelParameters
:param model.ComputeParameters.ComputeParameters compute_params: ComputeParameters
:rtype model.ComputeResult.ComputeResult|None
"""
hash_key = self.generate_hash(model_params, compute_params)
result = DBUtil.execute_command(
self._db_name,
"SELECT value, runtime, started FROM Results WHERE name = (?)",
(hash_key, ), "ONE")
print "result: " + str(result[0])
print "runtime: " + str(result[1])
print "started: " + str(result[2])
return result
def test_03__update_emp(self, username, http_code, success, code, message):
"""
修改员工测试用例
:return:
"""
# 发送修改员工接口
response = self.emp_api.update_emp(username)
# 获取返回数据
json_data = response.json()
logging.info("修改后返回的数据为:{}".format(json_data))
# 断言
assert_common(self, response, http_code, success, code, message)
# 数据库操作
with DBUtil() as db_util:
sql = "select username from bs_user where id={}".format(app.EMP_ID)
db_util.execute(sql)
result = db_util.fetchone()[0]
logging.info("数据库返回数据为:{}".format(result))
try:
self.assertEqual(username, result)
except AssertionError as e:
raise e
def save_result_hash(self, hash_key, result, started=None, endtime=None):
precheck_result = DBUtil.execute_command(self._db_name,
"SELECT started FROM Results WHERE name = (?)", (hash_key,), "ONE")
if precheck_result is not None:
if started is not None:
DBUtil.execute_command(self._db_name, "UPDATE Results SET started = ?, value = ? WHERE name = ?", (started, hash_key))
if endtime is not None:
if precheck_result[0] is not None:
runtime = endtime - precheck_result[0]
else:
runtime = -1
print "RUNTIME: " + str(runtime)
print "HASHKEY: " + str(hash_key)
DBUtil.execute_command(self._db_name, "UPDATE Results SET runtime = ?, value = ? WHERE name = ?", (str(runtime), result, str(hash_key)))
print "result already in database"
return True
if endtime is not None and started is not None:
runtime = endtime - started
else:
runtime = None
DBUtil.execute_command(self._db_name, "INSERT INTO Results(name, value, started, runtime) VALUES (?,?,?,?)",
(hash_key, result, started, runtime))
return True
def get_number_of_workers(self):
return DBUtil.execute_command(self._db_name,
"SELECT COUNT(*) FROM Workers", None,
"ONE")[0]
def _shutdown_workers(self, num_workers):
ids = DBUtil.execute_command(self._db_name, "SELECT id FROM Workers",
None, num_workers)
for row in ids:
serverid = row[0]
self._delete_worker(serverid)
def delete_terminated_workers(self):
DBUtil.execute_command(
self._config.db_name,
"DELETE FROM Workers WHERE initialized = 'true' AND heartbeat < ?",
(time.time() - 60.0, ))
def select_nums():
conn = DBUtil.getConnection("192.168.245.128", "slf", "slf1234", "SLF_DB")
sql = "SELECT ls_blue,ls_red FROM TBL_Lottery_analysed_Data"
data = DBUtil.getDataBySQL(sql, conn)
def get_status():
return jsonify({"num_workers": worker_manager.get_number_of_workers(),
"running_tasks": DBUtil.execute_command(config.db_name, "SELECT COUNT(*) FROM Results WHERE value = 'null'",
None, "ONE")[0]})
def _shutdown_workers(self, num_workers):
ids = DBUtil.execute_command(self._db_name, "SELECT id FROM Workers", None, num_workers)
for row in ids:
serverid = row[0]
self._delete_worker(serverid)
def delete_terminated_workers(self):
DBUtil.execute_command(self._config.db_name,
"DELETE FROM Workers WHERE initialized = 'true' AND heartbeat < ?", (time.time() - 60.0,))
def delete_inactive_workers(self):
results = DBUtil.execute_command(self._db_name, "SELECT id FROM Workers WHERE initialized = 'true' AND active < ?", (time.time() - 60.0,), "ALL")
for result in results:
self._delete_worker(result[0])
def _get_current_queue_length(self):
results = DBUtil.execute_command(
self._config.db_name,
"SELECT COUNT(*) FROM Results WHERE value = 'null'", None, "ONE")
return results[0]
def task_started(self, event):
self._state.event(event)
hash_key = event['uuid']
started = event['timestamp']
print "task started"
DBUtil.execute_command(self._config.db_name, "UPDATE Results SET started = ? WHERE name = ?", (started, hash_key))
def __init__(self, db_name):
Storage.__init__(self)
self._db_name = db_name
DBUtil.execute_command(db_name, "CREATE TABLE IF NOT EXISTS Results (name TEXT PRIMARY KEY, value BLOB, started DATETIME, runtime FLOAT)")
def get_number_of_workers(self):
return DBUtil.execute_command(self._db_name, "SELECT COUNT(*) FROM Workers", None, "ONE")[0]