pythoncx-oracle

putting result of query into an object in python


Good Afternoon

I am trying to compile a well information and store the result of the fetch in list of objects as shown below using cx_Oracle 8.3.0 / python.

import cx_Oracle
def getOraCon(daDsn):
    global oraCon
    oraCon = cx_Oracle.connect(user='my_uid',password='my_pwd',dsn=daDsn)
    global oraCursor
    oraCursor = oraCon.cursor()
    executeDDLOracle("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'")

def getFetch(sql,cur):
    cur.execute(sql)
    return cur.fetchall()
    
def executeDDLOracle(strSQL):
    oraCursor.execute(strSQL)
    oraCon.commit()

def closeDbObject(dbObj):
    dbObj.close()
    dbObj = None
    del dbObj
    
def closeOra():
    oraCon.commit()
    closeDbObject(oraCursor)
    closeDbObject(oraCon)

def getFromList():
    getOraCon('CORPORATE_DB')
    executeDDLOracle('DROP TYPE T_TABLE')
    executeDDLOracle('create or replace TYPE T_RECORD IS OBJECT (w_id integer,w_lat varchar2(20), 
    sta_dpth integer,end_dpth integer)')
    executeDDLOracle('CREATE OR REPLACE TYPE T_TABLE AS TABLE OF T_RECORD')

    list_type = oraCon.gettype("T_RECORD")
    
    wb_list_main = list_type.newobject()
    wb_fetch=getFetch("select w_id, w_lat,sta_dpth, end_dpth from wb_data where lat_num >0", 
    oraCursor)
    for w_id, w_lat,sta_dpth, end_dpth in wb_fetch:
        wb_list = list_type.newobject()
        wb_list.type.attributes[0]=w_id
        wb_list.type.attributes[1]=w_lat
        wb_list.type.attributes[2]=sta_dpth
        wb_list.type.attributes[3]=end_dpth 
        wb_list_main.append(wb_list)
    closeOra()

I get the error message:

<class 'cx_Oracle.NotSupportedError'>: Python value cannot be converted to a database value

at:

        wb_list_main.append(list_type)

I would appreciate any assistance. Thank you.


Solution

  • Good Afternoon,

    I thought I would share with you all what I have discovered. I hope this helps others in their work.

    import cx_Oracle
    
    
    def getOraCon(daDsn):
        global oraCon
        oraCon = cx_Oracle.connect(user='my_uid',password='my_pwd',dsn=daDsn)
        global oraCursor
        oraCursor = oraCon.cursor()
        executeDDLOracle("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'")
    
    def makeDict(sql,cur):
        fetch = getFetch(sql,cur)
        return {key:val for key,val in fetch}   
    
    def getFetch(sql,cur):
        cur.execute(sql)
        return cur.fetchall()
        
    def executeDDLOracle(strSQL):
        oraCursor.execute(strSQL)
        oraCon.commit()
    def closeDbObject(dbObj):
        dbObj.close()
        dbObj = None
    def closeOra():
        oraCon.commit()
        closeDbObject(oraCursor)
        closeDbObject(oraCon)
        
    def getData():
        getOraCon('CORPORATE_DB')
        executeDDLOracle('DROP TYPE T_TABLE')
        executeDDLOracle('create or replace TYPE T_RECORD IS OBJECT (w_id integer,w_lat 
        varchar2(20),sta_dpth integer,end_dpth integer)')
        executeDDLOracle('CREATE TYPE T_TABLE AS TABLE OF T_RECORD')
        list_type = oraCon.gettype("T_RECORD")
        wb_list = list_type.newobject()
        
        table_type = oraCon.gettype("T_TABLE")
        wb_table = table_type.newobject()
    
        wb_fetch=getFetch("select w_id, w_lat,sta_dpth, end_dpth from wb_data where lat_num >0", oraCursor) 
        
        for w_id, w_lat,sta_dpth, end_dpth in wb_fetch:
            wb_list.W_ID=w_id
            wb_list.W_LAT =w_lat
            wb_list.STA_DPTH =sta_dpth
            wb_list.END_DPTH =end_dpth 
            wb_table.append(wb_list)
        wb_fetch = None
        del wb_fetch
        test_fetch= oraCursor.execute("select * from table (:id_values)", 
        id_values=wb_table)
        
        for d in test_fetch.description:
            print(d)
        for r in test_fetch:
            print(r)
    

    On a side note, while I agree that ideally I should update my driver to oracledb, currently I have no option to update the driver.