利用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;

'''


以下为在pycharm2023版本里执行python代码后的显示结果截图：