代码:
#encoding=utf-8 import cx_Oracle import sys import datetime import time class CsvMaker: def __init__(self,idx,fromId,toId): idxStr="{:0>3d}".format(idx) date=datetime.datetime.today() dateStr=date.strftime('%Y%m%d_%H%M%S') self.filename="07_I{0}_F{1}_T{2}_D{3}".format(idxStr,fromId,toId,dateStr) self.fromId=fromId self.toId=toId self.lines=[] #print(self.filename) def run(self): self.fetchDatas() self.makeFile() print("文件{0}作成".format(self.filename)) def fetchDatas(self): conn=cx_Oracle.connect('luna','1234','127.0.0.1:1521/orcl') cursor=conn.cursor(); sql =' select ct.name,f.tags as line from' sql+=' (' sql+=' select e.cid,listagg(e.tg,\',\') within group (order by e.sn) as tags from' sql+=' (' sql+=' select c.cid,c.sn, decode(nvl(d.tid,0),0,\'0\',\'1\') as tg from' sql+=' (select a.sn,a.val,b.id as cid from' sql+=' (select level as sn,\'0\' as val from dual connect by level<=1000) a,' sql+=' (select id from customer where {0}<id and id<={1}) b ) c'.format(self.fromId,self.toId) sql+=' left join ' sql+=' (select * from customer_tag where {0}<cid and cid<={1}) d'.format(self.fromId,self.toId) sql+=' on c.cid= d.cid and c.sn=d.tid' sql+=' ) e' sql+=' group by e.cid' sql+=' ) f' sql+=' left join customer ct' sql+=' on f.cid=ct.id' sql+=' order by f.cid' cursor.execute(sql) rowset=cursor.fetchall(); for i in range(len(rowset)): row=rowset[i] line=row[0]+','+row[1] self.lines.append(line) cursor.close(); conn.close(); def buildLine(self,tags): list=[] for i in range(1000): list.append("0") arr=tags.split(',') for i in arr: idx=int(i) list[idx-1]="1" deli="," return deli.join(list) def makeFile(self): deli="\n" text=deli.join(self.lines) with open(self.filename,'w') as outfile: outfile.write(text) # 下载分段 def downloadSegment(idx,start,end): #print("idx={0} start={1} end={2}".format(idx,start,end)) m=CsvMaker(idx,start,end) m.run() # 分段 def cutSegment(minId,maxId,volumn): print("minId={0} maxId={1} volumn={2}".format(minId,maxId,volumn)) idx=0 start=minId end=0 if minId+volumn>maxId: end=maxId idx+=1 downloadSegment(idx,start,end) return end=start+volumn while end<maxId: idx+=1 downloadSegment(idx,start,end) start=end end+=volumn if end>=maxId: end=maxId idx+=1 downloadSegment(idx,start,end) break #Entrance minId=int(sys.argv[1]) maxId=int(sys.argv[2]) volumn=10000 if minId<maxId: start=time.time() cutSegment(minId,maxId,volumn) elapsed=(time.time()-start) print("Seconds elapsed:{0}".format(round(elapsed,1))) else: print('minId can not be bigger than maxId.')
END