Python绑定变量插入oracle数据库

Python绑定变量插入oracle数据库

1、数据表

create table ORDER_TEST
(
  order_id      CHAR(16),
  product_id    VARCHAR2(5),
  product_price NUMBER,
  product_num   INTEGER
);

2、Python代码

核心是通过begin end 语块，用:1 :2 :3 :4传递参数，实现oracle绑定参数，提高效率。

import cx_Oracle
def bindparameter():
    db = cx_Oracle.connect('scott', 'Tiger', '192.168.123.123:1521/backup')
    cursor_query = db.cursor()
    sql_text = "begin execute immediate 'insert into ORDER_TEST values(:1,:2,:3,:4) ' using 'WSCQqBWfukdXqSrD','NX-14','649.85','126';commit; end;"
    cursor_query.execute(sql_text)
    sql_text = "begin execute immediate 'insert into ORDER_TEST values(:1,:2,:3,:4) ' using 'WSCQqBWfukdXqSrD','CG-95','737.27','173';commit; end;"
    cursor_query.execute(sql_text)
    sql_text = "begin execute immediate 'insert into ORDER_TEST values(:1,:2,:3,:4) ' using 'WSCQqBWfukdXqSrD','OH-54','282.47','138';commit; end;"
    cursor_query.execute(sql_text)
    sql_text = "begin execute immediate 'insert into ORDER_TEST values(:1,:2,:3,:4) ' using 'WSCQqBWfukdXqSrD','XK-65','180.99','168';commit; end;"
    cursor_query.execute(sql_text)

    db.commit()
    cursor_query.close()
    db.close()

if __name__ == "__main__":

    bindparameter()


检查插入数据：

SQL> SELECT * FROM ORDER_TEST;

ORDER_ID PRODUCT_ID PRODUCT_PRICE PRODUCT_NUM

---

WSCQqBWfukdXqSrD NX-14 649.85 126
WSCQqBWfukdXqSrD CG-95 737.27 173
WSCQqBWfukdXqSrD OH-54 282.47 138
WSCQqBWfukdXqSrD XK-65 180.99 168

Executed in 0.117 seconds