Python封装一个模块对Oracle执行查询、删除、插入等操作

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