Python read text data write to database and query optimization

Text data format

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/78/3A/wKiom1Z4LlnTJ2BsAABumSdrKSw965.png "title=" capture. PNG "alt=" Wkiom1z4llntj2bsaabumsdrksw965.png "/>

Ip2int functions for converting IP addresses to integers

The INT2IP function is used to convert integers to IP addresses

The Insert_row function is used to insert database records

From __future__ import print_functionimport torndbdef get_mysql_conn ():     return torndb. Connection (        host=mysql["host"] +  ":"  +  mysql["Port"],        database=mysql["Database"],         user=mysql["User"],        password= mysql["password"],        charset= "UTF8") mysql = {          "Host":  "127.0.0.1",          "Port":  "3306",         "database":  "test",          "Password":  "",         "user" :  "root",         "charset":  "UTF8"     } Def ip2int (IP):     try:        hexn =   ". Join (["%02x " % long (i)  for i in ip.split ('. ')])     except exception, e:        hexn  =  ". Join (["%02x " % long (i)  for i in  ' 0.0.0.0 '. Split ('. ')])     return long (hexn, 16) Def int2ip (n):     d =  256 * 256 * 256    q = []    while  d > 0:        m,n = divmod (n,d)          q.append (str (m))         d  = d/256    return  '. Join (q) def insert_row ():     with open ("./ipdata.csv",  ' R ') &NBSP;AS&NBSP;FR: &nBsp;       lines = fr.readlines ()     nl_p_ list = []    for l in lines:         ls = l.strip (). Split (', ',  4) &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;C1,  c2, c3, c4, c5 = ls[0], ip2int (Ls[1]),  ip2int (Ls[2]),  ls[3], &NBSP;LS[4]&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;NL&NBSP;=&NBSP;[C2,&NBSP;C3,&NBSP;C4,&NBSP;C5]         nl_p_list.append (NL)     db = get _mysql_conn ()     db.execute ("Start transaction")     for i  in range (Len (nl_p_list)/1000 + 1):         tmp_nl_ p_list = nl_p_list[i*1000:  (i+1) *1000]        ret  = db.insertmany (' INSERT&Nbsp;into ipdata  (Startip, endip, country, carrier)  values  (%s, %s,  %s, %s) ',  tmp_nl_p_list)     db.execute ("COMMIT") if __name__ ==   ' __main__ ':     insert_row ()     # print (Ip2int (' 106.39.222.36 ')     with open ("./ipdata.csv",  ' R ')  as fr:         lines = fr.readlines ()     nl_p_list =  []    for l in lines:         Ls = l.strip (). Split (', ',  4)         c1, c2,  C3, c4, c5 = ls[0], ip2int (Ls[1]),  ip2int (ls[2]), ls[3], ls[4]         nl = [c2, c3, c4, c5]         nl_p_lisT.append (NL)     import random    import time     ip_list = map (Lambda x: x[1], random.sample (nl_p_list, 100))      db = get_mysql_conn ()     ret_list = []     #{0} table name     sql_tmp =  ' select {0}.* from  (select *  FROM  ' Test ' .ipdata where %s>=startip order by startip desc  limit 1)  {0} '     sql_list = []     #拼接一个很长的sql     for i in range (Len (ip_list)):         sql_list.append (Sql_tmp.format (' t '  + str (i)  % ip_list[i])      sql =  '  union all  ' join (sql_list)     t0 = time.time ()     #&nbSp;for row in db.query (SQL):     #     print (Row)     dict (Zip (ip_list, db.query (SQL)))     t1 = time.time ()     for ip in ip_list:        ret  = db.get (' select * from  ' test '. Ipdata where %s>=startip order  by startip desc limit 1 ',  ip)          Startip, endip = ret.get (' StartIP '),  ret.get (' EndIP ')          if startip <= ip <= endip:             ret_list.append ((Ip, ret.get (' Country ')))          else:            ret_ List.append ((ip, u "Unknown"))    t2 = time.time ()     print (t1-t0)      Print (T2-T1)

Format output string function format ()

Parameters using the string are represented by {NUM} ,0, representing the first parameter ,1, representing the second parameter , and then sequentially sliding scale;

The zip () function accepts any number of sequences (including 0 and 1) as parameters, returning a tuple list

The dict () function creates a new dictionary from an object that can be iterated. For example, a tuple of a list

Reference article:

Python standard library: Built-in function dict

Http://www.2cto.com/kf/201411/354739.html

Ways to optimize:

1000 times times more efficient field and index

Use union ALL for a single query to isolate

This article is from the "bit accumulation" blog, please be sure to keep this source http://tianxingzhe.blog.51cto.com/3390077/1727003

Python read text data write to database and query optimization