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)
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()