python 批量查询ip地址并写入数据库

这篇文章主要为你解决下列问题

• 需要批量查询大量ip
• 需要将ip地址查询自动写入数据库

修改填写数据库信息 同时需要先将ip地址信息先写入数据库

#!/usr/local/bin/python
#coding: utf-8

import json,pymysql,time
from urllib import request

def get_conn():
    conn = pymysql.connect(host='xxx', port=3306, user='xxx', passwd='xxx', db='crm', charset='utf8')
    return conn

def query_all(cur, sql, args):
    cur.execute(sql, args)
    return cur.fetchall()

def ip_region():
    conn = get_conn()
    cur = conn.cursor()
    k=j=0   # k记录获得502网关错误，j记录轮寻次数
    sql_ip = 'select ip from origin where country is NULL;'
    while query_all(cur=cur,sql=sql_ip,args=None):
        results=query_all(cur=cur,sql=sql_ip,args=None)
        for i in results:
            res = url(i[0])   #获取指定ip的归属地函数
            if res:
                sql_add = "update origin set country='"+res[0]+"',region='"+res[1]+"',city='"+res[2]+"',isp='"+res[3]+"' where ip='"+i[0]+"';"
                # print(sql_add)
                cur.execute(sql_add)
                conn.commit()
            else:
                k=k+1
                print('====>get 502:',k)
            time.sleep(0.5)
        j=j+1
        print('========>second round:',j)
    cur.close()
    conn.close()
    return k,j
def url(ip):
    url = "http://ip.taobao.com/service/getIpInfo.php?ip="+ ip   #淘宝查ip归属地请求api
    try:
        jsondata = json.loads(request.urlopen(url).read())
        if jsondata['code'] == 1:
            jsondata['data'] = {'region': '', 'city': '', 'isp': ''}
        print(jsondata['data']['country'], jsondata['data']['region'], jsondata['data']['city'],
              jsondata['data']['isp'])
        return (
        jsondata['data']['country'], jsondata['data']['region'], jsondata['data']['city'], jsondata['data']['isp'])
    except:
        return False
if __name__ == "__main__":
    start_time = time.asctime(time.localtime())
    result = ip_region()
    print('get 502 counts:',result[0])
    print('round counts:',result[1])
    print('start time:',start_time)
    print('end time:',time.asctime(time.localtime()))