def arrays2sql(self, array2, columns_order):
"""
数组对象导入到mysql
:param array2: 数组就是 Insert into 后面 values ({}) 这个对象
:param columns_order: 就是 insert into table({``,``}) 里面的对象。
:return:
"""
if len(array2) < 1:
return False, "数据不足插入"
# if len(array2[0]) != len(columns_order):
# return False, "带插入对象数据列不匹配"
_sql_str_list = []
for _item in array2:
_sql_str = "(\'" + "\',\'".join(_item.replace("'",
"\\dou")) + "\')"
_sql_str_list.append(_sql_str)
_query_sql = """insert into {table_name}({columns}) values {values_str};""".format(
values_str=", ".join([str(x) for x in _sql_str_list]),
table_name=self.table_name,
columns=columns_order)
try:
from xsqlmb.src.ltool.sqlconn import sql_action
sql_action(_query_sql)
return len(_sql_str_list)
except:
try:
from xsqlmb.src.cfgs.logConfig import logging
except:
import logging
logging.info("导入数据库失败！")
return 0
def accesslog_to_sql(self, local=False):
nad_datas = []
_accesslog_datas = self.get_latest_accsslog()
for x in _accesslog_datas:
obj = x.copy()
try:
obj["time_local"] = get_pydt_based_logdt(
re.match("(.*?)\s(.*)", obj["time_local"]).group(1))
obj["timestamp"] = obj["time_local"]
try:
obj["request_id"] = obj["request_id"]
except:
obj["request_id"] = uuid4()
obj["upstream_response_time"] = "0.01" if obj[
"upstream_response_time"] == "-" else "0.0"
obj["request_time"] = "0.01" if obj[
"upstream_response_time"] == "-" else "0.0"
except:
logging.error("Error:存在AccessLog日志不一样的正则 " + obj["time_local"])
continue
nad_datas.append(obj)
seccess_insert_num = self.many_insert2_accesslog(nad_datas)
logging.info("插入【" + str(seccess_insert_num) + "】条新数据到访问日志SQL数据库成功")
def modseclog_to_sql(self):
# from datetime import datetime
# default_time = datetime(1995, 8, 14)
nad_datas = self.get_latest_modseclog()
from django.core.paginator import Paginator
p = Paginator(nad_datas, self.MAX_INSERT_NUM) # 分页列别
page_count = p.num_pages # 总页数
seccess_insert_num = 0
from xsqlmb.api.logstash.utils.get_table_columns import get_waf_alert_log_columns
cols = get_waf_alert_log_columns()
_columns = "`" + "`, `".join(cols) + "`"
_keys = cols
for x in [x + 1 for x in range(page_count)]:
nad_list = list(p.page(x).object_list)
try:
_insert_num = MutiTypesInsets2SqlClass(table_name=WAF_ALERT_LOG_SQL_TABLE).arrays2sql2(
nad_list, columns_order=_columns, keys_list=_keys)
seccess_insert_num += len(_insert_num)
except:
logging.error("告警日志格式化存在键值对异常或者重复插入。")
finally:
import json
_detailed_list = [ [x["audit_logid"], json.dumps(x)] for x in nad_list ]
MutiTypesInsets2SqlClass(table_name=WAF_ALERT_LOG_DETAILED_SQL_TABLE).arrays2sql(
_detailed_list, columns_order="`audit_logid`,`detaild`"
)
logging.info("插入【" + str(seccess_insert_num) + "】条新数据到告警日志SQL数据库成功")
def modseclog_to_sql(self):
# from datetime import datetime
# default_time = datetime(1995, 8, 14)
nad_datas = self.get_latest_modseclog()
from django.core.paginator import Paginator
p = Paginator(nad_datas, self.MAX_INSERT_NUM) # 分页列别
page_count = p.num_pages # 总页数
seccess_insert_num = 0
from xsqlmb.api.logstash.utils.get_table_columns import get_waf_alert_log_columns
cols = get_waf_alert_log_columns()
_columns = "`" + "`, `".join(cols) + "`"
_keys = cols
for x in [x + 1 for x in range(page_count)]:
nad_list = list(p.page(x).object_list)
try:
_insert_num = MutiTypesInsets2SqlClass(
table_name=WAF_ALERT_LOG_SQL_TABLE).arrays2sql2(
nad_list, columns_order=_columns, keys_list=_keys)
seccess_insert_num += _insert_num
finally:
import json
# _detailed_list = [ [x["audit_logid"], json.dumps(x)] for x in nad_list ]
# MutiTypesInsets2SqlClass(table_name=WAF_ALERT_LOG_DETAILED_SQL_TABLE).arrays2sql(
# _detailed_list, columns_order="`audit_logid`,`detaild`"
# )
_detailed_list = [
dict(audit_logid=x["audit_logid"], detaild=json.dumps(x))
for x in nad_list
]
from xsqlmb.src.ltool.mongo import MongoConn
from xsqlmb.api.logstash.cfgs.configs import WAF_ALERT_LOG_DETAILED_SQL_TABLE
MongoConn().insert_data(WAF_ALERT_LOG_DETAILED_SQL_TABLE,
_detailed_list)
logging.info("插入【" + str(seccess_insert_num) + "】条新数据到告警日志SQL数据库成功")
def arrays2sql2(self, dict_array, columns_order, keys_list):
"""
数组对象导入到mysql
:param array2: 数组就是 Insert into 后面 values ({}) 这个对象
:param columns_order: 就是 insert into table({``,``}) 里面的对象。
:param keys_list: 字典插入时候需要的顺序key。
:return:
"""
if len(dict_array) < 1:
# return False, "数据不足插入"
return 0
_sql_str_list = []
for _item in dict_array:
_sql_str = "(\'" + "\',\'".join(
[str(_item[key]).replace("'", "\\dou")
for key in keys_list]) + "\')"
_sql_str_list.append(_sql_str)
_query_sql = """insert into {table_name}({columns}) values {values_str};""".format(
values_str=", ".join([str(x) for x in _sql_str_list]),
table_name=self.table_name,
columns=columns_order)
try:
from xsqlmb.src.ltool.sqlconn import sql_action
sql_action(_query_sql)
return len(_sql_str_list)
except:
# 一般来说到不了下面这一步。
try:
from xsqlmb.src.cfgs.logConfig import logging
except:
import logging
logging.info("导入数据库失败！")
return 0