【はじめの一歩】OCIのMySQL Database Serviceをつかってみよう！（MDS＋Python 他⑦）:トランザクション

はじめに

講釈よりも、とにかく触りたい・使いたい初心者向け手順書テイスト
OCIのMDSをたてて、Python等で繋げて使ってみる。
今回はConnector/Pythonを利用しトランザクション処理の体験です。

前提：OCIの有償アカウントであること（30日間無料トライアルの方もOKです）

本手順は、翔泳社の良書
MySQL徹底入門 第4版 MySQL 8.0対応を参考としています。
同書籍のサンプルファイルを活用します。
※サンプルファイルのReadMeに以下のありがたいお言葉があるので活用して効率化します。
「本書のサンプルデータは自由に利用できることとします。改変のほか、自由に利用、複製、再配布することができます。」
ただ、サンプルファイルの日本語部分は文字化けしているファイルがあるので、
そのまま使うのが難しい場合があります。

本記事の・・・
OCI環境についてはMDS＋Python 他①
DB構成についてはMDS＋Python 他②
シンプルなデータ更新についてはMDS＋Python 他③
プレースホルダーを使用したデータ更新についてはMDS＋Python 他④
シンプルなデータ参照についてはMDS＋Python 他⑤
プレースホルダーを使用したデータ参照についてはMDS＋Python 他⑥
をご確認ください。

前準備

・MDSの起動
・コンピュートの起動
・Cloud Shellの起動とコンピュートへのSSH接続

Cloud Shellからのssh接続

$ ssh -i 秘密キー パブリックIPアドレス -l opc
例：$ ssh -i ssh-key-2022-05-31.key 150.230.193.122-l opc

を行います。

PYファイルの作成

今回はRollback用とCommit用の２つのPYファイルを作ります。

pyファイル作成

$ vi ファイル名.py
ファイルが開かれたら「i」キーを押下した後、処理内容を記入し、「ESCキー →　:(コロン)　→ wq!」で上書き保存

前処理

前段で接続処理を記入します。
mysql.connector.connectのカッコ内：user、password、host、databaseを自身の環境に合わせて編集
★★また、今回はトランザクションの動きをみるため、「autocommit 」は明示的に「False」にします。
デフォルトはどちらか調べてみてください。

import mysql.connector
from mysql.connector import errorcode

try:
    conn = mysql.connector.connect(user='mdsuser', password='Mdsp@ss01',
                                   host='10.0.1.51',
                                   database='mdstest', use_pure=True)
    conn.autocommit = False
    cur = conn.cursor()

トランザクション処理

BEGINで始まり、処理実行後、成功したら「Commit」で更新内容を確定、失敗したら「RollBack」で更新内容を元に戻す。
早速動きを見てみましょう。

■動きの概要
2件のデータ追加処理を行います。
・Rollback側は、データ追加の際に、code情報が重複するように指定します。（code=20）
・Commit側は、データ追加の際に、code情報を指定しません。
それぞれの処理の前後にテーブル内のデータ全件参照させ、結果を確認します。

Rollback側

    sql_str = "INSERT INTO commodity  (code,name,price) VALUES (%s,%s,%s)"
 
    print("---　処理前全件確認　---")
    cur2 = conn.cursor(buffered=True)
    sql_str2 = "SELECT * FROM commodity";
    cur2.execute(sql_str2)
    rows = cur2.fetchall()
    for row in rows:
        print(row)

    print("1つ目のクエリー")
    cur.execute(sql_str, ("20","豚肉", "480"))

    print("2つ目のクエリー")
    cur.execute(sql_str, ("20","鶏肉", "350"))
    conn.commit()
    print("COMMITしました")
    print("---　処理後全件確認　---")
    cur2.execute(sql_str2)
    rows = cur2.fetchall()
    for row in rows:
        print(row)

Commit側 ※Rollback側PYファイルを流用し該当する3行を修正する。

    sql_str = "INSERT INTO commodity  (name,price) VALUES (%s,%s)"


    print("1つ目のクエリー")
    cur.execute(sql_str, ("豚肉", "480"))

    print("2つ目のクエリー")
    cur.execute(sql_str, ("鶏肉", "350"))

