python使用ip2Region库离线查询ip地址归属地,生成Excel报告

参考:

python使用ip2Region库查询ip地址归属地查询/ip朔源自动生成Excel报告

https://www.pythonheidong.com/blog/article/895015/58b65cf3aeda4f51d1f5/

xlsxwriter简单用法

https://blog.csdn.net/lb245557472/article/details/102955908/

ip2Region库

https://gitee.com/lionsoul/ip2region

xlsxwriter库替换了xlsx库，经过测试可写入了100万+行，无限制。xlsx最大行数限制65535

ip2region.db替换为ip2Region升级后的ip2region.xdb

#-*- coding:utf-8 -*-

import struct, sys, os, time
from platform import python_version
from xdbSearcher import XdbSearcher
import xlsxwriter

def testSearch(ip_s):
    dbFile = "../../data/ip2region.xdb"
    searcher = XdbSearcher(dbfile=dbFile)
    try:
        #print("开始检测：",(ip_s))
        #sTime = time.time() * 1000
        data = searcher.searchByIPStr(ip_s)
        #print("return-data：" + f"{data}")
        # elif algorithm == "memory":
        #     data = searcher.memorySearch(line)
        # else:
        #     data = searcher.btreeSearch(line)
        #eTime = time.time() * 1000
        #ip_info = ("%s|%s" % (ip_s, data))
        #print("检测完成：return" + ip_info)
        return data

    except Exception as e:
        print("[Error]: %s" % e)
    searcher.close()
    

def all_in():
    #row_id = 1
    #book = xlwt.Workbook()
    workbook = xlsxwriter.Workbook('ip.xlsx')
    
    #sheet = book.add_sheet('sheet')
    worksheet = workbook.add_worksheet()
    
    row = 1
    
    title = ['源ip', '国家', '省份','城市','运营商']
    # 定义一个红色+黑体的格式.
    #bold = workbook.add_format({'bold': 1, "color": "red"})
    #worksheet.write('A1', '源ip', bold)
    #worksheet.write('B1', '国家', bold)
    #worksheet.write('C1', '省份', bold)
    #worksheet.write('C1', '城市', bold)
    #worksheet.write('C1', '运营商', bold)

    for col in range(len(title)):
        worksheet.write(0, col, title[col])
    with open('ip.txt' ,'r') as file:
        for line in file.readlines():
            ip = line.strip()
            try:
                data = testSearch(ip)
                #print("循环内"+f"{data}")
                ct = data.split('|')[0].strip()
                pv = data.split('|')[2].strip()
                city = data.split('|')[3].strip()
                yys = data.split('|')[4].strip()
                if ct == "0":
                    #print("地址库中未找到对应的IP归属地，请更新地址库或者确定ip准确性！")
                    worksheet.write(row, 0, ip)
                    worksheet.write(row, 1, "/")
                    worksheet.write(row, 2, "/")
                    worksheet.write(row, 3, "/")
                    worksheet.write(row, 4, "/")
                    row += 1
                else:
                    worksheet.write(row, 0, ip)
                    worksheet.write(row, 1, ct)
                    worksheet.write(row, 2, pv)
                    worksheet.write(row, 3, city)
                    worksheet.write(row, 4, yys)
                    row += 1
            except Exception as e:
                print("[Error]: %s" % e)
                worksheet.write(row, 0, ip)
                worksheet.write(row, 1, "查询异常")
                row += 1
    #book.save('score.xls')
    workbook.close()


if __name__ == "__main__":
    all_in()

25w条ip记录查询时间为44s

输出格式为