QQwry到Sqlite3(续)

发布时间：2020-12-12 23:34:38 所属栏目：百科来源：网络整理

导读：下面是用于查询转成sqlite3的qqwry的python代码 '''Created on 2010-5-4@author: Ben @mail: ben.pang.china@gmail.com''' import os,sys,re import sqlite3 from converter import b2I,dbname def int2Ip(i): """IP字符串转换成IP对象""" i = int(i) return

下面是用于查询转成sqlite3的qqwry的python代码

'''
Created on 2010-5-4

@author: Ben

@mail: ben.pang.china@gmail.com
'''

import os,sys,re
import sqlite3
from converter import b2I,dbname

def int2Ip(i):
    """IP字符串转换成IP对象"""
    i = int(i)
    return IP( "{}.{}.{}.{}".format( i >> 24,i  >> 16 & 0xFF,i  >> 8 & 0xFF,i & 0xFF ) )

def ip2Int(ip):
    """IP字符串转换成Integer"""
    if ipm.match(ip):
        return b2I( reversed(list( map( int,ip.split(".") ) )) )

# local variables
ipm = re.compile( "^((2[0-4]d|25[0-5]|[01]?dd?).){3}(2[0-4]d|25[0-5]|[01]?dd?)$" )
conn = None

def init():
    global conn
    sqlite3.register_converter("ip",int2Ip)
    conn = sqlite3.connect(dbname,detect_types = sqlite3.PARSE_COLNAMES);
    
class IP:
    def __init__(self,ip):
        """IP类,存放IP地址,并验证IP字符串是否有效"""
        if not ipm.match(ip):
            raise ValueError( "invalid ip value:" + ip )
        self.ip = ip
    
    def __str__(self):
        return self.ip

if __name__ == '__main__':
    if os.path.exists(dbname) and len(sys.argv) == 2 and ipm.match(sys.argv[1]):
        init()
        cur = conn.cursor()
        cur.execute("""
        SELECT start_ip as "s [ip]",end_ip as "e [ip]",country.name,area.name
        FROM ipaddr,country,area WHERE (? BETWEEN start_ip AND end_ip) AND cid = country.id AND aid = area.id;
        """,(ip2Int(sys.argv[1]),) )
        
        for r in cur:
            print("""
            start ip: {} ~ {}
            {}
            """.format(*r))
        
        conn.close()
    else:
        print("not found ipdb for" + dbname,file = sys.stderr )

很不幸,经过尝试转成sqlite3的QQwry的压缩率并没有我想的那么高,整个库文件有31M,是QQwry原格式的3倍. 下面是SQL脚本

CREATE TABLE IF NOT EXISTS country (
    id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS area (
    id INTEGER PRIMARY KEY,name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS ipaddr (
    start_ip INTEGER PRIMARK KEY,end_ip INTEGER NOT NULL UNIQUE,cid INTEGER NOT NULL,aid INTEGER DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS ipIdx ON ipaddr (start_ip,end_ip)

因为在SQLITE3里把IP转成integer了,所以可以看到上面的代码中注册了一个convertor用于转换,如果觉得不方便还可以注册funtion viewIP,然后create view,使用注册的函数显示IP,这会增加开销,但可以满足使用习惯像这样 create view ipview as SELECT viewIP(start_ip),... FROM ipaddr....

OK.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!