Oracle文件方式批量导入数据

上一篇 / 下一篇 2017-04-01 17:48:32 / 个人分类：其他

-----留个记录------

python准备txt文件（根据业务需求）

import cx_Oracle

import sys

def getUid(): conn=cx_Oracle.connect('user','pwd','hostname:port/orcl')

cur=conn.cursor()

cur.execute('select id from testtable where XXX=103001')

result = cur.fetchall()

cur.close()

conn.close()

print result

return result

f=open("d:\userlogininfo.txt","w")

result=getUid()

print len(result)

for i in range(1,len(result)+1):

a = "id,NAME,RECORDid,SEXCOD,SEX_NAME,.......一切想插入到数据库的值"'\n'

b = a.replace("id",str(result[i-1][0]));

c = ""+b

f.write(c)

f.close()

------以上文件已经准备好，运行可生成txt文件---------

control.txt---一下是导入表的一些设置

LOAD DATA

INFILE 'D:\userlogininfo.txt'

truncate

INTO TABLE porsetable

fields terminated by ','

trailing nullcols

(ID,NAME,RECORDID,SEX_CODE,SEX_NAME,....该目标表中的所有字段)

-----导入操作-命令行操作-------cmd 命令

C:\Documents and Settings\Administrator>sqlldr scott/pwd control=c:\control.txt log=c:\loadlog.txt