CODEWITHSUNDEEP
pythoncx-oracle
Anurag Pattanayak
Anurag Pattanayak

Reputation: 107

CX_Oracle insert data using Bind variable / function and return the record id too

I am trying to create a definition using cx_oracle to insert data in to the database and in return get the record id. I searched the forum and found the solution as below. 

def insert_data(self,SqlQry):
     try:
         idvar=self.__cursor.var(cx_Oracle.NUMBER)
         SqlQry=SqlQry + " returning ID into :vrecord_id"

         self.__cursor.execute(SqlQry,v1='test1',v2='test2',v3='test3',v4='test4', vrecord_id=idvar)
         vid= idvar.getvalue()
         self.__con.commit()
         retuen vid    

     except cx_Oracle.DatabaseError as e:
         return e
         print("Error in data insert")  


print(sql_insertData("INSERT INTO MYTABLE(Field1,Field2,Field3,Field4) VALUES(:v1, :v2, :v3,:v4)")

This works fine and i am able to get the ID. But i want to pass the values with the sql statement inplace of defining each individually as i have done now in .execute line.

cursor.execute(SqlQry,v1='test1',v2='test2',v3='test3',v4='test4', vrecord_id=idvar)

I want to change the current print statement to like this :

print(sql_insertData(
        """INSERT INTO RAP_RISK_TYPE(RISK_HEADER,RISK_TYPE_DISP,RISK_TYPE_DESC,RISK_TYPE_CAT) 
           VALUES
           (:v1, :v2, :v3,:v4)""", ['newvalue1','newvalue2','newvalue3','newvalue4']

But if i do this how do i write the execute statement to get the ID, i get error if i do the below

def insert_data(self,SqlQry,parm):
        try:
            idvar=self.__cursor.var(cx_Oracle.NUMBER)
            SqlQry=SqlQry + " returning ID into :vrecord_id"

            self.__cursor.execute(SqlQry,parm, vrecord_id=idvar)
            vid= idvar.getvalue()
            self.__con.commit()
            retuen vid    

        except cx_Oracle.DatabaseError as e:
            return e
            print("Error in data insert")  


print(sql_insertData(
        """INSERT INTO RAP_RISK_TYPE(RISK_HEADER,RISK_TYPE_DISP,RISK_TYPE_DESC,RISK_TYPE_CAT) 
           VALUES
           (:v1, :v2, :v3,:v4)""", ['newvalue1','newvalue2','newvalue3','newvalue4']

I am not able to pass the list from the print statement and add the "idvar" both at same time.

Upvotes: 1

Views: 1468

Answers (2)

Anthony Tuininga
Anthony Tuininga

Reputation: 7096

Chris has given an excellent generic answer. If you are looking for the specific answer to your question, you need to do the following:

self.__cursor.execute(SqlQry, parm + [idvar])

In other words, you need to ensure that only one set of parameters is passed, not multiple!

Upvotes: 1

Christopher Jones
Christopher Jones

Reputation: 10586

With cx_Oracle 7.3, you can access Cursor.lastRowid after executing the INSERT. See the cx_Oracle example LastRowid.py:

cursor = connection.cursor()
cursor.execute("insert into mytab (id, data) values (:1, :2)", [1, "First"])
print("Rowid 1:", cursor.lastrowid)

Otherwise use the RETURNING INTO clause you were looking at. There is a RETURNING INTO example at https://cx-oracle.readthedocs.io/en/latest/user_guide/bind.html#dml-returning-bind-variables.

Upvotes: 2

Related Questions