後処理（共通）

例外処理の中でRollbackを行っています。Rollback後に全件参照しています。
Commit側PYファイルも同処理を記入します。

後処理

except mysql.connector.Error as err:
    conn.rollback()
    print("ROLLBACKしました")
    print("---　処理後全件確認　---")
    cur2 = conn.cursor(buffered=True)
    sql_str2 = "SELECT * FROM commodity";
    cur2.execute(sql_str2)
    rows = cur2.fetchall()
    for row in rows:
        print(row)

    print("VendorError:", err.errno)
    print("SQLState:", err.sqlstate)
    print("SQLException:", err.msg)
else:
    cur.close()
    conn.close()

実行結果

両方の結果からトランザクションの動きの違いを見ることができました。

Rollback実行結果

$ python Rollback.py 
---　処理前全件確認　---
(1, 'Py_トマト001', 100, None, datetime.datetime(2022, 6, 1, 0, 26, 6))
(2, 'Py_リンゴ(10kg)', 4000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(3, 'Py_ブドウ(3kg)', 5000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(4, 'Py_ミカン(10kg)', 3000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(5, 'Py_イワシ', 100, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(6, 'Py_アジ', 200, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(7, 'Py_サバ', 300, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(8, 'Py_サンマ', 400, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(9, 'Py_マグロ', 900, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
1つ目のクエリー
2つ目のクエリー
ROLLBACKしました
---　処理後全件確認　---
(1, 'Py_トマト001', 100, None, datetime.datetime(2022, 6, 1, 0, 26, 6))
(2, 'Py_リンゴ(10kg)', 4000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(3, 'Py_ブドウ(3kg)', 5000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(4, 'Py_ミカン(10kg)', 3000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(5, 'Py_イワシ', 100, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(6, 'Py_アジ', 200, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(7, 'Py_サバ', 300, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(8, 'Py_サンマ', 400, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(9, 'Py_マグロ', 900, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
VendorError: 1062
SQLState: 23000
SQLException: Duplicate entry '20' for key 'commodity.PRIMARY'
$ 

Commit実行結果

$ python Commit.py 
---　処理前全件確認　---
(1, 'Py_トマト001', 100, None, datetime.datetime(2022, 6, 1, 0, 26, 6))
(2, 'Py_リンゴ(10kg)', 4000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(3, 'Py_ブドウ(3kg)', 5000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(4, 'Py_ミカン(10kg)', 3000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(5, 'Py_イワシ', 100, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(6, 'Py_アジ', 200, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(7, 'Py_サバ', 300, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(8, 'Py_サンマ', 400, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(9, 'Py_マグロ', 900, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
1つ目のクエリー
2つ目のクエリー
COMMITしました
---　処理後全件確認　---
(1, 'Py_トマト001', 100, None, datetime.datetime(2022, 6, 1, 0, 26, 6))
(2, 'Py_リンゴ(10kg)', 4000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(3, 'Py_ブドウ(3kg)', 5000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(4, 'Py_ミカン(10kg)', 3000, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(5, 'Py_イワシ', 100, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(6, 'Py_アジ', 200, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(7, 'Py_サバ', 300, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(8, 'Py_サンマ', 400, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(9, 'Py_マグロ', 900, None, datetime.datetime(2022, 6, 1, 2, 0, 14))
(21, '豚肉', 480, None, datetime.datetime(2022, 6, 1, 6, 17, 50))
(22, '鶏肉', 350, None, datetime.datetime(2022, 6, 1, 6, 17, 50))
$ 

今回は以上です。長文お付き合い頂き、ありがとうございました。

【重要】ＭＤＳは安価ですが、使わないのであれば、停止する、もしくは削除するのを忘れずに
　　　　停止だけだと、ストレージは月単位で課金されます。
お片付け方法は、初回：MDS＋Python 他① お片付けを参考にしてください。