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

我还曾有这样一段代码的经历：

物流部门发出去的包裹都带有跟踪号，那么我们需要定时的查询这些包裹的投递情况，从而影响客户服务的进程。

首先将数据库中存有的跟踪号取出，依次放在物流跟踪网站查询，返回结果和数据库原有结果对比，有更新即完成数据更新。

Python版本：2.7.11， 代码记录:

  1 # -*- 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对数据库的基本操作，并使用多进程提高整体运行速度。

 

posted on 2016-04-24 09:32  NormalProgrammer  阅读(373)  评论(0)  编辑  收藏  举报

导航