python - 插入并返回 :array

Question

我想在 oracle 中的表中添加多行并将添加的值返回到 python 列表

返回的列是主键，在oracle端生成

我写的代码不起作用，这是可以理解的，但是我不知道该怎么写。请帮帮我

out_id = cursor.var(cx_Oracle.NUMBER)

batch = []
for i in range(3):
    batch.append({'val': i})

ins = 'INSERT INTO table (col1) VALUES (:val) RETURNING table.col2 into :out_id'

cursor.executemany(ins, batch)

print(list_out_id.getvalue())

我收到一个错误

ORA-01036: 非法变量名称/编号

标签： pythonsqlalchemycx-oraclesql-returning

Answer 1

For printing output in the format

[{'val': 1, 'out_id': id_1}, {'val': 2, 'out_id': id_2}, {...}]

the following code will work

import cx_Oracle
# Establish the database connection - Add your DB details here 
connection = cx_Oracle.connect(
    user="hr", password="hr", dsn="localhost/orclpdb")

# Obtain a cursor cursor = connection.cursor()

# set the input and output variables
out_id = cursor.var(int, arraysize=3)
batch = []
for i in range(3):
    batch.append({'val': i, 'out_id': out_id})    

# set the SQL statement
ins = 'INSERT INTO table(col1) VALUES (:val) RETURNING table.col2 into :out_id'

# run the query and fetch the output into 'out_id' variable 
cursor.executemany(ins, batch)

#print the output in the required format
for i in range(3):
    batch[i]['out_id'] = batch[i]['out_id'].getvalue(i)
print(batch) 
print(out_id.values)
# Do a commit to the table if required
# connection.commit()