文件内容：
col1 col2 col3 China 1.299 0.004 4.762 USA 0.832 5.196 2.521
读取脚本
#!/usr/bin/env python import cx_Oracle conn = cx_Oracle.connect('loge/china@192.168.1.11/prod') c = conn.cursor() # 创建一个列表，后期批量插入 list_line=[] # 只读打开文件 f = open("/root/test.txt", "r") # 创建一个变量，用于跳过文件第一行 line_num = 0 while True: # 读取每一行 line1 = f.readline() line_num +=1 if line1: #处理每行\n if (line_num !=1): line = line1.strip('\n') # 这里将字符串转换为逗号隔开的字符串 line = ",".join(line.split()) # 这里将字符串转为列表 line = line.split(",") print(line) #list_line=(','.join(line.split())).split(',') #list_line.append((','.join(line.split())).split(',')) area = line[0] val1 = float(line[1]) val2 = line[2] val3 = line[3] # 这里组装列表 list_line.append((area,val1,val2,val3)) print("area:%s,val1:%.3f,val2:%s,val3:%s"%(area,val1,val2,val3)) else: break # 关闭文件 f.close() # 执行批量插入 c.executemany('insert into t1(area,v1,v2,v3) values(:1,:2,:3,:4)',list_line) # 提交事务 conn.commit() #关闭游标 c.close() # 关闭DB连接 cursor.close()
2,其他操作，查询，调用过程，函数
>>> import cx_Oracle >>> conn=cx_Oracle.connect('loge/china@192.168.1.11/prod') >>> c=conn.cursor() >>> x=c.execute("""select * from t1""") >>> x.fetchone() ('China', 1.299, 0.004, 4.762, None) >>> c.close() >>> c=conn.cursor() >>> c.prepare("""select * from t1 where area=:id""") >>> x=c.execute(None,{'id':'China'}) >>> for row in x: ... print(row) ... ('China', 1.299, 0.004, 4.762, None) >>> c.close() >>> conn.close() >>> >>> >>> #调用过程 ... >>> conn=cx_Oracle.connect('loge/china@192.168.1.11/prod') >>> c=conn.cursor() >>> str1='China' >>> str2=' ' >>> x=c.callproc('p_demo',[str1,str2]) >>> print(str2) >>> print(str1) China >>> str2=c.var(cx_Oracle.STRING) >>> x=c.callproc('p_demo',[str1,str2]) >>> print(str2) <cx_Oracle.STRING with value 'China'> >>> print(str2.getvalue) <built-in method getvalue of cx_Oracle.STRING object at 0x7f2ab913e4a8> >>> print(str2.getvalue()) China >>> >>> >>> >>> #调用函数 ... >>> c.close() >>> c=conn.cursor() >>> str1='China' >>> str2=c.callfunc('f_demo',cx_Oracle.STRING,[str1]) >>> print(str2) China >>> c.close() >>> conn.colse()附，使用的过程函数
CREATE OR REPLACE PROCEDURE P_DEMO(V1 IN VARCHAR2, V2 OUT VARCHAR2) IS BEGIN V2 := V1; END; CREATE OR REPLACE FUNCTION F_DEMO(V1 VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN V1; END;