python操作oracle資料庫

標籤：python   oracle   

1、首先要下載安裝cx_Oracle

      注意：下載的cx_Oracle版本要與自己的python環境版本一致，否則可能串連資料庫是報錯。

2、資料庫表結構

      

3、python代碼

      

# -*- mode: python; coding: utf-8 -*-## python operate oracle, contain insert、delete、update、select.## @author liyulin  # @date 2014-11-07  import cx_Oracleclass PythonOralceUtil:    def __enter__(self):        self.conn = cx_Oracle.connect('testuser/[email protected]/CHROMEBOOK')         self.cursor = self.conn.cursor()        return self       def __exit__(self, type, value, traceback):         self.cursor.close()        self.conn.close()            ############################################    # 查詢reg_codes中的所有資料    ############################################    def queryAll(self):        self.cursor.execute('select * from reg_codes')        results = self.cursor.fetchall()        for result in results:            print result        ############################################    # 根據序號查詢reg_codes中的一條資料    ############################################    def queryBySeq(self, seq):        self.cursor.execute('select * from reg_codes where seq=:1', seq)        result = self.cursor.fetchone()        if (result is not None):                     for index in range(0,6):                print result[index],                        ############################################    # 向reg_codes中插入N條資料    ############################################    def insertManay(self, insertValue):        self.conn.begin()        try:            self.cursor.executemany('insert into reg_codes(device,unique_code,group_code,input_file,sn,input_ts) values(:1,:2,:3,:4,:5,sysdate)', insertValue)        except AssertionError:            self.conn.rollback()            raise Warning, "invalid insertValue (%s)" % insertValue        self.conn.commit()            ############################################    # 更新reg_codes中一條資料    ############################################    def updateOne(self, sqe, input_file):        updateValue = [input_file, sqe]        self.cursor.execute('update reg_codes set input_file=:1 where seq=:2', updateValue)        ############################################    # 更新reg_codes中N條資料    ############################################    def updateManay(self, updateValues):        self.conn.begin()        try:            self.cursor.executemany('update reg_codes set input_file=:1 where seq=:2', updateValues)        except AssertionError:            self.conn.rollback()            raise Warning, "invalid insertValue (%s)" % updateValues        self.conn.commit()        ############################################    # 刪除reg_codes中一條資料    ############################################    def delete(self, sqe):        self.cursor.execute('delete from reg_codes where seq=:1', sqe)        ############################################    # 刪除reg_codes中N條資料    ############################################    def deleteManay(self, seqs):        self.conn.begin()        try:            self.cursor.executemany('delete from reg_codes where seq=:1', seqs)        except AssertionError:            self.conn.rollback()            raise Warning, "invalid seqs (%s)" % seqs        self.conn.commit()            ############################################# 執行代碼############################################        with PythonOralceUtil() as pythonOralceUtil:#     insertValue = [['jerry', 'unique_code2333', 'group_code2333', 'debug233', '1111111111122'],#                ['jerry', 'unique_code244', 'group_code244', 'debug244', '22222222233'],#                ['jerry', 'unique_code255', 'group_code255', 'debug255', '33333333344'],#                ['jerry', 'unique_code266', 'group_code266', 'debug266', '44444444455'],#                ['jerry', 'unique_code277', 'group_code277', 'debug277', '55555555566']]#     pythonOralceUtil.insertManay(insertValue)#     pythonOralceUtil.updateOne('27', 'debug_updated')#     pythonOralceUtil.delete([27])#     pythonOralceUtil.deleteManay([[31],[44],[45]])    updateValues = [['debug_updated', '46'],                    ['debug_updated', '47'],                    ['debug_updated', '48'],                    ['debug_updated', '34']]    pythonOralceUtil.updateManay(updateValues)    pythonOralceUtil.queryAll()    pythonOralceUtil.queryBySeq([27])

python操作oracle資料庫