class Asset(object):
def __init__(self):
self.mysql = Mysql()
def createTask(self, config):
state = 1
ident = 0
scan_type = 1
dete_obj = 'ALL'
vul_plug_id = '0'
content_type = config
now_time = time.strftime('%Y-%m-%d %H:%M:%S')
task_name = "AUTO" + time.strftime('%Y%m%d%H%M')
sql1 = "insert into tbl_task (start_time,task_name,dete_obj,vul_plug_id,scan_type,content_type,add_time,state,ident) values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"
_id = self.mysql.insertOne(
sql1, (now_time, task_name, dete_obj, vul_plug_id, scan_type,
content_type, now_time, state, ident))
t_id = int(_id)
if content_type == 2:
LeakScan().scan(dete_obj, vul_plug_id, t_id)
else:
AssetDiscovery().find_asset(dete_obj, content_type, vul_plug_id,
t_id)
end_time = time.strftime('%Y-%m-%d %H:%M:%S')
asset_count = self.find_asset_count()
sql2 = "update tbl_task set end_time=%s,asset_count=%s where id=%s"
self.mysql.update(sql2, (end_time, asset_count, t_id))
def find_asset_count(self):
sql = "select * from tbl_asset where state=%s"
count = self.mysql.getCount(sql, (1))
return int(count)
def get_cycle_data_from_db(table_name, col_name):
db = Mysql()
query_sql = "select " + col_name + " from " + table_name + " where status = %s order by rand() limit 1"
# update_sql = "update " + table_name + " set status = 1 where " + col_name + " = %s" # 取数完更新状态的sql
result = db.getOne(query_sql, [0])
# db.update(update_sql, [result[col_name]]) # 更新状态
db.dispose()
return result[col_name]
def get_one():
# 申请资源
mysql = Mysql()
get_sql = "SELECT * FROM posture_result ORDER BY update_time DESC LIMIT 1"
result = mysql.getOne(get_sql)
# print("{:}\t{:}".format(result['update_time'],result['score']))
print(type(result))
print(result)
# 释放资源
mysql.dispose()
def CVeReturnNsfocus(str1):
result = []
cvelist = str1.split("\n");
mysql = Mysql()
for cveone in cvelist:
sql = "SELECT * FROM nsfocusvul where vul_cve = '%s'" % (cveone.strip())
res = mysql.getOne(sql)
if res != False:
resobj = VulInfo(res["vul_id"],res["vul_cve"],u''+res["vul_name"],u''+res["vul_desc"],u''+res["vul_soul"],res["vul_data"])
result.append(resobj)
mysql.dispose()
return result
def insert_data():
# 申请资源
mysql = Mysql()
roll_max = 0.0999989
roll_min = 0.050000374106515644
roll_mean = 0.07501550874813788
roll_count = 100
pitch_max = 0.09999891074324113
pitch_min = 0.050000374106515644
pitch_mean = 0.07501550874813788
pitch_count = 1000
deviation_max = 0.09999891074324113
deviation_min = 0.050000374106515644
deviation_mean = 0.07501550874813788
deviation_count = 100
total_score = 87.76071883296575
insert_sql = "INSERT INTO posture_result(update_time,delta_roll_max,delta_roll_min,delta_roll_mean,delta_roll_count,delta_pitch_max,delta_pitch_min,delta_pitch_mean,delta_pitch_count,delta_deviation_max,delta_deviation_min,delta_deviation_mean,delat_deviation_count,score) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
update_time = datetime.datetime.now().strftime("%Y%m%d%H%M%S%f")
mysql.insertMany(
insert_sql,
[(update_time, roll_max, roll_min, roll_mean, roll_count, pitch_max,
pitch_min, pitch_mean, pitch_count, deviation_max, deviation_min,
deviation_mean, deviation_count, total_score)])
# 释放资源
mysql.dispose()
def InsertVul(request, VulInfo):
if VulInfo != None:
mysql = Mysql()
try:
sql = "INSERT INTO `vulinfo`.`nsfocusvul` VALUES (NULL, %s, %s, %s, %s, %s, %s)"
param = (VulInfo.vul_id, VulInfo.vul_cve, VulInfo.vul_name,
VulInfo.vul_desc, VulInfo.vul_soul, VulInfo.vul_data)
print str(param) + "----- insert-ok"
mysql.insertOne(sql, param)
mysql.end()
except Exception, e:
Writefile("mysqllog.txt", str(e) + VulInfo.tostr() + "\n")
print e
mysql.dispose()
def apriori():
if request.method == 'OPTIONS':
return template('status:200')
name = request.json['name']
print(name)
mysql = Mysql()
taskSql = "SELECT * FROM trans_type_1"
taskList = mysql.getAll(taskSql)
nodeSql = "SELECT t_1,t_2,t_3,t_4,t_5,t_6,t_7 FROM task_seq WHERE station_name=" + "'" + name + "'"
nodeTaskList = mysql.getAll(nodeSql)
mysql.dispose()
task = []
for x in nodeTaskList:
nodeTask = []
for i in range(7):
if x['t_' + str(i + 1)] is not None:
nodeTask.append(x['t_' + str(i + 1)])
task.append(nodeTask)
df = pd.DataFrame({'task_seq': task})
df.to_csv('task.csv', index=False, sep=',')
inFile = dataFromFile('task.csv')
items, rules = runApriori(inFile, 0.10, 0.5)
rule = dealResults(taskList, rules)
# return template('{"ret":{{ret}},"task":{{task}}}',task=rule,ret=200)
return {"ret": 200, "task": rule}
def predict():
if request.method == 'OPTIONS':
return template('status:200')
os.chdir("/home/czhou/python/model")
gbr_r = joblib.load("pre_r_model.m")
#户数 写字楼面积 周围网点数 银行 地区
# test_X=[[150000,8300000,50,2,3]]
print(request.json)
loc = request.json['loc']
region = request.json['region']
bank = request.json['bank']
test = [region, bank]
test_r = [test]
pre_r = gbr_r.predict(test_r)
print(pre_r)
mysql = Mysql()
tmp = getNewLatLng(loc['lat'], loc['lng'], pre_r[0])
netSql = 'SELECT COUNT(name) AS data from bankdata_copy WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
r1 = mysql.getOne(netSql)
print(r1)
firmSql = 'SELECT person_num from firm_info WHERE flat >' + str(
tmp['minLat']) + ' AND flat <' + str(
tmp['maxLat']) + ' AND flng >' + str(
tmp['minLng']) + ' AND flng <' + str(tmp['maxLng'])
r2 = mysql.getAll(firmSql)
person_num = 0
if r2 != False:
for k in r2:
person_num += k['person_num']
print(person_num)
xqSql = 'SELECT total_house from fdd_xq WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
r3 = mysql.getAll(xqSql)
total_house = 0
if r3 != False:
for k in r3:
total_house += k['total_house']
print(total_house)
mysql.dispose()
test_b = [[r1['data'], person_num, total_house, region, bank]]
gbr_b = joblib.load("pre_bsum_model.m")
pre_b = gbr_b.predict(test_b)
print(pre_b)
return template('{"bsum":{{bsum}}}', bsum=pre_b)
def request1(appkey, movieName, m="GET"):
values = list()
url = "http://op.juhe.cn/onebox/movie/video"
params = {
"key": appkey, # 应用APPKEY(应用详细页查询)
"dtype": "json", # 返回数据的格式,xml或json，默认json
"q": movieName, # 影视搜索名称
}
params = urlencode(params)
if m == "GET":
f = urllib.urlopen("%s?%s" % (url, params))
else:
f = urllib.urlopen(url, params)
content = f.read()
res = json.loads(content)
if res:
error_code = res["error_code"]
if error_code == 0:
# 成功请求
mysql = Mysql()
url_list = getJsonContent((res["result"]))
# print((res["result"])['cover'])
for i in range(len(url_list)):
global pic_id
values.append([pic_id, url_list[i]])
pic_id += 1
mysql.insertMany(
'INSERT IGNORE INTO picture(picture_id,picture_url) values(%s,%s)',
values)
mysql.dispose()
else:
print("%s:%s" % (res["error_code"], res["reason"]))
else:
print("request api error")
def __init__(self):
self.mysql = Mysql()
class ScanMaster(object):
def __init__(self):
self.mysql = Mysql()
def logs(self):
info = sys.exc_info()
level = 4
lylog = log.LogMaster('scan_log', info, level)
lylog.buildLog()
def identify(self, port, protocol, banner):
vul_list = []
sql = "select id,port,state from sys_vul"
info = self.mysql.getAll(sql)
for msg in info:
if msg['state']:
port_list = msg['port'].split(',')
for p in port_list:
if str(port) == p:
vul_list.append(msg['id'])
return vul_list
def choose(self, port, vul_plug_id):
vul_list = []
sql = "select port from sys_vul where id=%s and state=1"
vid_list = vul_plug_id.split(',')
for i in vid_list:
result = self.mysql.getOne(sql, (i))
data = result['port']
port_list = str(data).split(',')
if str(port) in port_list:
vul_list.append(i)
return vul_list
def door(self, t_id, vul_plug_id, ip, port, protocol, banner, content):
url = ''
state = {}
data = ''
vid_list = []
if vul_plug_id == '0':
try:
vid_list = self.identify(port, protocol, banner)
except Exception:
self.logs()
else:
try:
vid_list = self.choose(port, vul_plug_id)
except Exception:
self.logs()
for i in vid_list:
s_name = 's' + str(i)
if s_name == 's1168':
if content and 'hikvision' not in content.lower():
continue
result = eval(s_name).start(ip, port, protocol)
if result:
state, url, data = result
if state:
self.insert_vullist(ip, port, url, i, data, t_id)
else:
self.updete_vullist(ip, port, i, t_id)
def find_count(self, ip, port, vul_id):
sql1 = "select * from tbl_vullist where IP=%s and port=%s and vul_id=%s"
sql2 = "select vul_name,grade from sys_vul where id=%s"
result = self.mysql.getOne(sql2, (vul_id))
grade = result['grade']
vul_name = result['vul_name']
data_count = self.mysql.getCount(sql1, (ip, port, vul_id))
result2 = self.mysql.getOne(sql1, (ip, port, vul_id))
vullist_id = None
if result2:
vullist_id = result2['id']
return int(data_count), vullist_id, vul_name, grade
def change(self, ip):
ip_list = ip.split('.')
ip_list1 = copy.copy(ip_list)
for i in ip_list:
if len(i) == 2:
ip_list1[ip_list1.index(i)] = '0' + i
elif len(i) == 1:
ip_list1[ip_list1.index(i)] = '00' + i
return int(''.join(ip_list1))
def find_area_id(self, ip):
try:
sql = 'select area_id,IP_location from sys_ip_location where inet_aton(IP_start) <= inet_aton(%s) AND inet_aton(IP_end)>=inet_aton(%s);'
result = self.mysql.getOne(sql, (ip, ip))
area_id = result['area_id']
ip_location = result['IP_location']
return area_id, ip_location
except Exception:
try:
country, province, city, operator = location.find_location(ip)
if country and province:
sql2 = 'select code from sys_administrative_area where name=%s or name=%s'
result = self.mysql.getOne(sql2, (city, province))
code = result['code']
ip_location2 = province + city
sql3 = "insert into sys_ip_location (IP_start,IP_end,IP_loc_county,IP_loc_province,IP_loc_city,IP_loc_district,operator,area_id,IP_location,aton_start,aton_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,inet_aton(%s),inet_aton(%s))"
self.mysql.insertOne(
sql3, (ip, ip, country, province, city, '', operator, code, ip_location2, ip, ip))
return code, ip_location2
except Exception:
return '', ''
def insert_vullist(self, ip, port, url, vul_id, detail, t_id):
count, vullist_id, vul_name, grade = self.find_count(ip, port, vul_id)
now_time = time.strftime('%Y-%m-%d %H-%M-%S')
if count:
sql = "update tbl_vullist set update_time=%s,is_repair=%s,is_newly_added=%s where IP=%s and port=%s and vul_id=%s"
self.mysql.update(sql, (now_time, 0, 1, ip, port, vul_id))
else:
print '---->', ip
area_id, IP_location = self.find_area_id(ip)
sql = "insert into tbl_vullist (IP,port,url,vul_id,add_time,detail,vul_name,grade,t_id,area_id,IP_location) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
vullist_id = self.mysql.insertOne(sql, (ip, port, url, vul_id, now_time, detail, vul_name, grade, t_id, area_id, IP_location))
self.count_vul(t_id, vullist_id, vul_id, 1)
def updete_vullist(self, ip, port, vul_id, t_id):
count, vullist_id, vul_name, grade = self.find_count(ip, port, vul_id)
if count:
now_time = time.strftime('%Y-%m-%d %H-%M-%S')
sql = "update tbl_vullist set repair_time=%s,update_time=%s,is_repair=%s,is_newly_added=%s where IP=%s and port=%s and vul_id=%s"
self.mysql.update(sql, (now_time, now_time, 1, 0, ip, port, vul_id))
self.count_vul(t_id, vullist_id, vul_id, 2)
def count_vul(self, t_id, vullist_id, vul_id, state):
sql = "select vul_info,vul_count from tbl_task where id=%s"
result = self.mysql.getOne(sql, (t_id))
vul_info = result['vul_info']
vul_count = result['vul_count']
vul_list = vul_count.split(',')
w_all = vul_list[0]
w_1 = vul_list[1]
w_2 = vul_list[2]
w_3 = vul_list[3]
sql2 = "select grade from tbl_vullist where id=%s"
result = self.mysql.getOne(sql2, (vullist_id))
grade = result['grade']
if state == 1:
if grade == 1:
w_3 = str(int(w_3) + 1)
w_all = str(int(w_all) + 1)
elif grade == 2:
w_2 = str(int(w_2) + 1)
w_all = str(int(w_all) + 1)
else:
w_1 = str(int(w_1) + 1)
w_all = str(int(w_all) + 1)
insert_count = []
insert_count.append(w_all)
insert_count.append(w_1)
insert_count.append(w_2)
insert_count.append(w_3)
new_vul_count = ','.join(insert_count)
if state == 1:
new_vul_info = str(vullist_id)
if vul_info:
new_vul_info = vul_info + ',' + str(vullist_id)
else:
new_vul_info = vul_info
if vul_info:
info_list = vul_info.split(',')
if str(vul_id) in info_list:
new_vul_info = ','.join(info_list.remove(str(vul_id)))
sql3 = "update tbl_task set vul_info=%s,vul_count=%s where id=%s"
self.mysql.update(sql3, (new_vul_info, new_vul_count, t_id))
#encoding=utf-8 """ author: wdw110 """ from MySqlConn import Mysql from _sqlite3 import Row #申请资源 mysql = Mysql() sqlAll = '' result = mysql.getAll(sqlAll) if result: print 'get all' for row in result: print '%s\t%s'%() sqlAll = '' result = mysql.getMany(sqlAll,2) if result: print 'get many' for row in result: print '' result = mysql.getOne(sqlAll) print 'get one' print '' #释放资源
# coding=utf-8
import sys
import log
import time
import smtplib
from MySqlConn import Mysql
from email.mime.text import MIMEText
mysql = Mysql()
msg_from = '*****@*****.**' # 发送方邮箱
passwd = 'ngscanner2018' # 填入发送方邮箱的授权码
def logs():
info = sys.exc_info()
level = 4
lylog = log.LogMaster('scan_log', info, level)
lylog.buildLog()
def send(ip, vul_name):
if on2off():
emali = find_email(ip)
if emali:
time1 = time.strftime('%Y-%m-%d')
time2 = time.strftime('%Y年%m月%d日')
subject = "风险通报"
content = '''
管理员：
你好！
''' + time2 + '''专项漏洞''' + vul_name + '''扫描中发现IP：''' + ip + '''存在该漏洞，请及时处理！
# coding:utf-8
import requests
import re
from MySqlConn import Mysql
# 申请资源
mysql = Mysql()
for i in range(2):
page = i * 50
page_url = "http://tieba.baidu.com/f?kw=%E5%90%8C%E6%B5%8E%E5%A4%A7%E5%AD%A6&ie=utf-8&pn=" + str(
page)
r = requests.get(page_url)
ret = re.findall(r'(<a href="/p/\d+)', r.text)
print(len(ret))
values = list()
topic = '同济大学'
for j in ret:
baidu_id = 'http://tieba.baidu.com'
baidu_id += j[9:]
values.append([baidu_id, topic])
mysql.insertMany(
'INSERT IGNORE INTO baidu_info(baidu_id,topic) values(%s,%s)', values)
# 释放资源
mysql.dispose()
from MySqlConn import Mysql
# mysql = Mysql()
# sqlAll = "select * from t_perform_test"
# result = mysql.getAll(sqlAll)
# if result:
# print "get all"
# for row in result:
# print "%s\t%s" %(row["name"], row["password"])
#
# mysql.dispose()
mysql = Mysql()
sql_in = "insert into test.t_perform_test values(%s, %s)"
for i in range(1, 1000):
params = ["liuyang" + str(i), "password" + str(i)]
num = mysql.insertOne(sql_in, params)
print num
mysql.dispose()
def on_gift_message(msg):
print '[%s] 礼物 %-30s---> %s' % (datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S'), msg.attr('nn'), msg.attr('gfid'))
sql = 'INSERT INTO giftmsg_%s(nn,uid,level,gfid,time)VALUES(%s,%s,%s,%s,NOW())'
mysql.insertOne(sql, (int(roomid), msg.attr('nn'), msg.attr('uid'),
msg.attr('level'), msg.attr('gfid')))
mysql.commit()
def run():
room = ChatRoom(roomid)
room.on('chatmsg', on_chat_message)
room.on('dgb', on_gift_message)
room.knock()
def createTable():
tableChatMsg = 'CREATE TABLE IF NOT EXISTS `chatmsg_%s`(`nn` VARCHAR(20),`uid` INT,`ct` TINYINT,`txt` VARCHAR(100),`level` TINYINT,`time` DATETIME)CHARSET=utf8 COLLATE=utf8_unicode_ci'
mysql.update(tableChatMsg, int(roomid))
tableGiftMsg = 'CREATE TABLE IF NOT EXISTS `giftmsg_%s`(`nn` VARCHAR(20),`uid` INT,`level` TINYINT,`gfid` SMALLINT,`time` DATETIME)CHARSET=utf8 COLLATE=utf8_unicode_ci'
mysql.update(tableGiftMsg, int(roomid))
if __name__ == '__main__':
roomid = sys.argv[1]
mysql = Mysql()
createTable()
run()
mysql.dispose()
def prebsum():
mysql = Mysql()
sql = 'SELECT bsum,node_in_prer,firm_in_prer,house_in_prer,region_index,bank_index FROM pre_r WHERE region_index=2 or region_index=7 or region_index=8'
res = mysql.getAll(sql)
mysql.dispose()
a = []
for x in res:
b = []
b.append(x['bsum'])
b.append(x['node_in_prer'])
b.append(x['firm_in_prer'])
b.append(x['house_in_prer'])
b.append(x['region_index'])
b.append(x['bank_index'])
a.append(b)
np.savetxt('to_predict_bsum.csv', a, delimiter=',')
mse_arr = []
b = permutate('pre_bsum_train.csv', 0.98)
file_rows = b['file_rows'] - b['test_rows']
train_rows = math.floor(file_rows * 0.9)
np.savetxt('train.csv', b['train'], delimiter=',')
np.savetxt('test.csv', b['test'], delimiter=',')
for time in range(50):
os.chdir("C:/Users/yufeng/Desktop/python")
t = permutate('train.csv', 0.98)
np.savetxt('train_t.csv', t['train'], delimiter=',')
df = pd.read_csv('train_t.csv', header=0, encoding='utf-8')
y_train, x_train = df.ix[0:train_rows, 0:1], df.ix[0:train_rows, 1:]
y_test, x_test = df.ix[train_rows:, 0:1], df.ix[train_rows:, 1:]
params = {
'n_estimators': 100,
'max_depth': 5,
'min_samples_split': 2,
'learning_rate': 0.01,
'loss': 'ls'
}
gbr = ensemble.GradientBoostingRegressor(**params)
gbr.fit(x_train, y_train)
os.chdir("/home/czhou/python/model")
joblib.dump(gbr, "pre_bsum_model" + str(time) + ".m")
y_pre = gbr.predict(x_test)
y_test = np.array(y_test)
m = []
n = []
for i, j in enumerate(y_pre):
m.append(y_pre[i] / (y_test[i][0] + y_pre[i]))
n.append(y_test[i][0] / (y_test[i][0] + y_pre[i]))
mse = mean_squared_error(n, m)
mse_arr.append(mse)
a = mse_arr.index(min(mse_arr))
os.chdir("/home/czhou/python")
df1 = pd.read_csv('to_predict_bsum.csv', header=0, encoding='utf-8')
y_test, x_test = df1.ix[0:, 0:1], df1.ix[0:, 1:]
os.chdir("/home/czhou/python/model")
gbr = joblib.load("pre_bsum_model" + str(a) + ".m")
print("train_model" + str(a) + ".m")
joblib.dump(gbr, "pre_bsum_model.m")
y_pre1 = gbr.predict(x_test)
index = np.arange(1, 24, 1)
m = []
n = []
y_test = np.array(y_test)
for i, j in enumerate(y_pre1):
m.append(y_pre1[i] / (y_test[i][0] + y_pre1[i]))
n.append(y_test[i][0] / (y_test[i][0] + y_pre1[i]))
mse = mean_squared_error(n, m)
print("MSE: %.4f" % mse)
class LeakScan(object):
def __init__(self):
self.mysql = Mysql()
def logs(self):
info = sys.exc_info()
level = 4
lylog = log.LogMaster('scan_log', info, level)
lylog.buildLog()
def Redis(self):
pool = redis.ConnectionPool(host='localhost',
port=6379,
decode_responses=True)
conn = redis.Redis(connection_pool=pool)
return conn
def mod_vul_plug_id(self, vul_plug_id):
port_list = []
id_list = []
if ',' in vul_plug_id:
id_list = vul_plug_id.split(',')
else:
id_list = [vul_plug_id]
if id_list:
for i in id_list:
sql1 = 'select port from sys_vul where id=' + str(i)
result = self.mysql.getOne(sql1)
string_port = str(result['port'])
if ',' in string_port:
p_list = string_port.split(',')
for port in p_list:
port = port.replace(' ', '')
if str(port) not in port_list:
port_list.append(str(port))
else:
if str(string_port) not in port_list:
port_list.append(str(string_port.replace(' ', '')))
str_port = str(tuple(port_list))
if len(port_list) == 1:
str_port = str_port.replace(',', '')
sql = "select IP,port,protocol,html_page,banner from tbl_asset_info where port IN" + str_port
return sql
else:
return "select IP,port,protocol,html_page,banner from tbl_asset_info"
def find_range_ip(self, dete_obj):
ip_list = dete_obj.split('-')
ip_start = ip_list[0]
ip_end = ip_list[-1]
sql = 'select IP,port,protocol,html_page,banner from tbl_asset_info where inet_aton(%s)<= inet_aton(ip) AND inet_aton(%s)>=inet_aton(ip)'
info = self.mysql.getAll(sql, (ip_start, ip_end))
count = self.mysql.getCount(sql)
return count, info
def find_asset_count(self):
sql = "select * from tbl_asset where state=%s"
count = self.mysql.getCount(sql, (1))
return int(count)
def process(self, num1, num2):
a = float(num1)
b = float(num2)
sche = "%.f%%" % (b / a * 100)
return sche
def scan(self, dete_obj, vul_plug_id, t_id):
rcon = self.Redis()
if not rcon.hgetall(t_id):
rcon.hmset(t_id, {'state': 0, 'count': 0})
sql1 = "select IP,port,protocol,html_page,banner from tbl_asset_info"
if vul_plug_id != '0':
sql1 = self.mod_vul_plug_id(vul_plug_id)
info = self.mysql.getAll(sql1)
count = self.mysql.getCount(sql1)
redis_dic = rcon.hgetall(t_id)
state = redis_dic['state']
now_count = int(redis_dic['count'])
if '-' in dete_obj:
count, info = self.find_range_ip(dete_obj)
if count != 0:
for index, msg in enumerate(info, 1):
if state == '1':
if index <= now_count:
continue
rcon.hincrby(t_id, 'count', amount=1)
ip = msg['IP']
port = msg['port']
protocol = msg['protocol']
html = msg['html_page']
banner = msg['banner']
print ip, port
try:
ScanMaster().door(t_id, vul_plug_id, ip, port, protocol,
banner, html)
except Exception:
self.logs()
proces = self.process(count, index)
asset_count = self.find_asset_count()
sql2 = "update tbl_task set curr_process=%s,asset_count=%s where id=%s"
self.mysql.update(sql2, (proces, asset_count, t_id))
now_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql3 = "update tbl_task set end_time=%s,state=%s where id=%s"
self.mysql.update(sql3, (now_time, 2, t_id))
else:
now_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql4 = "update tbl_task set end_time=%s,curr_process=%s,state=%s where id=%s"
self.mysql.update(sql4, (now_time, '100%', 2, t_id))
rcon.delete(t_id)
sql5 = 'delete from tbl_task_manage where t_id=%s'
self.mysql.delete(sql5, (t_id))
def getscore():
# 申请资源
mysql = Mysql()
sql = "SELECT station_name FROM original_data WHERE station_name!='财务部' GROUP BY station_name"
nameList = mysql.getAll(sql)
#释放资源
mysql.dispose()
loanM = []
monthBsum = []
replaceDeg = []
bsumGrowth = []
xqHouse = []
xzlArea = []
for x in nameList:
mysql = Mysql()
#每个网点的贷款金额
loanOne = 0
loanSql = 'SELECT loan_money FROM original_data WHERE station_name=' + "'" + x[
'station_name'] + "'"
# loanRes = mysql.getAll(loanSql)
# for k in loanRes:
# if k['loan_money']!=None :
# loanOne += k['loan_money']
# loanM.append(loanOne)
#单个网点每月业务总量
bsumOne = 0
bsumSql = 'SELECT count(_index) AS data FROM original_data WHERE station_name=' + "'" + x[
'station_name'] + "'"
# bsumRes = mysql.getOne(bsumSql)
# if bsumRes['data']:
# monthBsum.append(bsumRes['data'])
# else:
# monthBsum.append(bsumOne)
#单个网点的可替代程度
replaceSql = 'SELECT bratio,lg_to_min FROM bankdata_copy WHERE name=' + "'" + x[
'station_name'] + "'"
# replaceRes = mysql.getOne(replaceSql)
# replaceDeg.append(replaceRes)
#未来总业务量增长
#网点定位 业务覆盖半径
nodeLocSql = 'SELECT lng,lat,cover_r FROM bankdata_copy WHERE name=' + "'" + x[
'station_name'] + "'"
nodeLoc = mysql.getOne(nodeLocSql)
tmp = getNewLatLng(nodeLoc['lat'], nodeLoc['lng'], nodeLoc['cover_r'])
#网点周围新建小区 户数
newXq = 0
newXqSql = 'SELECT house_num from ajk_newxq WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
newXqRes = mysql.getAll(newXqSql)
print(newXqRes)
if newXqRes != False:
for k in newXqRes:
if k['house_num'] != None:
newXq += k['house_num']
xqHouse.append(newXq)
#网点周围新建写字楼 面积
newXzl = 0
newXzlSql = 'SELECT area from ajk_newxzl WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
newXzlRes = mysql.getAll(newXzlSql)
if newXzlRes != False:
for k in newXzlRes:
newXzl += k['area']
xzlArea.append(newXzl)
mysql.dispose()
sumxq = sum(xqHouse)
sumxzl = sum(xzlArea)
bsumGrowthScore = []
for i, j in enumerate(xqHouse):
tmp = j * 0.06238087 + xzlArea[i] * 0.16797319
bsumGrowth.append(tmp)
maxBG = max(bsumGrowth)
mysql = Mysql()
for i, j in enumerate(nameList):
tmp = round((bsumGrowth[i] / maxBG) * 100, 2)
tmpSql = 'UPDATE node_score SET future_bsum_growth=' + str(
tmp) + ' WHERE _name=' + "'" + j['station_name'] + "'"
mysql.update(tmpSql)
mysql.dispose()
def handle(self):
print 'got connection from ', self.client_address
#self.wfile.write('connection %s:%s at %s succeed!' % (host,port,ctime()))
#self.wfile.write(senddata)
data = ''
while True:
recvdata = self.request.recv(1024)
if not recvdata:
break
if data == '' and recvdata[:2] != '\x40\x40':
continue
if data == '' and recvdata[:2] != '\x40\x40' and recvdata[
-2:] == '\x23\x23':
continue
if recvdata[:2] == '\x40\x40' and recvdata[-2:] == '\x23\x23':
data = recvdata
if recvdata[:2] == '\x40\x40' and recvdata[-2:] != '\x23\x23':
data += recvdata
continue
if recvdata[:2] != '\x40\x40' and recvdata[-2:] != '\x23\x23':
data += recvdata
continue
if recvdata[:2] != '\x40\x40' and recvdata[-2:] == '\x23\x23':
data += recvdata
if data != '' and data[:2] == '\x40\x40' and data[
-2:] == '\x23\x23':
#print data
#if ord(data[26])==2:
dd = data[2:-2]
arr = dd.split('##@@')
desadd = ''
for i in arr:
s = ''
for j in i[16:22][::-1]:
s += hex(ord(j))[2:].zfill(2)
desadd = s
if True:
recvdate = ''
for i in arr:
s = ''
for j in i[4:10][::-1]:
s += str(ord(j)).zfill(2)
recvdate = s
resadd = ''
for i in arr:
s = ''
for j in i[10:16][::-1]:
s += hex(ord(j))[2:].zfill(2)
resadd = s
codenumber = ''
jsoninfo = ''
if ord(data[27]) == 2:
tmpinfo = pmd.parse_typeflag_02(data[27:-3])
codenumber = tmpinfo['codenumber']
jsoninfo = json.dumps(tmpinfo)
if ord(data[27]) == 204:
tmpinfo = pmd.parse_typeflag_204(data[27:-3])
codenumber = tmpinfo['codenumber']
jsoninfo = json.dumps(tmpinfo)
if ord(data[27]) == 206:
tmpinfo = pmd.parse_typeflag_206(data[27:-3])
codenumber = tmpinfo['codenumber']
jsoninfo = json.dumps(tmpinfo)
if ord(data[27]) == 1:
tmpinfo = pmd.parse_typeflag_01(data[27:-3])
codenumber = ''
jsoninfo = json.dumps(tmpinfo)
if ord(data[27]) == 24:
tmpinfo = pmd.parse_typeflag_24(data[27:-3])
codenumber = ''
jsoninfo = json.dumps(tmpinfo)
if ord(data[27]) == 205:
tmpinfo = pmd.parse_typeflag_205(data[27:-3])
codenumber = ''
jsoninfo = json.dumps(tmpinfo)
mysql = Mysql()
sql = "insert into mintordata(mintortime,resadd,desadd,datainfo,controlunit,typeflag,codenumber,jsoninfo) values(%s,%s,%s,%s,%s,%s,%s,%s)"
values_info = (recvdate, resadd, desadd, data[27:-3],
ord(data[26]), ord(data[27]), codenumber,
jsoninfo)
results_value = mysql.insertOne(sql, values_info)
mysql.dispose()
#cur.execute('insert into mintordata(mintortime,resadd,desadd,datainfo,controlunit,typeflag,codenumber,jsoninfo) values(%s,%s,%s,%s,%s,%s,%s,%s)',(recvdate,resadd,desadd,data[27:-3],ord(data[26]),ord(data[27]),codenumber,jsoninfo))
#conn.commit()
print "RECV from ", self.client_address[
0], " at ", self.client_address[
1], " recvdata at ", recvdate, " resadd at ", resadd, " desadd at ", desadd
#senddata=data[:6]+second+minute+hour+day+month+year+data[18:24]+data[12:18]+data[24:26]+'\x03'
senddata = data[:
6] + second + minute + hour + day + month + year + data[
18:24] + data[
12:18] + '\x00\x00' + '\x03'
#senddata=data[:6]+second+minute+hour+day+month+year+data[18:24]+data[12:18]+data[24:26]+'\x04'+data[27:-3]
checkstr = chr(uchar_checksum(senddata[2:]))
senddata += checkstr + '##'
self.request.send(senddata)
data = ''
from MySqlConn import Mysql mysql = Mysql() sql_up = "update market.market_user set status = 0 where telephone = 18626330613" mysql.update(sql_up, param=None) mysql.dispose()
def getCoverR():
mysql = Mysql()
nameSql = 'SELECT name FROM bankdata_copy'
res1 = mysql.getAll(nameSql)
for x in res:
crSql = 'SELECT f_s_dis,flng,flat,slng,slat FROM original_data WHERE station_name=' + "'" + x[
'name'] + "'"
res2 = mysql.getAll(crSql)
nodeRadius = 0
sorted(res2.items(), key=lambda item: item[0])
if len(res2) > 0:
nodeRadius = res2[math.floor(len(res2) * 0.2)]['f_s_dis']
tmp = res2[math.floor(len(res2) * 0.2)]
k = getNewLatLng(tmp['slat'], tmp['slng'], nodeRadius)
xqSql = 'SELECT total_house from fdd_xq WHERE lat >' + str(
k['minLat']) + ' AND lat <' + str(
k['maxLat']) + ' AND lng >' + str(
k['minLng']) + ' AND lng <' + str(k['maxLng'])
res3 = mysql.getAll(xqSql)
total_house = 0
for m in res3:
total_house += m['total_house']
print(total_house)
xzlSql = 'SELECT area from biz_bld WHERE lat >' + str(
k['minLat']) + ' AND lat <' + str(
k['maxLat']) + ' AND lng >' + str(
k['minLng']) + ' AND lng <' + str(k['maxLng'])
res4 = mysql.getAll(xzlSql)
total_area = 0
for m in res3:
total_area += m['area']
print(total_area)
netSql = 'SELECT COUNT(name) AS data from bankdata_copy WHERE lat >' + str(
k['minLat']) + ' AND lat <' + str(
k['maxLat']) + ' AND lng >' + str(
k['minLng']) + ' AND lng <' + str(k['maxLng'])
r1 = mysql.getOne(netSql)
print(r1)
firmSql = 'SELECT person_num from firm_info WHERE flat >' + str(
k['minLat']) + ' AND flat <' + str(
k['maxLat']) + ' AND flng >' + str(
k['minLng']) + ' AND flng <' + str(k['maxLng'])
r2 = mysql.getAll(firmSql)
person_num = 0
for m in r2:
person_num += m['person_num']
print(person_num)
sql_r = 'UPDATE bankdata_copy SET cover_r =' + str(
nodeRadius
) + ',xq_house=' + str(total_house) + ',xzl_area=' + str(
total_area) + ',near_node=' + str(
r1['data']) + ',firmb_in_r=' + str(
person_num) + ' WHERE name=' + "'" + x['name'] + "'"
r4 = mysql.update(sql_r)
mysql.dispose()
class AssetDiscovery(object):
def __init__(self):
self.mysql = Mysql()
def logs(self):
info = sys.exc_info()
level = 4
lylog = log.LogMaster('scan_log', info, level)
lylog.buildLog()
def find_range_ip(self, dete_obj):
ip_list = dete_obj.split('-')
ip_start = ip_list[0]
ip_end = ip_list[-1]
sql = 'select IP,detection_times,port_info from tbl_asset where inet_aton(%s)<= inet_aton(ip) AND inet_aton(%s)>=inet_aton(ip)'
info = self.mysql.getAll(sql, (ip_start, ip_end))
count = self.mysql.getCount(sql)
return count, info
def process(self, num1, num2):
a = float(num1)
b = float(num2)
sche = "%.f%%" % (b / a * 100)
return sche
def find_asset_count(self):
sql = "select * from tbl_asset where state=%s"
count = self.mysql.getCount(sql, (1))
return int(count)
def find_asset(self, dete_obj, content_type, vul_plug_id, t_id):
sql1 = "select IP,detection_times,port_info from tbl_asset"
info = self.mysql.getAll(sql1)
count = self.mysql.getCount(sql1)
if '-' in dete_obj:
count, info = self.find_range_ip(dete_obj)
if count != 0:
for index, msg in enumerate(info, 1):
ip = msg['IP']
print ip
self.scan(msg, content_type, vul_plug_id, t_id)
proces = self.process(count, index)
asset_count = self.find_asset_count()
sql2 = "update tbl_task set curr_process=%s,asset_count=%s where id=%s"
self.mysql.update(sql2, (proces, asset_count, t_id))
now_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql3 = "update tbl_task set end_time=%s,state=%s where id=%s"
self.mysql.update(sql3, (now_time, 2, t_id))
else:
now_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql4 = "update tbl_task set end_time=%s,curr_process=%s,state=%s where id=%s"
self.mysql.update(sql4, (now_time, '100%', 2, t_id))
def update_asset(self, host, os, items, all_port, device_type, device_info):
area_id, IP_location = self.find_area_id(host)
detection_times = int(items) + 1
state = 1
if all_port:
state = 1
else:
state = 0
update_time = time.strftime('%Y-%m-%d %H:%M:%S')
sql = "update tbl_asset set update_time=%s,area_id=%s,IP_location=%s,state=%s,detection_times=%s,os=%s,device_type=%s, device_info=%s where IP=%s"
self.mysql.update(sql, (update_time, area_id, IP_location,
state, detection_times, os, device_type, device_info, host))
def update_port_info(self, all_port, host):
port_info = str(all_port)
sql = '''update tbl_asset set port_info="%s" where IP="%s"''' % (
port_info, host)
self.mysql.update(sql)
def find_area_id(self, ip):
try:
sql = 'select area_id,IP_location from sys_ip_location where inet_aton(IP_start) <= inet_aton(%s) AND inet_aton(IP_end)>=inet_aton(%s);'
result = self.mysql.getOne(sql, (ip, ip))
area_id = result['area_id']
ip_location = result['IP_location']
return area_id, ip_location
except Exception:
try:
country, province, city, operator = location.find_location(ip)
if country and province:
sql2 = 'select code from sys_administrative_area where name=%s or name=%s'
result = self.mysql.getOne(sql2, (city, province))
code = result['code']
ip_location2 = province + city
sql3 = "insert into sys_ip_location (IP_start,IP_end,IP_loc_county,IP_loc_province,IP_loc_city,IP_loc_district,operator,area_id,IP_location,aton_start,aton_end) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,inet_aton(%s),inet_aton(%s))"
self.mysql.insertOne(
sql3, (ip, ip, country, province, city, '', operator, code, ip_location2, ip, ip))
return code, ip_location2
except Exception:
return '', ''
def find_asset_id(self, ip):
sql = 'select id,area_id from tbl_asset where IP=%s'
result = self.mysql.getOne(sql, (ip))
asset_id = result['id']
area_id = result['area_id']
return asset_id, area_id
def find_protocol(self, port):
sql = 'select protocol from sys_port where port=%s'
result = self.mysql.getOne(sql, (port))
protocol = result['protocol']
return protocol
def find_cate(self, cate_id):
sql1 = 'select parent_id from sys_rule_cate where id=%s'
sql2 = 'select cate_name from sys_rule_cate where id=%s'
result1 = self.mysql.getOne(sql1, (cate_id))
parent_id = result1['parent_id']
result2 = self.mysql.getOne(sql2, (parent_id))
cate_name = result2['cate_name']
return cate_name
def find_banner(self, content, title='', header=''):
sql = "select id,rule_content,original_info,cate_id,cate_name from sys_rule"
info = self.mysql.getAll(sql)
id_list = []
banner_list = []
for msg in info:
_id = str(msg['id'])
rule_content = msg['rule_content']
original_info = msg['original_info']
cate_id = msg['cate_id']
cate_name = msg['cate_name']
rule = str(original_info).lower()
if title:
if rule in title.lower():
parent_cate_name = self.find_cate(cate_id)
id_list.append(_id)
banner_list.append(rule_content)
banner_list.append(cate_name)
banner_list.append(parent_cate_name)
if content:
if rule in content.lower():
parent_cate_name = self.find_cate(cate_id)
id_list.append(_id)
banner_list.append(rule_content)
banner_list.append(cate_name)
banner_list.append(parent_cate_name)
if header:
if rule in str(header).lower():
parent_cate_name = self.find_cate(cate_id)
id_list.append(_id)
banner_list.append(rule_content)
banner_list.append(cate_name)
banner_list.append(parent_cate_name)
try:
return ','.join(list(set(banner_list))), ','.join(list(set(id_list)))
except Exception:
return '', ''
def find_manufacturer(self, content):
if content:
sql = 'select simple_name,keyword from sys_manufacturer'
info = self.mysql.getAll(sql)
for msg in info:
simple_name = msg['simple_name']
keyword = msg['keyword']
key_list = []
if ',' in keyword:
key_list = keyword.split(',')
else:
key_list.append(keyword)
for key in key_list:
if key.lower() in content.lower():
return simple_name
return ''
else:
return ''
def history(self, ip, dict1, dict2):
add_time = time.strftime('%Y-%m-%d %H:%M:%S')
if not dict1:
dict1 = '{}'
dict1 = eval(dict1)
if dict1 != dict2:
if len(dict1) > len(dict2):
for i in dict1:
if i in dict2:
pass
else:
sql1 = "update tbl_asset_info set history=%s where IP=%s and port=%s"
self.mysql.update(sql1, (1, ip, i))
else:
for j in dict2:
if j in dict1:
pass
else:
asset_id, area_id = self.find_asset_id(ip)
protocol = dict2[j]
sql2 = "insert into tbl_asset_info (IP,port,protocol,add_time,asset_id,area_id) values(%s,%s,%s,%s,%s,%s)"
self.mysql.insertOne(
sql2, (ip, j, protocol, add_time, asset_id, area_id))
def telnet(self, ip, port):
cmd = 'sudo /usr/local/bin/python /var/www/html/ngscanner/scan/send.py ' + ip + ' ' + port
output = os.popen(cmd)
stdoutput = output.read()
if stdoutput:
return stdoutput.strip()
else:
return ''
def send_http(self, host, port):
protocol = self.find_protocol(port)
if protocol == 'https':
try:
res = requests.get('https://' + host + ':' + str(port),
timeout=5, verify=False)
return res
except Exception:
return
else:
try:
res = requests.get('http://' + host + ':' + str(port), timeout=5)
return res
except Exception:
return
def re_title(self, content):
content = content.lower()
h = re.search(r'<title>[\s\S]*?</title>', content)
if h:
title = h.group()
if title:
return title.replace('<title>', '').replace('</title>', '')
def mod_headers(self, res):
url = res.url
scheme = 'HTTP'
if url.startswith('https:'):
scheme = 'HTTPS'
code = res.status_code
reason = res.reason
h = res.headers
he = dict(h)
data = []
data.append(scheme + '/' + str(code) + ' ' + reason + '\n')
for i in h:
data.append(i + ':' + h[i] + '\n')
header = ''.join(data).strip()
server = ''
if 'Server' in he:
server = he['Server']
return header, server
def mod_protocol(self, port, protocol):
sql1 = 'select protocol from sys_port where port=%s and data_source IN (3,1) order by id desc'
sql2 = 'select protocol from sys_port where port=%s and data_source=%s'
sql3 = "insert into sys_port (port,protocol,data_source,cate,add_time) values(%s,%s,%s,%s,%s)"
result = self.mysql.getOne(sql1, (port))
db_protocol1 = result['protocol']
if db_protocol1:
return db_protocol1
else:
result2 = self.mysql.getOne(sql2, (port, 2))
db_protocol2 = result2['protocol']
if db_protocol2:
return db_protocol2
else:
if protocol.lower() != 'unknown':
add_time = time.strftime('%Y-%m-%d %H:%M:%S')
self.mysql.insertOne(sql3, (port, protocol, 2, 0, add_time))
return protocol
def decode(self, req):
encoding = req.encoding
if encoding == 'ISO-8859-1':
encodings = requests.utils.get_encodings_from_content(req.text)
if encodings:
encoding = encodings[0]
else:
encoding = req.apparent_encoding
encode_content = req.content.decode(
encoding, 'replace').encode('utf-8', 'replace')
return encode_content
def scan(self, data_one, content_type, vul_plug_id, t_id):
host = data_one['IP']
items = data_one['detection_times']
port_info = data_one['port_info']
OS = ''
all_port = {}
device_type = ''
device_info = ''
try:
all_port, OS, device_type, device_info = self.nmap_scan(host)
except Exception:
self.logs()
self.update_asset(host, OS, items, all_port, device_type, device_info)
if all_port:
self.history(host, port_info, all_port)
self.update_port_info(all_port, host)
for port, protocol in all_port.items():
if port == '19':
continue
banner = ''
print '----->', port
add_time = time.strftime('%Y-%m-%d %H:%M:%S')
protocol = self.mod_protocol(port, protocol)
asset_id, area_id = self.find_asset_id(host)
res = self.send_http(host, port)
content = None
try:
content = self.decode(res)
except Exception:
pass
if content:
print 'http'
title = self.re_title(content)
header, server = self.mod_headers(res)
banner, rule_id = self.find_banner(content, title, header)
manufacturer = self.find_manufacturer(content)
sql_http = "update tbl_asset_info set protocol=%s,asset_id=%s,title=%s,header=%s,html_page=%s,banner=%s,rule_id=%s,server=%s,manufacturer=%s where IP=%s and port=%s"
self.mysql.update(sql_http, (protocol, asset_id, title, header, content, banner, rule_id, server, manufacturer, host, port))
else:
print 'telnet'
content = self.telnet(host, port)
if content:
banner, rule_id = self.find_banner(content)
manufacturer = self.find_manufacturer(content)
sql_telnet = "update tbl_asset_info set protocol=%s,asset_id=%s,html_page=%s,banner=%s,rule_id=%s,manufacturer=%s where IP=%s and port=%s"
self.mysql.update(sql_telnet, (protocol, asset_id, content, banner, rule_id, manufacturer, host, port))
sql_time = "update tbl_asset_info set update_time=%s where IP=%s and port=%s"
self.mysql.update(sql_time, (add_time, host, port))
if content_type == 0:
ScanMaster().door(t_id, vul_plug_id, host, int(port), protocol, banner, content)
else:
print 'No open ports!'
def nmap_scan(self, ip):
cmd = 'nmap -O %s' % (ip)
stdoutput = None
try:
output = os.popen(cmd)
stdoutput = output.read()
except Exception:
pass
if stdoutput and 'Nmap scan report for' in stdoutput:
trim = stdoutput.split('\n')
device_type = ''
device_info = ''
all_port = {}
OS = ''
for i in trim:
line = i.lower()
if 'open' in line and 'warning' not in line and '%' not in line and 'filtered' not in line and 'running' not in line and 'cpe:' not in line and 'nmap scan report' not in line:
one = i.split(' ')
data = []
for j in one:
if j:
data.append(j)
port = data[0].split('/')[0]
protocol = data[-1]
if port.isdigit():
all_port[port] = protocol
if 'Aggressive OS guesses' in stdoutput:
if 'Aggressive OS guesses' in i:
OS = self.get_os(i).strip()
elif 'Running' in i:
if ',' in i:
i = i.split(',')[0]
OS = i.split(':')[-1].strip()
if 'Device type' in i:
device = i.split(':')[-1]
if '|' in device:
device_list = device.split('|')
device_type = ','.join(device_list).strip()
device_info = device_list[0].strip()
else:
device_type = device_info = device.strip()
return all_port, OS, device_type, device_info
else:
return {}, '', '', ''
def get_os(self, i):
b = i.split('),')
for i in b:
if ',' in i or 'or' in i:
pass
else:
if '(' in i:
os = i.split('(')[0].strip()
if 'Aggressive OS guesses' in os:
os = os.split(':')[-1]
return os
# coding:utf-8
'''''
@author: baocheng
'''
from MySqlConn import Mysql
from _sqlite3 import Row
# 申请资源
mysql = Mysql()
sqlAll = "SELECT tb.uid as uid, group_concat(tb.goodsname) as goodsname FROM ( SELECT goods.uid AS uid, IF ( ISNULL(goodsrelation.goodsname), goods.goodsID, goodsrelation.goodsname ) AS goodsname FROM goods LEFT JOIN goodsrelation ON goods.goodsID = goodsrelation.goodsId ) tb GROUP BY tb.uid"
result = mysql.getAll(sqlAll)
if result:
print "get all"
for row in result:
print "%s\t%s" % (row["uid"], row["goodsname"])
sqlAll = "SELECT tb.uid as uid, group_concat(tb.goodsname) as goodsname FROM ( SELECT goods.uid AS uid, IF ( ISNULL(goodsrelation.goodsname), goods.goodsID, goodsrelation.goodsname ) AS goodsname FROM goods LEFT JOIN goodsrelation ON goods.goodsID = goodsrelation.goodsId ) tb GROUP BY tb.uid"
result = mysql.getMany(sqlAll, 2)
if result:
print "get many"
for row in result:
print "%s\t%s" % (row["uid"], row["goodsname"])
result = mysql.getOne(sqlAll)
print "get one"
print "%s\t%s" % (result["uid"], result["goodsname"])
# 释放资源
mysql.dispose()
#!/usr/bin/python
# -*- coding:utf8 -*-
# @Author : tuolifeng
# @Time : 2017-10-17 13:28:40
# @File : test_conn.py
# @Software : PyCharm
# 测试对MySQL的访问
from MySqlConn import Mysql
# 申请资源
mysql = Mysql()
sqlAll = "select * from gz_lianjia_xiaoqu_all"
result = mysql.getAll(sqlAll)
if result:
print "get all"
for row in result:
print "%s\t%s\t%s\t%s\t%s" % (row["area_name"], row["price"],
row["longtitude"], row["latitude"],
row["detail_url"])
#sqlAll = "select * from gz_lianjia_xiaoqu_all"
#result = mysql.getMany(sqlAll, 2)
#if result:
# print "get many"
# for row in result:
# print "%s\t%s" % (row["area_name"], row["price"])
#result = mysql.getOne(sqlAll)
#print "get one"
#print "%s\t%s" % (result["area_name"], result["price"])
def dealPreR():
# 申请资源
mysql = Mysql()
sql = "delete FROM pre_r"
result = mysql.delete(sql)
#释放资源
mysql.dispose()
a = np.loadtxt('test_pre_r.csv', delimiter=',')
a = np.delete(a, 0, axis=0)
for x in a:
mysql = Mysql()
tmpSql = "INSERT INTO pre_r (old_index, bsum, lat, lng, bank_index, region_index, pre_r) VALUES (" + str(
x[0]) + ',' + str(x[1]) + ',' + str(x[2]) + ',' + str(
x[3]) + ',' + str(x[5]) + ',' + str(x[6]) + ',' + str(
x[7]) + ')'
result = mysql.update(tmpSql)
tmp = getNewLatLng(x[2], x[3], x[7])
netSql = 'SELECT COUNT(name) AS data from bankdata_copy WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
r1 = mysql.getOne(netSql)
print(r1)
firmSql = 'SELECT person_num from firm_info WHERE flat >' + str(
tmp['minLat']) + ' AND flat <' + str(
tmp['maxLat']) + ' AND flng >' + str(
tmp['minLng']) + ' AND flng <' + str(tmp['maxLng'])
r2 = mysql.getAll(firmSql)
person_num = 0
for k in r2:
person_num += k['person_num']
print(person_num)
xqSql = 'SELECT total_house from fdd_xq WHERE lat >' + str(
tmp['minLat']) + ' AND lat <' + str(
tmp['maxLat']) + ' AND lng >' + str(
tmp['minLng']) + ' AND lng <' + str(tmp['maxLng'])
r3 = mysql.getAll(xqSql)
total_house = 0
for k in r3:
total_house += k['total_house']
print(total_house)
sql_r = 'UPDATE pre_r SET node_in_prer=' + str(
r1['data']) + ',firm_in_prer=' + str(
person_num) + ',house_in_prer=' + str(
total_house) + ' WHERE old_index=' + str(x[0])
r4 = mysql.update(sql_r)
mysql.dispose()
prebsum()
return template('test:{{test}}', test="正在处理")