pythonsqloracle-databaseinsertcx-oracle

Trying to run executemany for insert into returning value in Oracle with Python


Good Morning,

I am trying to find a way to do an insert into returning SQL statement in python. I would like to use executemany,but my code is rather unworkable at this point. Any help would be greatly appreciated. Thank you.

Here is the SQL to create the table:

 CREATE TABLE TABLE1
   (    F1 NUMBER(*,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1, 
    F2 NUMBER(*,0) NOT NULL ENABLE, 
     CONSTRAINT "TABLE1_PK" PRIMARY KEY ("F1")
   )

The Python code is:

import cx_Oracle

def makeCxDSNLessConnection(src):
    return cx_Oracle.connect(user=sUser, password=sPass ,dsn=src)
def tempDoit():
    oraCon = makeCxDSNLessConnection('EPAT')
    cur = oraCon.cursor()
    thislist=list()
    thisOut = cur.var(int)
    cur.execute("TRUNCATE TABLE TABLE1")
    for i in range(1000000):
        thislist.append((i,))
    cur.executemany("INSERT INTO TABLE1(F2) VALUES(:1) RETURNING F1 INTO :2", thislist, thisOut )
    oraCon.commit()
    while True:
        rows = cur.fetchmany(100)
        if not rows:
            break
        for row in rows:
            print(row)  
    

Solution

  • I have found the proper python code:

    import cx_Oracle
    
    def makeCxDSNLessConnection(src):
        return cx_Oracle.connect(user=sUser, password=sPass ,dsn=src)
    
    def tempDoit():
        oraCon = makeCxDSNLessConnection('EPAT')
        cur = oraCon.cursor()
        thislist=list()
        thisOut =cur.var(cx_Oracle.STRING,arraysize=10000)
        cur.execute("TRUNCATE TABLE TABLE1")
        for i in range(10000):
            thislist.append({'F2':i,'F1':thisOut})
        cur.executemany("INSERT INTO TABLE1(F2) VALUES(:F2) RETURNING F1 INTO :F1", thislist)
        print(thisOut.values)
        oraCon.commit()