python oracle增删改查,Python cx_oracle自动化操作oracle数据库增删改查封装，优化返回查询数据...

#coding=utf-8

importcx_Oracleimportosimportjson

os.environ[‘NLS_LANG‘] = ‘SIMPLIFIED CHINESE_CHINA.UTF8‘

"""python version 3.7"""

classTestOracle(object):def __init__(self, user, pwd, ip, port, sid):

self.connect= cx_Oracle.connect(user + "/" + pwd + "@" + ip + ":" + port + "/" +sid)

self.cursor=self.connect.cursor()defselect(self, sql):

list=[]

self.cursor.execute(sql)

result=self.cursor.fetchall()

col_name=self.cursor.descriptionfor row inresult:

dict={}for col inrange(len(col_name)):

key=col_name[col][0]

value=row[col]

dict[key]=value

list.append(dict)

js= json.dumps(list, ensure_ascii=False, indent=2, separators=(‘,‘, ‘:‘))returnjsdefdisconnect(self):

self.cursor.close()

self.connect.close()definsert(self, sql, list_param):try:

self.cursor.executemany(sql, list_param)

self.connect.commit()print("插入ok")exceptException as e:print(e)finally:

self.disconnect()defupdate(self, sql):try:

self.cursor.execute(sql)

self.connect.commit()exceptException as e:print(e)finally:

self.disconnect()defdelete(self, sql):try:

self.cursor.execute(sql)

self.connect.commit()print("delete ok")exceptException as e:print(e)finally:

self.disconnect()#if __name__ == "__main__":#test_oracle = TestOracle(‘SCOTT‘, ‘pipeline‘, ‘127.0.0.1‘, ‘1521‘, ‘orcl‘)#param = [(‘ww1‘, ‘job003‘, 1333, 2), (‘ss1‘, ‘job004‘, 1444, 2)]## test_oracle.insert("insert into bonus(ENAME,JOB,SAL,COMM)values(:1,:2,:3,:4)",param)#也可以下面这样解决orc-1036非法变量问题#test_oracle.insert("insert into bonus(ENAME,JOB,SAL,COMM)values (:ENAME,:JOB,:SAL,:COMM)", param)#test_oracle1 = TestOracle(‘SCOTT‘, ‘pipeline‘, ‘127.0.0.1‘, ‘1521‘, ‘orcl‘)#test_oracle1.delete("delete from bonus where ENAME=‘ss1‘ or ENAME=‘ww1‘")#test_oracle3 = TestOracle(‘SCOTT‘, ‘pipeline‘, ‘127.0.0.1‘, ‘1521‘, ‘orcl‘)#js = test_oracle3.select(‘select * from bonus‘)#print(js)