1,读取文件操作
文件内容:
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;