cx_Oracle模块封装

About cx_Oracle

    cx_Oracle is a Python extension module that enables access to Oracle Database. It conforms to the Python database API 2.0  specification with a considerable number of additions and a couple of exclusions.

---

# coding:utf-8

"""
db.py
contains some oracle database operations
"""

import cx_Oracle 
# customer definition exception
from exception import IdbError
#log some info to some file
from logger import Logger
import os, sys, re

DB_STRING = cx_Oracle.STRING
DB_NUMBER = cx_Oracle.NUMBER
DB_DATETIME = cx_Oracle.DATETIME

class Param:
    """
        store procedures paraments class
    """
    def __init__(self, para_type, value, out=False):
        if para_type not in [DB_STRING, DB_NUMBER, DB_DATETIME]:
            raise IdbError("%s is not a valid para type %s" % para_type)
        self.param_type = para_type
        self.value = value
        self.out = out

class DB:
    """
        oracle db class
    """
    logger = Logger("/path/to/your/log/path","db")
    def __init__(self, username, pwd, dsn):
        self.username = username
        self.pwd = pwd
        self.dsn = dsn    
        self.__conn__ = None
        self.__cursor__ = None
    
    def open_connection(self):
        ora_str = self.username+'/'+self.pwd+self.dsn
        try:
            self.__conn__ = cx_Oracle.connect(ora_str)
        except Exception as e:
            DB.logger.log_error("connect to DB failed %s" % str(e))
            raise IdbError("connect to DB failed %s" % str(e))
        self.__cursor__ = self.__conn__.cursor()

    def close_connection(self):
        try:
            if self.__conn__:
                self.__conn__.close()
            if self.__cursor__:
                self.__cursor__.close()
        except Exception as e:
            pass

    def select(self, statement, **paraments):
        self.open_connection()
        try:
            self.__cursor__.execute(statement, paraments)
            rs = self.__cursor__.fetchall()
        except Exception as e:
            pass
        finally:
            self.close_connection()
        print(rs)
        return rs
    
    
    def select_onerow(self, statement, **paraments):
        self.open_connection()
        try:
            self.__cursor__.execute(statement, paraments)
            rs = self.__cursor__.fetchone()
        except Exception as e:
            pass
        finally:
            self.close_connection()
        if rs:
            print(rs)
            return rs   
        else: return None

    def exec_update(self, statement, **paraments):
        self.open_connection()
        try:
            self.__cursor__execute(statement, paraments)
            self.__conn__.commit()
        except Exception as e:
            pass
        finally:
            rowcount = self.__cursor__.rowcount
            self.close_connection()
        return rowcount
    
     def callsp(self, proc, *params):
        self.open_connection()
        # construct parament list
        param_array = ()
        for param in params:
            p = self.__cursor__.var(param.param_type)
            print(p)
            if not param.out:
                p.setvalue(0, param.value)
            param_array +=p
        # call store procedure
        try:
            self.__cursor__.callproc(proc, param_array)
            for i,param in enumerate(params):
                if param.out:
                    param.value = param_array[i].getvalue()
        except Exception as e:
            print(e)
        finally:
            self.close_connection()
 
if __name__ == '__main__':
    dsn = "mydsn"
    username = "oracleman"
    pwd = 'oracleman'
    proc = 'myproc'
    str1 = Param(DB_STRING, 'tm')
    str2 = Param(DB_STRING, '', True)
    ora = DB(username, pwd, dsn0
    ora.callsp(proc, str1, str2)
    ora.select("select * from tablename")
    ora.select_onerow("your statement here")