def change_cdma_addr():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('SELECT * from global_gsm where city like "%省" ')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
address =var['area'].replace(' ', '')
province,city,district = place_cut(address)
update_sql = '''
UPDATE global_gsm SET province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''" WHERE cid = "'''+str(var["cid"])+'''" and lac = "'''+str(var["lac"])+'''" and mcc = "'''+str(var["mcc"])+'''" and mnc = "'''+str(var["mnc"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
def change_ip_addr():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('select * from global_ip_addr where province="贵省"')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
address =var['area'].replace(' ', '')
province,city,district = place_cut(address)
update_sql = '''
UPDATE global_ip_addr SET province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''" WHERE start_ip = "'''+str(var["start_ip"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
def ip_cell_get(ip):
print('use IP get cell address')
province = ""
city = ""
district = ""
area = ""
num_ip=ip2int(str(ip))
print num_ip
sql='''
SELECT area,province,city,district FROM global_ip_addr WHERE start_num<="''' + str(num_ip)+'''" AND end_num>="''' + str(num_ip)+'''"
'''
search_succ,search_result = OperateSQL("bs_rom_data_server").query_get(sql)
if not search_succ:
print('ip search err')
return "","","","",""
print(search_result)
if search_result:
print search_result
country = '中国'
area = search_result[0]["area"].replace(' ','')
province,city,district = place_cut(area)
# province = search_result[0]["province"]
# city = search_result[0]["city"]
# district = search_result[0]["district"]
if area == "局域网":
province = "上海市"
city = "上海市"
district = "浦东新区"
if city is None:
city=""
if province is None:
province = ""
if district is None:
district = ""
print(area,province,city,district)
return country,area,province,city,district
def change_address():
get_succ, get_result = OperateSQL("bs_rom_data_server").query_get(
'select * from first_active where province="宁夏" limit 100')
if not get_succ:
print("err")
print(get_result)
#打印表中的多少数据
for var in get_result:
province = ""
city = ""
district = ""
address = var.get('area', '')
print address
province, city, district = place_cut(str(address))
out = '%s|%s|%s' % (province.decode('utf8'), city.decode('utf8'),
district.decode('utf8'))
print out
# print province,city,district
update_sql = '''
UPDATE first_active SET location_state=1,province="''' + province + '''" , city="''' + city + '''" , district="''' + district + '''" WHERE id = "''' + str(
var["id"]) + '''"
'''
update_succ, update_result = OperateSQL(
"bs_rom_data_server").query_update(update_sql)
def handle_first_data_controller():
get_succ,get_result=OperateSQL("bs_rom_data_server").query_get('select * from handle_first_active where city not like "%市" and LENGTH(city)>=4 and LENGTH(city)<=9 and city not like "%州" ')
if not get_succ:
print("err")
# print get_result
#打印表中的多少数据
for var in get_result:
get_succ = False
is_gsm = True
mcc = None
mnc = None
cid = None
lac = None
imsi = None
result = None
province = None
city = None
district = None
area = None
mcc = var['mcc']
cid = var['cid']
mnc = var['mnc']
lac = var['lac']
if cid=='-1':
is_gsm = False
if len(var['cdmasid'])>0:
mnc = var['cdmasid']
lac = var['cdmanid']
cid = var['cdmabid']
sql = 'select * from global_cdma where sid='+mnc+' and nid='+lac+' and bid='+cid+' '
print(sql)
get_succ,result=OperateSQL("bs_rom_data_server").query_get(sql)
if not get_succ:
result=None
print("select cmda err")
else:
sql = 'select * from global_gsm where mnc='+mnc+' and lac='+lac+' and cid='+cid+' and mcc='+mcc+' '
print(sql)
get_succ,result=OperateSQL("bs_rom_data_server").query_get(sql)
# print(get_succ,result)
if not get_succ:
result=None
print("select gsm err")
if result is None or len(result)==0:
if var['imsi0']!='0':
imsi=var['imsi0']
else:
imsi = var['imsi1']
gaode_rep = gaode_cell_get(mcc,mnc,lac,cid,var['imei0'],imsi,is_gsm)
print(gaode_rep)
if gaode_rep and gaode_rep['result']['type']!='0' and 'desc' in gaode_rep['result'].keys():
address = gaode_rep['result']['desc'].replace(' ', '')
_,_,district = place_cut(address)
print(address,district)
province = gaode_rep['result'].get('province','')
city = gaode_rep['result'].get('city','')
country = gaode_rep['result'].get('country','')
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+gaode_rep['result']['desc']+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if update_succ:
print(update_succ)
insert_jizhan(is_gsm,mnc,mcc,cid,lac,province,city,district,gaode_rep['result']['desc'],country)
else:
update_sql = '''
UPDATE handle_first_active SET location_state=2 WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
update_succ = False
elif var['gps'] is not None and var['gps']!='':
gps_rep=gps_cell_get(var['gps'])
if gps_rep:
address=gps_rep['regeocode']['formatted_address'].replace(' ', '')
province,city,district = place_cut(address)
country = gps_rep['regeocode']['addressComponent']['country']
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+address+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by GPS err')
else:
country,area,province,city,district=ip_cell_get(var['ip'])
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+area+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by IP err')
else:
country,area,province,city,district=ip_cell_get(var['ip'])
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+area+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)
if not update_succ:
print('update by IP err')
else:
if len(result)>0:
address = result[0]['area'].replace(' ', '')
province,city,district = place_cut(address)
print "test"
print city
# district = result[0]['district']
# province = result[0]['province']
# city = result[0]['city']
country = result[0]['country']
update_sql = '''
UPDATE handle_first_active SET location_state=1,country="'''+country+'''" ,province="'''+province+'''" , city="'''+city+'''" , district="'''+district+'''",area="'''+address+'''" WHERE id = "'''+str(var["id"])+'''"
'''
update_succ,update_result = OperateSQL("bs_rom_data_server").query_update(update_sql)