PHP 用mmdb库 根据ip地址获取省市区&经纬度 GeoLite.mmdb

   public function handle()
    {

//        $filePath = storage_path('app/public/excel.xlsx');
        ini_set('memory_limit', '2048M');
        $filePath = "/Users/wangsongkai/Documents/www/xxxx/public/user-ip.xlsx";
        $import = new FilesInfoImport();
        $data = Excel::toArray($import, $filePath);
        unset($data[0][0]);
        if ($data[0] == null) {
            echo "ERROR";
            die();
        }
        $mmdbFilePath = '/Users/wangsongkai/Desktop/GeoLite2-City.mmdb';
        try {
            foreach ($data[0] as $v) {
                if (empty($v[4])) {
                    echo "ip为空";
                    echo PHP_EOL;
                    continue;
                }

                $ipAddress = $v[4];
                $reader = new Reader($mmdbFilePath);
                $record = $reader->city($ipAddress);

                $country = isset($record->country->names['zh-CN']) ? $record->country->names['zh-CN'] : ""; 
                $economize = isset($record->mostSpecificSubdivision->names['zh-CN']) ? $record->mostSpecificSubdivision->names['zh-CN'] : "";
                $city = isset($record->city->names['zh-CN']) ? $record->city->names['zh-CN'] : "";
                $distinguish = isset($record->subdivisions[1]->names['zh-CN']) ? $record->subdivisions[1]->names['zh-CN'] : "";


                // 构造写入的数据
                $exportData[] = [
                    'id' => $v[0],
                    'name' => $v[1],
                    'nickname' => $v[2],
                    'mobile' => $v[3],
                    'ip' => $v[4],
                    'country' => $country,//国家
                    'economize' => $economize,//省
                    'city' => $city,//市
                    'distinguish' => $distinguish//区
                ];
            }
            //在导出来
            $header = [
                "用户ID", "真实姓名", "名称", "手机号", "ip", "国家", "省份", "城市", "区"
            ];
            $data = [
                [
                    'name' => 'sheet',
                    'data' => [],
                    'config' => []
                ]
            ];
            $tmp = [];
            $tmp[0] = $header;
            foreach ($exportData as $v) {
                $tmp[] = [
                    $v['id'],
                    $v['name'],
                    $v['nickname'],
                    $v['mobile'],
                    $v['ip'],
                    $v['country'],
                    $v['economize'],
                    $v['city'],
                    $v['distinguish'],
                ];
            }
            $data[0]['data'] = $tmp;

            $filename = 'result-ip' . '_' . date('Y_m_d', time()) . '_' . time() . '.' . BaseExcel::XLSX;
            Excel::store(new Export($data), 'excel/' . $filename, 'local', BaseExcel::XLSX);
            echo "SUCCESS";
        } catch (\GeoIp2\Exception\AddressNotFoundException $e) {
            echo "在数据库中未找到 IP 地址。\n";
        } catch (\GeoIp2\Exception\AuthenticationException|\GeoIp2\Exception\OutOfQueriesException $e) {
            echo "错误: " . $e->getMessage() . "\n";
        } catch (\MaxMind\Db\Reader\InvalidDatabaseException $e) {
            echo $e->getMessage() . "\n";
        }

    }
    

github.com/P3TERX/GeoL…

上⬆下载

用的是老的ip库 可能会有找不到的地址 我处理为空了 导出来后在找个在线ip地址 然后两个excel 合并结束～

import time
import openpyxl
import requests

 
workbook = openpyxl.load_workbook('/Users/xxx/Desktop/null.xlsx')
sheet = workbook.active
 
country_column_index = 6 #国家
prov_column_index = 7    #省
city_column_index = 8    #市
district_column_index = 9#县（区）   
error_column_index = 10#err
 
for row_index, row in enumerate(sheet.iter_rows(values_only=True), start=1):
    if row[4] is None or row[4] == "0":
        print("暂无此 ip")
        continue
    else:
    
        url = 'https://qifu-api.baidubce.com/ip/geo/v1/district?ip='+row[4]
        response = requests.get(url)
        if response.status_code == 200:
            data = response.json()
            time.sleep(0.6)
            result = data['data']['country'] + "-" + data['data']['prov'] + "-" + data['data']['city'] + "-" + data['data']['district']
            print(result)
            # 将结果写入Excel表格
            sheet.cell(row=row_index, column=country_column_index, value=data['data']['country'])
            sheet.cell(row=row_index, column=prov_column_index, value=data['data']['prov'])
            sheet.cell(row=row_index, column=city_column_index, value=data['data']['city'])
            sheet.cell(row=row_index, column=district_column_index, value=data['data']['district'])
        
        else:
             sheet.cell(row=row_index, column=error_column_index,value="请求失败，状态码{response.status_code}")
       

# 保存工作簿
workbook.save('/Users/xxx/Desktop/query-null-ip.xlsx')
# 关闭工作簿
workbook.close()