调用API接口，查询手机号码归属地（3）

从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()

调用API接口，查询手机号码归属地（1）https://www.cnblogs.com/outsrkem/p/11219743.html

调用API接口，查询手机号码归属地（2）https://www.cnblogs.com/outsrkem/p/11221003.html

调用API接口，查询手机号码归属地（3）https://www.cnblogs.com/outsrkem/p/11221604.html

调用phone库，查询手机号码归属地（4）https://www.cnblogs.com/outsrkem/p/11236509.html