利用python对数据库Oracle进行增删查改，并且调用执行存储过程。

利用python对数据库Oracle进行增删查改，并且调用执行存储过程。

import cx_Oracle
class Oracle(object):
def init(self, user_name, password, host, instance):
self._conn = cx_Oracle.connect(‘%s/%s@%s/%s’ % (user_name, password, host, instance))
self.cursor = self._conn.cursor()
print(‘[提示]: 连接成功’)

def query_all(self, sql):
    self.cursor.execute(sql)
    return self.cursor.fetchall()

def query_one(self, sql):
    self.cursor.execute(sql)
    return self.cursor.fetchone()

def query_by(self, sql, params={}):
    self.cursor.execute(sql, params)
    return self.cursor.fetchall()

def insert(self, sql, params={}):
    self.cursor.execute(sql, params)
    self._conn.commit()

def update(self, sql, params={}):
    self.cursor.execute(sql, params)
    self._conn.commit()

def delete(self, sql, params={}):
    self.cursor.execute(sql, params)
    self._conn.commit()
def procedure(self, sql, params={}):
    self.cursor.execute(sql, params)
    self._conn.commit()

if name == ‘main’:
test = Oracle(‘scott’, ‘abc123’, ‘127.0.0.1:1521’, ‘orcl’)
# 测试：查询所有记录
sql1 = ‘select * from scott.dept’
result = test.query_all(sql1)
print(result)
print(‘所有记录已经显示。’)

# 测试：查询单条记录
result = test.query_one(sql1)
print(result)

# 测试：根据查询条件查询记录
sql1 = 'select * from scott.dept where deptno = :deptno and dname = :dname'
result = test.query_by(sql1, {'deptno': 10, 'dname': 'ACCOUNTING'})
print(result)
print('查询指定的记录行。')


#sql1 = "insert into scott.dept(deptno, dname, loc) values(:deptno, :dname, :loc)"
#test.insert(sql1, {'deptno': 50, 'dname': 'a', 'loc': 'b'})
#print('已经成功插入一行。')

sql1 = "update scott.dept t set t.dname = :dname, t.loc = :loc where t.deptno = :deptno"
test.update(sql1, {'deptno': 50, 'dname': 'aaa', 'loc': 'bbb'})
print('已经成功更新一行。')

sql = "delete scott.dept t where t.deptno = :deptno"
test.delete(sql, {'deptno': 90})
print('已经成功删除一行。')

sql = "begin pkg_test.pro_test(:i_deptno, :i_dname, :i_loc); end;"
test.procedure(sql, {'i_deptno': 11, 'i_dname': 'aaa3', 'i_loc': 'bbb3'})
print('已经通过存储过程成功插入一行。')

##以下为Oracle存储过程pkg_test对应的代码
‘’’
CREATE OR REPLACE PACKAGE pkg_test IS
PROCEDURE pro_test(i_deptno IN NUMBER,
i_dname IN VARCHAR2,
i_loc IN VARCHAR2);
END pkg_test;

CREATE OR REPLACE PACKAGE BODY pkg_test IS

PROCEDURE pro_test(i_deptno IN NUMBER,
i_dname IN VARCHAR2,
i_loc IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ‘insert into scott.dept(deptno, dname, loc) values(:b1, :b2, :b3)’
USING i_deptno, i_dname, i_loc;

COMMIT;
END pro_test;
END pkg_test;

‘’’