Python cx_Oracle bind list of variables

Question

How can I use bind parameters with variable parameters, for example I would prepare in python dynamically an insert all statement, and would like to bind it with rows of data.

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (:expr[0][0], :expr[0][1], :expr[0][n])
  INTO mytable (column1, column2, column_n) VALUES (:expr[1][0], :expr[1][n], :expr[1][n])
  INTO mytable (column1, column2, column_n) VALUES (:expr[n][0], :expr[n][1], :expr[n][n])
SELECT * FROM dual;

Is this possible using cx_oracle on python?

Christopher Jones · Accepted Answer · 2018-08-15 01:17:20Z

2

Interpreting your real question as 'how do I insert lots of data efficiently in cx_Oracle', the answer is not to use INSERT ALL. Instead you should use executemany():

data = [
    (60, "Parent 60"),
    (70, "Parent 70"),
    (80, "Parent 80"),
    (90, "Parent 90"),
    (100, "Parent 100")
]

cursor.executemany("""
        insert into ParentTable (ParentId, Description)
        values (:1, :2)""", data)

This is all described in https://blogs.oracle.com/opal/efficient-and-scalable-batch-statement-execution-in-python-cx_oracle

answered Aug 15, 2018 at 1:17

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python cx_Oracle bind list of variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related