'''
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 )
下面是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.