1、环境准备
oracle创建用于测试的表t1:
SCOTT@TNS_PDB01>create table t1(id number,name varchar2(20));
表已创建。
SCOTT@TNS_PDB01>desc t1;
名称 是否为空? 类型
----------------------------------------------------------- -------- --------------------------------
---------
ID NUMBER
NAME VARCHAR2(20)
SCOTT@TNS_PDB01>select * from t1;
未选定行
2、使用Python创建一个用于DML操作的模块
import cx_Oracle
class MyOracle():
def __init__(self,userName,userPasswd,TNS):
if userName != 'sys':
self.conn = cx_Oracle.connect(userName+ '/' + userPasswd + '@' + TNS)
else:
self.conn = cx_Oracle.connect(userName + '/' + userPasswd + '@' + TNS,mode=cx_Oracle.SYSDBA)
self.cursor = self.conn.cursor()
#查询函数
def selectDatas(self,selectSql):
self.cursor.execute(selectSql)
rows = self.cursor.fetchall()
for row in rows:
print("%d %s" % row)
#批量插入函数
def batchInsertDatas(self,batchInsertSql,batchInsertDatas):
self.cursor.executemany(batchInsertSql,batchInsertDatas)
self.conn.commit()
#删除函数
def deleteDatas(self,deleteSql):
print("Begin deleting...")
self.cursor.execute(deleteSql)
print(str(self.cursor.rowcount) + " has be deleted.")
self.conn.commit()
print("End deleting...")
#关闭连接函数
def closeConn(self):
self.cursor.close()
self.conn.close()
my_oracle = MyOracle('scott','scott','TNS_PDB01')
myBatchInsertSql = "insert into scott.t1 values(:1,:2)"
myBatchInsertDatas = [
(1,'a'),
(2,'b'),
(3,'c'),
(4,'d'),
(5,'e'),
(6,'f'),
(7,'g'),
(8,'h')
]
mySelectSql = "select * from scott.t1 order by 1"
my_oracle.selectDatas(mySelectSql)
my_oracle.batchInsertDatas(myBatchInsertSql,myBatchInsertDatas)
myDeleteSql = "delete from scott.t1 where id in (1,2,3)"
my_oracle.deleteDatas(myDeleteSql)
my_oracle.selectDatas(mySelectSql)
my_oracle.closeConn()
3、执行脚本后检查是否成功执行DML操作
SCOTT@TNS_PDB01>select * from t1;
ID NAME
--- ---------------
4 d
5 e
6 f
7 g
8 h