[cx_Oracle入門](第10回) 更新系DML文とDDL/DCL文

検証環境

• Oracle Cloud利用
• Oracle Linux 7.7 (VM.Standard2.1)
• Python 3.6
• cx_Oracle 8.0.0
• Oracle Database 19.5 (ATP, 1OCPU)
• Oracle Instant Client 18.5

※今回以降、cx_Oracleのバージョンを7.3から8に変更します。7.3だと説明が異なる場合もなるべくフォローします。

更新系DML文とDDL/DCL文の実行

基本SELECT文と同じで、Cursorオブジェクトのexecute()メソッドでSQL文を実行します。以下、サンプルです。なお、COMMIT/ROLLBACK文は、Connectionオブジェクトにcommit(), rollbackというメソッドが用意されており、こちらを使用することも可能です。

sample10a.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
create table dept(
  deptno     number(2,0),
  dname      varchar2(14),
  loc        varchar2(13),
  constraint pk_dept primary key (deptno)
)
"""
SQL2 = "insert into dept values(10, 'ACCOUNTING', 'NEW YORK')"
SQL3 = "commit"
SQL4 = "select * from dept"

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                cursor.execute(SQL1)
                cursor.execute(SQL2)
                cursor.execute(SQL3)
                print(cursor.execute(SQL4).fetchone())

バインド変数

バインド変数の扱いも、基本的には第7回と同様です。以下サンプルです。前述のsample10a.pyの実行が前提となっています。

sample10b.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = "insert into dept values(:deptno, :dname, :loc)"
SQL2 = "commit"
binds = [{"deptno":20, "dname":"RESEARCH", "loc":"DALLAS"},
         {"deptno":30, "dname":"SALES", "loc":"CHICAGO"}]

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                cursor.prepare(SQL1)
                cursor.executemany(None, binds)
                cursor.execute(SQL2)

下から2行目のexecutemany()メソッドは、利用方法から想像がつくとは思いますが、2番目のパラメータのリストの要素数の回数のPreparedStatementを実行します。

NULL値のバインド

DBのNULL値は、Python上ではNoneが該当します。NULLを格納したい場合はバインド変数にはNoneを設定します。NULL値をSELECTした場合は、Noneが変数に格納されます。

Output先としてのバインド変数

ここまで、SQLへ値を渡す形のバインド変数の例ばかりでした。実際には、SQLから受ける形のバインド変数も存在します。この場合、Cursorオブジェクトのvarメソッドを利用して、値を受けるバインド変数の役割をするPython変数を作成することで対応が可能です。以下、RETURNING句を受けるバインド変数利用のサンプルです。前述のsample10b.pyの実行が前提となっています。

sample10c.py

import cx_Oracle

USERID = "admin"
PASSWORD = "FooBar"
DESTINATION = "atp1_low"
SQL1 = """
update dept set deptno = deptno + 10 where deptno > 0
returning deptno into :out_deptno
"""
SQL2 = "rollback"

with cx_Oracle.connect(USERID, PASSWORD, DESTINATION) as connection:
        with connection.cursor() as cursor:
                bind_deptno = cursor.var(int)
                cursor.execute(SQL1, out_deptno = bind_deptno)
                print(f"DEPTNO = {bind_deptno.getvalue()}")
                print(f"RowCount = {cursor.rowcount}")
                cursor.execute(SQL2)

下から5行目が、SQLからの出力を受けるバインド変数のための、Python変数の定義です。var変数の引数にデータを受ける際のPythonデータ型を指定します。
下から3行目のgetvalue()で、SQL文のRETURNING句のバインド変数内容を受け取っています。引数はデフォルトが0で、バインド変数の位置を指定します。今回はバインド変数が一つしかないので0(一つ目)で問題ありません。この例の場合だと、printされる更新後の列値はintのリストの形で戻ります。

更新行数を知る

先のサンプルの下から2行目が該当します。
更新SQL実行後にサンプルにあるCursorオブジェクトのrowcount属性にアクセスすると、更新された行数を取得することが可能です。SELECT文でこの属性値にアクセスした場合は、その時点でfetchされた行数が格納されています。

変更履歴

• 2020/8/7 : Connection.commit(), Connection.rollback()の説明の追加