更新现有数据库中的物流信息

# -*- coding:utf-8 -*- 2 3 from StringIO import StringIO 4 import urllib2 5 import json 6 import gzip 7 from multiprocessing import Pool 8 import MySQLdb 9 import sys 10 11 def get_tracking_info(eachnum): 12 # Get tracking info in json format 13 url=‘http://www.17track.net/r/handlertrack.ashx?callback=jQuery1110042733539966866374_1444210681878&num=‘+eachnum+‘&pt=0&cm=0&cc=0&_=1444210681879‘ 14 15 request=urllib2.Request(url) 16 request.add_header(‘Accpect-encoding‘, ‘gzip‘) 17 request.add_header(‘User-Agent‘,"Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/42.0.2311.152 Safari/537.36") 18 response=urllib2.urlopen(request) 19 trd_info=response.read() 20 21 if response.info().get(‘Contect-encoding‘)==‘gzip‘: 22 buf=StringIO(response.read()) 23 f=gzip.GzipFile(fileobj=buf) 24 trd_info=f.read() 25 26 trd_info=trd_info[43:-1] 27 json_info=json.loads(trd_info) 28 29 return json_info 30 31 def get_shippinginfo_into_db(json_info): 32 # Sort shipping info and status into variables 33 tracking_code=json_info[‘dat‘][‘a‘] 34 fullfill_days=json_info[‘dat‘][‘f‘] 35 36 record_out=len(json_info[‘dat‘][‘z2‘]) 37 record_in=len(json_info[‘dat‘][‘z1‘]) 38 39 ship_type=‘Registered‘ 40 41 if record_in!=0: 42 has_update_in=True 43 else: 44 has_update_in=False 45 46 if record_out!=0: 47 has_update_in=True 48 else: 49 has_update_in=False 50 51 52 has_update_in=‘ ‘ 53 has_update_in=‘ ‘ 54 has_update_in=json_info[‘dat‘][‘e‘] 55 if has_update_in==0: 56 has_update_in=‘Not Found‘ 57 has_update_in=False 58 elif has_update_in==10: 59 has_update_in=‘Under Shipping‘ 60 has_update_in=False 61 elif has_update_in==20: 62 has_update_in=‘Warn: Expired‘ 63 has_update_in=True 64 elif has_update_in==30: 65 has_update_in=‘Pick Up‘ 66 has_update_in=True 67 elif has_update_in==40: 68 has_update_in=‘Delivered‘ 69 has_update_in=True 70 elif has_update_in==50: 71 has_update_in=‘Alert: Returned‘ 72 has_update_in=True 73 74 75 # Check the Origin Shipping info whether updated online. 76 info_in=[] 77 last_info_in=‘‘ 78 if record_in>0: 79 m=0 80 81 last_info_in=json_info[‘dat‘][‘z1‘][m][‘a‘]+" "+json_info[‘dat‘][‘z1‘][m][‘c‘]+","+json_info[‘dat‘][‘z1‘][m][‘z‘] 82 # Newest Tracking Info from Origin 83 84 # Get Full Info from Origin 85 for i in range(record_in): 86 info_in.append(json_info[‘dat‘][‘z1‘][m][‘a‘]+" "+json_info[‘dat‘][‘z1‘][m][‘c‘]+","+json_info[‘dat‘][‘z1‘][m][‘z‘]) 87 m=m+1 88 else: 89 print "Package haven‘t update online!" 90 91 info_out=[] 92 last_info_out=‘‘ 93 if record_out>0: 94 n=0 95 96 last_info_out=json_info[‘dat‘][‘z2‘][n][‘a‘]+" "+json_info[‘dat‘][‘z2‘][n][‘c‘]+","+json_info[‘dat‘][‘z2‘][n][‘d‘]+","+json_info[‘dat‘][‘z2‘][n][‘z‘] 97 # Newest Tracking Info from Destination 98 99 # Get Full Tracking Info from Destination 100 for i in range(record_out): 101 info_out.append(json_info[‘dat‘][‘z2‘][n][‘a‘]+" "+json_info[‘dat‘][‘z2‘][n][‘c‘]+","+json_info[‘dat‘][‘z2‘][n][‘d‘]+","+json_info[‘dat‘][‘z2‘][n][‘z‘]) 102 n=n+1 103 else: 104 print "Package haven‘t update from Destination Country!" 105 106 info_in=‘|‘.join(info_in) 107 info_out=‘|‘.join(info_out) 108 109 conn=MySQLdb.connect(host=‘192.168.1.100‘, port=3306, user=‘root‘, passwd=‘root‘, db=‘shipping‘,charset=‘utf8‘) 110 cur=conn.cursor() 111 112 sqli=‘‘‘insert into shipping.ship_track_info 113 (tracking_code,ship_type,record_out,last_info_out,has_update_in,record_in,last_info_in,has_update_in,has_update_in,
info_out,info_in,has_update_in,fullfill_days) 114 values("%s","%s","%s","%s",%s,"%s","%s",%s,"%s","%s","%s",%s,"%s") 115 ON DUPLICATE KEY UPDATE record_out=VALUES(record_out), last_info_out=VALUES(last_info_out), 116 has_update_in=VALUES(has_update_in), record_in=VALUES(record_in), last_info_in=VALUES(last_info_in), 117 has_update_in=VALUES(has_update_in), has_update_in=VALUES(has_update_in), has_update_in=VALUES(has_update_in), 118 info_out=VALUES(info_out), info_in=VALUES(info_in), has_update_in=VALUES(has_update_in), fullfill_days=VALUES(fullfill_days)‘‘‘ / 119 %(tracking_code,ship_type,record_out,last_info_out,has_update_in,record_in,last_info_in,has_update_in,has_update_in,info_out, /
info_in,has_update_in,fullfill_days) 120 121 cur.execute(sqli) 122 conn.commit() 123 124 cur.close() 125 conn.close() 126 127 print ‘DB update done!‘ 128 129 def fetch_tracking_number(): 130 conn=MySQLdb.connect(host=‘192.168.1.103‘, port=3306, user=‘root‘, passwd=‘root‘, db=‘shipping‘,charset=‘utf8‘) 131 cur=conn.cursor() 132 133 count=cur.execute(‘select tracking_code,record_in from shipping.ship_track_info‘) 134 # fetchall tracking number in DB 135 trNo_wait_check=cur.fetchall() 136 count=int(count) 137 138 cur.close() 139 conn.close() 140 141 trackingnum=[] 142 for eachnum in range(count): 143 TrNo.append(str(trNo_wait_check[eachnum][0])) 144 145 return trackingnum 146 147 def main(eachnum): 148 json_info=get_tracking_info(eachnum) 149 get_shippinginfo_into_db(json_info) 150 151 if __name__ == ‘__main__‘: 152 trackingnum=fetch_tracking_number() 153 # srart multiprocessing and run 154 p=Pool(6) 155 for eachnum in trackingnum: 156 p.apply_async(main,args=(eachnum,)) 157 158 p.close() 159 p.join()

这里面主要练习了用Python解析json格式的数据， 同时学习了使用MySQLdb，Python对数据库的基本操作，并使用多进程提高整体运行速度。

 

更新现有数据库中的物流信息

标签：