网工写python—爬IP地址库

一、选取一个有开放API的IP地址库

经比较，选择ip-api这个库（免费，一天内不限制次数），备选IPwois

def getIsp(ip):
    try:
        proxies = { "http": "http://user:passwors@proxy.xxxx.com:8080/"}
        res = requests.get('http://ip-api.com/json/' + ip, headers={"Connection": "close"},timeout=(1000,2000),proxies = proxies).content
        #res = requests.get('http://ip-api.com/json/' + ip, headers={"Connection": "close"}, timeout=(100, 200)).content
        print(ip)
        data = json.loads(res)
        if data['status'] == 'success':
            return data['country'], data['isp'], data['as']
        else:
            '''data = IPWhois(ip).lookup_whois()
            asn2 = data['asn']
            country2 = pycountry.countries.get(alpha_2=data['asn_country_code']).name
            isp2 = data['asn_description']'''
            return 'fail', 'fail', 'fail'
    except Exception as e:
        print("getISP出现异常："+str(e))
        ''' data = IPWhois(ip).lookup_whois()
         asn2 = data['asn']
         country2 = pycountry.countries.get(alpha_2=data['asn_country_code']).name
         isp2 = data['asn_description']'''
        time.sleep(60)
        return 'fail', 'fail','fail'

遇到的问题：IP-API限制一分钟内只能查询大约50个IP，超过后获取不到数据，故在出现异常后，通过time.sleep(60)实现1min的缓冲时间（造成时间浪费，穷没得办法）

二、将查询到的IP信息存入数据库

def update_ISP (number):
    try:
        conn = pool.connection()
        cur = conn.cursor()
        for i in range(50000):
            rows_count,subnet = inquery_prefix(number)
            if rows_count > 0:
                subnet = ''.join(subnet)
                country1,isp1,asn1 = getIsp(subnet)
                country = str(country1)
                isp = str(isp1)
                val = (country1,isp1,number)
                sql = "update routes set `country`=%s,`isp`=%s where number = %s"
                print("update routes set `country`=%s"%country+ ",`isp`=%s"%isp+" where number = %d"%int(number))
                cur.execute(sql,val)
                number = str(int(number) + 1)
                conn.commit()
            else:
                number = str(int(number) + 1)
        return number
    except Exception as e:
        print('update_ISP出现异常：' +str(e))

遇到的问题：性能还是性能，一开始的update语句中的条件是where prefix=，导致存数据库很慢，后来改为主键条件where number=，存储变快了许多

三、其他

1、本来想爬取全球80万条路由条目的信息，并通过多线程并发爬取，结果发现python的多线程太假，并不能提高效率

2、受限于API分钟内有次数限制，频繁查询获取不到数据，导致2min内只能存50条路由条目，80万条路由条目要爬几十天，有点坑坑，计划持续优化（代码能力渣渣的网工的泪）