【script】python使用cx_Oracle模块访问Oracle

python访问oracle基础代码

'''
cx_Oracle需与python版本一一对应
cx_Oracle下载地址：https://www.lfd.uci.edu/~gohlke/pythonlibs/#cx_oracle
'''

import cx_Oracle  #导入oracle数据库的包

class ORACLE:
    def __init__(self, user, pwd, host, port, service):
        self.user = user
        self.pwd = pwd
        self.host = host
        self.port = port
        self.service = service
        
    def __GetConnect(self):
        '''
        连接数据库
        '''
        try:
            self.conn = cx_Oracle.connect(self.user, self.pwd, self.host + ':' + self.port + '/' + self.service)
            db = self.conn.cursor()
            return db
        except Exception as e:
            raise e
            
    def ExecQuery(self, sql):
        '''
        查询数据库
        '''
        try:
            db = self.__GetConnect()
            db.execute(sql)
            resList = db.fetchall()
            self.conn.close()
            return resList
        except Exception as e:
            raise e

    def ExecNonQuery(self, sql):
        '''
        更新数据库
        '''
        try:
            db = self.__GetConnect()
            db.execute(sql)
            self.conn.commit()
            self.conn.close()
        except Exception as e:
            raise e

# #创建数据库连接对象，要使用哪个数据库需登陆所属用户，否则无法查找到库和表
# orc = ORACLE(user='system', pwd='system', host='127.0.0.1', port='1521', service='XE')

# # 查询数据库示例
# reslist = orc.ExecQuery("select * from \"student\"")
# list(map(print, reslist))

# # 更新数据库示例
# newsql="update \"student\" set \"age\"='18' where \"num\"=3"
# orc.ExecNonQuery(newsql.encode('utf-8'))

访问中出现的报错

ORA-00942: table or view does not exist

oracle数据库是不存在库名的，只有用户和表对象。当表不属于当前登陆的用户，则可能出现该报错

找到表所属的用户，登陆对应用户即可；当然，也可以在当前登陆的用户下创建对应的表，下面演示第一种情况