class DataBaseOperate(object):
def __init__(self):
self.__log = Log("DataBaseOperate", 'DEBUG').logger
self.__db_pool = None
def creat_db_pool(self, mysql):
user = mysql.get('MYSQL_USER')
password = mysql.get('MYSQL_PASSWD')
port = int(mysql.get('MYSQL_PORT'))
host = mysql.get('MYSQL_HOST')
self.__log.debug('创建数据库连接池：%s' % host)
self.__db_pool = PooledDB(creator=pymysql,
mincached=3,
maxcached=5,
maxshared=0,
maxconnections=20,
blocking=True,
maxusage=None,
setsession=None,
host=host,
port=port,
user=user,
db=None,
passwd=password)
self.__log.debug('创建数据库连接池完成!')
def query_data(self, sql):
con = self.__db_pool.connection()
cursor = con.cursor(cursor=pymysql.cursors.DictCursor)
try:
self.__log.error(sql)
cursor.execute(sql)
results = cursor.fetchall()
self.__log.debug(results)
for result in results:
for fields in result:
if isinstance(result[fields], datetime.datetime):
result[fields] = str(
result[fields].strftime('%Y-%m-%d %H:%M:%S'))
elif isinstance(result[fields], datetime.date):
result[fields] = str(
result[fields].strftime('%Y-%m-%d'))
elif isinstance(result[fields], decimal.Decimal):
result[fields] = float(result[fields])
return results
except Exception as e:
self.__log.error('执行sql异常：\n%s' % e)
self.__log.error(sql)
finally:
cursor.close()
con.close()
def close_db_pool(self):
self.__db_pool.close()
" success")
else:
LOG.info("[Mapping] create " + index + "/" + doc_type +
" failed")
return res["acknowledged"]
else:
raise Exception(res)
except Exception, e:
raise e
else:
if es.indices.create(index=index, body=settings):
if doc_type is not None and mappings is not None:
res = es.indices.put_mapping(index=index,
doc_type=doc_type,
body=mappings)
LOG.debug("create mappings response: " + str(res))
if "acknowledged" in res:
if res["acknowledged"]:
LOG.info("[Mapping] create " + index + "/" + doc_type +
" success")
else:
LOG.info("[Mapping] create " + index + "/" + doc_type +
" failed")
return res["acknowledged"]
else:
raise Exception(res)
else:
LOG.info("[Index] create index=" + index + " success")
return True
else:
LOG.error("create " + index + "/" + doc_type + " Failed")
class dataProcessing(Config, QuerySqliteData):
def __init__(self,
configurationName=None,
logName='dataProcessing',
logLevel='DEBUG'):
super(dataProcessing, self).__init__()
AssemblyConfig()
self.configurationName = configurationName
self.sender_email = None
self.password = None
self.mysql_dict = None
self.conf = None
self.smtpHost = None
self.L = Log(logName, logLevel).logger
self.abs_path = os.path.dirname(os.path.abspath(__file__))
self.initialize_parameter()
self.db = DataBaseOperate()
def initialize_parameter(self):
"""
"""
if self.configurationName:
self.conf = self.query_execution_conf(self.configurationName)
else:
self.L.debug("按照时间执行任务")
self.conf = self.query_execution_conf(str(datetime.now().hour))
if self.conf:
env_conf = self.env_conf()
self.sender_email = env_conf.get('EMAIL_SENDER')
self.password = env_conf.get('EMAIL_PASSWD')
self.mysql_dict = eval(env_conf.get('MYSQL_DICT'))
self.smtpHost = self.conf.smtp_host
self.assembly_receiver_data()
else:
# 传入的KEY没有在config文件中找到，或者执行时间与配置时间不匹配
self.L.debug("没有可执行配置，请确认传入KEY或执行时间")
raise Exception("没有可执行配置，请确认传入KEY或执行时间")
def assembly_receiver_data(self):
"""
获取收件人邮箱
:return:
"""
receobj = self.query_receiver_conf(self.conf)
receiver = []
for rece in receobj:
receiver.append(rece.email)
self.receiver = receiver
def assembly_mapping_data(self, datas: list, lordId=None, viceId=None):
"""
处理需要映射的数据
:param datas: 查询结果数据
:param lordId: 主表id
:param viceId: 副表id
:return:
"""
mapping_list = self.query_mapping_conf(lordId, viceId)
for mapping in mapping_list:
mapping_file = mapping.mapping_file
mapping_key = eval(mapping.mapping_key)
enumerate_dict = Config().get_config(name=mapping_file,
current_path=ENUMERATE_PATH)
datas = conversion.replace_dict_value(
replace_key=mapping_key,
keep_dict=datas,
enumerate_dict=enumerate_dict)
return datas
def _merge_data(self, conf: object, datas: list):
"""
合并数据
:return:
"""
try:
datas.remove(())
except ValueError:
pass
try:
datas.remove(None)
except ValueError:
pass
if datas == [] or datas is None:
return None
if (conf.merge or conf.bus_vice_merge) and len(datas) > 1:
# 判断是否达到合并条件
merge_key = conf.merge_key if conf.merge else conf.bus_vice_merge_key
if merge_key == 'None' or merge_key is None:
# 合并单条类型的数据
if isinstance(datas[0], dict):
merge_data = datas
else:
merge_data = list(chain.from_iterable(datas))
result = {}
[result.update(i) for i in merge_data]
merge_data = [result]
else:
merge_data = DataAggregate().get_aggregate_result_copy(
datas, merge_key)
return merge_data
else:
return datas[0]
def assembly_lord_data(self):
"""
组装主数据
:return:
"""
lordData = {}
bus_conf_all = self.query_busuness_conf(confobj=self.conf)
for bus_conf in bus_conf_all:
lord_conf_all = self.query_lord_conf(confObj=bus_conf)
querydata = []
for lord_conf in lord_conf_all:
# 遍历拿到的主配置，并先处理副表数据
datas = []
vicedata = self.assembly_vice_data(lord_conf)
sql_all = self.assembly_replace_data(lord_conf, vicedata)
current_db_info = self.mysql_dict.get(lord_conf.db_name)
self.db.creat_db_pool(current_db_info)
for sql in sql_all:
_result = self.db.query_data(sql)
_result = self.assembly_mapping_data(_result, lord_conf.id)
datas.append(_result)
if lord_conf.bus_vice_merge:
datas.append(vicedata[0])
querydata.append(self._merge_data(lord_conf, datas))
lordData[bus_conf.table_title] = self._merge_data(
lord_conf, querydata)
return lordData
def assembly_replace_data(self, lordconf: object, vicedata: list):
"""
替换SQL字段
:param lordconf:
:param vicedata:
:param sqlList:
:return:
"""
sql_list = lordconf.busuness_sql.split(';')
replace_conf_all = self.query_replace_data_conf(lordconf)
sql_all = []
if not replace_conf_all:
for sql in sql_list:
if sql != '':
sql = sql + ';'
sql_all.append(sql)
return sql_all
for sql in sql_list:
for replace_conf in replace_conf_all:
for vice in vicedata:
vice_original = DataAggregate.data_assemble(
key=replace_conf.condition_value, parameters_ld=vice)
if len(vice_original) > 1:
sql = sql.replace(replace_conf.replace_value,
str(tuple(vice_original)))
elif len(vice_original) == 1:
sql = sql.replace(replace_conf.replace_value,
str(vice_original[0]))
if sql != '':
sql = sql + ';'
sql_all.append(sql)
return sql_all
def assembly_vice_data(self, lord_conf: object):
"""
获取汇总表的条件数据
:param lord_conf: 传数据库对象
:return:
"""
viceData = []
vice_all = self.query_vice_conf(lordConfObj=lord_conf)
# 获取数据表对象，进行遍历
for vice_conf in vice_all:
vice_sql = vice_conf.data_sql
db_name = vice_conf.data_db_name
self.db.creat_db_pool(self.mysql_dict.get(db_name))
sql_list = vice_sql.split(';')
datas = []
for sql in sql_list:
if sql == '':
continue
datas.append(self.db.query_data(sql=sql + ';'))
if len(datas) > 1:
viceData.append(list(chain.from_iterable(datas)))
elif len(datas) == 1:
viceData.append(datas[0])
if len(viceData) > 1:
viceData = list(chain.from_iterable(viceData))
return viceData
ips: 当前页码解析数据
"""
bs = BeautifulSoup(request_get(url), "html5lib")
page_num = bs.find("div", "pagination")
current_num = int(page_num.find("em", "current").text)
num_href = page_num.find_all("a")
total_num = current_num
for a in num_href:
try:
num = int(a.text)
if total_num < num:
total_num = num
except Exception, e:
pass
LOG.debug("current_num=%d ,total_num=%d" % (current_num, total_num))
ips = []
ip_list = bs.find("table").find_all("tr")
for ip in ip_list:
var = ip.find_all("td")
if not var:
continue
d = ({})
country = string_format(var[0].text)
if not country:
country = u"中国"
d.setdefault("country", country)
d.setdefault("ip_addr", string_format(var[1].text))
d.setdefault("port", int(string_format(var[2].text)))
