【Python】读取Oracle连接表生成CSV文件二

• Z时代
• 2024-01-10
• 分类：综合

代码：

#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

以上是 【Python】读取Oracle连接表生成CSV文件二 的全部内容， 来源链接： utcz.com/z/387598.html