从mysql数据库获取电话号码，查询归属地并插入到数据库
#!/usr/bin/python
# -*- coding: utf-8 -*-
import json, urllib, sys, pymysql
from urllib import urlencode, urlopen
reload(sys)
sys.setdefaultencoding(\'utf8\')
#调用接口，获取结果为二维字典
def getPageCode(url, params):
params = urlencode(params)
f = urllib.urlopen(url, params)
content = f.read()
res = json.loads(content)
return res
#print(\'Error code: %s\'%res["resultcode"])
#对api接口返回数据处理。
def resTest(resdata):
if resdata["resultcode"] == "200":
#返回有效结果
return resdata["result"]
else:
#定义错误字典，resdata["resultcode"]为接口返回的Error code
Errorinfo = {\'province\':\'Error code\', \'city\':resdata["resultcode"]}
return Errorinfo
#处理字典为单元组列表
def dictDate(data):
province = data["province"]
city = data["city"]
res.append((phoneNum, province, city))
#将省和市处理为一个元素：辽宁,葫芦岛
res1,res2,res3 = res[0][0],res[0][1],res[0][2]
symbol = \',\'
resz = [(res1,res2+symbol+res3)]
#print(\'list res is : %s\'%resz)
return resz
#连接数据库
dblink = pymysql.connect(
host = "10.10.10.31",
user = "abc",
password = "123456",
database = "test",
charset = "utf8")
#查询数据
def select(db):
cursor = db.cursor()
cursor.execute("select phoneNum from test.userinfo")
#cursor.execute("select phoneNum from test.userinfo order by id;")
# 使用 fetchone() 方法获取单条数据.fetchall()获取所有行
data = cursor.fetchall()
#print data
return data
#插入数据
def install(db, data):
cursor = db.cursor()
sql = "update `test`.`userinfo` set location=%s where phoneNum = %s"
#data=(第一个%s，第二个%s)
data = (data[0][1], data[0][0])
cursor.execute(sql, data)
db.commit()
if __name__ == "__main__":
url = "http://apis.juhe.cn/mobile/get"
for phoneNum in select(dblink):
phoneNum = phoneNum[0]
params = {
"phone": phoneNum,
"key": "0ea8e44e4612fb794c29asdfde48hg",
"dtype" : "json",
}
res = []
#调用getPageCode接口，resTest处理数据
resdate = resTest((getPageCode(url, params)))
#通过函数dictDate处理为单元组列表，[(\'12676512732\', u\'\u6d52\u6c5f,\u6e19\u5dde\')]
resUlt = dictDate(resdate)
install(dblink, resUlt)
dblink.close()
