python连接Oracle实现增删查改

一、下载对应的cx_Oracle模块

1.进入到python根目录下的scripts文件夹

2.在上方路径输入cmd，然后回车

3.在cmd中输入pip install cx_Oracle并回车

二、任务内容

1.建立python函数，对teacher表中数据进行修改，要求SQL语句中，通过参数传递值，构建修改值和查询条件，进行数据修改。

2.建立python函数，删除course表中的数据，要求SQL语句中，通过参数传递值，构建查询条件，进行数据删除。

3.建立python函数，查询对teacher和course表进行数据更新操作前后的结果。

三、代码实现

1.首先在图形化界面工具PLSQL中创建一系列的表：

代码如下：

--创建数据表
create table teacherCard
(
tcid int primary key,
tcdesc varchar2(20)
);

create table teacher
(
tid int primary key,
tname varchar2(20),
tcid int references teacherCard(tcid)
);

create table course
(
cid int primary key,
cname varchar2(20),
tid int references teacher(tid)
);
--输入数据
insert into teacherCard values(1,'tzdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');
insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);
insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);
commit;

2.创建好需要的Oracle类

代码如下：

import cx_Oracle
 
class Oracle(object):
    """  oracle db operator  """
    def __init__(self,userName,password,host,instance):
        self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName,password,host,instance))
        #self._conn = cx_Oracle.connect('system', 'Csz123456', '127.0.0.1:1521/orcl')
        self.cursor = self._conn.cursor()
        # query methods
    def queryAll(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    
    def queryOne(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()
    
    def queryBy(self,sql,nameParams={}):
        if len(nameParams) > 0 :
            self.cursor.execute(sql,nameParams)
        else:
            self.cursor.execute(sql)
            
        return self.cursor.fetchall()
    
    def insertBatch(self,sql,nameParams=[]):
        """batch insert much rows one time,use location parameter"""
        self.cursor.prepare(sql)
        self.cursor.executemany(None, nameParams)
        self.commit()
    
    def commit(self):
        self._conn.commit()
    
    def __del__(self):
        if hasattr(self,'cursor'): 
            self.cursor.close()
            
        if hasattr(self,'_conn'): 
            self._conn.close()

3.实现增删查改

增加操作：

def add_course(self, value):
      sql = "INSERT INTO course VALUES " + value
      #游标执行
      self.cursor.execute(sql)
      self.commit
      print("成功增加course表中的数据"

删除操作：

    def delete_course(self, condition):
        sql = "DELETE course WHERE " + condition
        #游标执行
        self.cursor.execute(sql)
        self.commit()
        print("成功删除course表中的数据")

查询操作：

def queryResult():
        sql1 = "SELECT * FROM teacher"
        sql2 = "SELECT * FROM course"
        oraDb = Oracle('system','Csz123456','127.0.0.1:1521','orcl')
        fields1 = oraDb.queryAll(sql1)
        fields2 = oraDb.queryAll(sql2)
        print(fields1)
        print(fields2)

修改操作：

def update_teacher(self, value, condition):
        sql = "UPDATE teacher SET TNAME = " + value + " WHERE " + condition
        #游标执行
        self.cursor.execute(sql)
        self,commit
        print("成功修改teacher表中的数据")

完整的python代码：

import cx_Oracle
 
class Oracle(object):
    """  oracle db operator  """
    def __init__(self,userName,password,host,instance):
        self._conn = cx_Oracle.connect("%s/%s@%s/%s" % (userName,password,host,instance))
        #self._conn = cx_Oracle.connect('system', 'Csz123456', '127.0.0.1:1521/orcl')
        self.cursor = self._conn.cursor()
        # query methods
    def queryAll(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    
    def queryOne(self,sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()
    
    def queryBy(self,sql,nameParams={}):
        if len(nameParams) > 0 :
            self.cursor.execute(sql,nameParams)
        else:
            self.cursor.execute(sql)
            
        return self.cursor.fetchall()
    

    def add_course(self, value):
        sql = "INSERT INTO course VALUES " + value
        #游标执行
        self.cursor.execute(sql)
        self.commit
        print("成功增加course表中的数据"
    
    def insertBatch(self,sql,nameParams=[]):
        """batch insert much rows one time,use location parameter"""
        self.cursor.prepare(sql)
        self.cursor.executemany(None, nameParams)
        self.commit()
    
    def commit(self):
        self._conn.commit()
    
    def __del__(self):
        if hasattr(self,'cursor'): 
            self.cursor.close()
            
        if hasattr(self,'_conn'): 
            self._conn.close()

    def update_teacher(self, value, condition):
        sql = "UPDATE teacher SET TNAME = " + value + " WHERE " + condition
        #游标执行
        self.cursor.execute(sql)
        print("成功修改teacher表中的数据")
        self.commit()

    def delete_course(self, condition):
        sql = "DELETE course WHERE " + condition
        #游标执行
        self.cursor.execute(sql)
        self.commit()
        print("成功删除course表中的数据")
        
    def queryResult():
        sql1 = "SELECT * FROM teacher"
        sql2 = "SELECT * FROM course"
        oraDb = Oracle('system','Csz123456','127.0.0.1:1521','orcl')
        fields1 = oraDb.queryAll(sql1)
        fields2 = oraDb.queryAll(sql2)
        print(fields1)
        print(fields2)
def test1():
    oraDb = Oracle('system','Csz123456','127.0.0.1:1521','orcl')
    value = "'csz'"
    condition = "TID = 1"
    oraDb.update_teacher(value, condition)
def test2():
    oraDb = Oracle('system','Csz123456','127.0.0.1:1521','orcl')
    condition = "CID = 1"
    oraDb.delete_course(condition)
print("更新操作之前的表:")
queryResult()
test1()
test2()
print("更新操作之后的表:")
queryResult()