QQwry到Sqlite3(续)

概述下面是用于查询转成sqlite3的qqwry的python代码 '''Created on 2010-5-4@author: Ben @mail: ben.pang.china@gmail.com'''import os, sys, reimport sqlite3from converter import b2I, dbnamedef int2Ip(i): 下面是用于查询转成sqlite3的qqwry的python代码

'''Created on 2010-5-4@author: Ben

@mail: ben.pang.china@gmail.com'''import os,sys,reimport sqlite3from converter import b2I,dbnamedef 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 variablesipm = re.compile( "^((2[0-4]d|25[0-5]|[01]?dd?).){3}(2[0-4]d|25[0-5]|[01]?dd?)$" )conn = Nonedef 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.ipif __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.

总结

以上是内存溢出为你收集整理的QQwry到Sqlite3(续)全部内容，希望文章能够帮你解决QQwry到Sqlite3(续)所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。