Python講座 / MariaDBへ接続しトランザクションを管理する

Python講座

PythonからMariaDBへはデータベースドライバーを使って接続し、SQL本文と入力値を分けて渡します。この例はPyMySQLを使い、動作中のサーバー、実習専用データベースとアカウントを必要とします。

サーバーと専用アカウントを準備する

ローカルMariaDBの管理者接続で次のSQLを実行します。同名のDBや利用者が既にあれば用途を調べ、新しい練習用名を選びます。例のパスワードを手元の秘密に置き換え、アプリには管理者アカウントを使わないでください。

CREATE DATABASE python_lab CHARACTER SET utf8mb4;
CREATE USER 'python_app'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-local-secret';
CREATE TABLE python_lab.expenses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  external_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  amount BIGINT NOT NULL CHECK(amount >= 0)
) ENGINE=InnoDB;
GRANT SELECT, INSERT, UPDATE ON python_lab.expenses TO 'python_app'@'127.0.0.1';

権限は必要な表操作へ限定します。InnoDBがトランザクションを提供します。既存アプリのデータとは別の練習用DBとして扱います。

トランザクションと接続失敗は、try・except・else・finally・raiseで例外を扱うで扱う規則とつながります。

選択子やサーバー接続の準備はMariaDB講座 / Pythonから接続してパラメーター化クエリーを使うの該当実習で確認できます。

ドライバーと秘密情報を用意する

仮想環境へ導入します。

python -m pip install PyMySQL

OSの環境設定機能やプロジェクトの秘密管理で、現在のプロセスへMARIADB_USERとMARIADB_PASSWORDを設定します。実際の値をソース管理やログへ残さず、シェル履歴にも注意します。

例は127.0.0.1へ接続します。リモートではサーバーとドライバーの資料に従い、適切なCAとホスト名検証を含むTLSを設定します。アドレスだけの変更でローカル接続と同じ前提を保てるわけではありません。

この例はlocalhostではなくTCPの127.0.0.1へ接続するため、SQLで作るアカウントのhostも127.0.0.1に合わせます。管理者はスキーマ準備だけに使い、Pythonはpython_appで接続します。下の環境変数は現在のセッションに設定し、実際のパスワードをコマンドに直書きしたり表示したりしません。接続失敗はサーバー・ポート、アカウントのhost・パスワード、テーブルのSELECT・INSERT・UPDATE権限の順に調べます。権限エラーのためにサーバー全体の権限を与えないでください。

Windows PowerShellでパスワードを非表示で入力し、終了後に環境変数と一時変数を除きます。

$env:MARIADB_USER = "python_app"
$secret = Read-Host "MariaDB password" -AsSecureString
$credential = New-Object System.Net.NetworkCredential("", $secret)
$env:MARIADB_PASSWORD = $credential.Password
python mariadb_demo.py
Remove-Item Env:MARIADB_PASSWORD
Remove-Item Env:MARIADB_USER
Remove-Variable secret, credential

macOS・UbuntuのBashでは現在のシェルに次のように設定します。

export MARIADB_USER=python_app
read -r -s -p "MariaDB password: " MARIADB_PASSWORD
printf "\n"
export MARIADB_PASSWORD
python mariadb_demo.py
unset MARIADB_PASSWORD MARIADB_USER

正常結果は('lunch', 8000)です。接続は自分の練習サーバーで確認します。環境変数は子プロセスにも渡り、専用の秘密管理ストアと同等の保護ではありません。

接続と保存の全体を実行する

mariadb_demo.pyへ保存します。

import os
import pymysql

con = pymysql.connect(
    host="127.0.0.1", port=int(os.environ.get("MARIADB_PORT", "3306")),
    user=os.environ["MARIADB_USER"], password=os.environ["MARIADB_PASSWORD"],
    database="python_lab", charset="utf8mb4", autocommit=False,
    connect_timeout=5, read_timeout=5, write_timeout=5,
)
try:
    try:
        with con.cursor() as cur:
            cur.execute("INSERT INTO expenses(external_id,name,amount) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name), amount=VALUES(amount)", ("demo-1", "lunch", 8000))
        con.commit()
    except Exception:
        con.rollback()
        raise
    with con.cursor() as cur:
        cur.execute("SELECT name,amount FROM expenses WHERE external_id=%s", ("demo-1",))
        print(cur.fetchone())
finally:
    con.close()

PyMySQLのある環境で実行します。

python mariadb_demo.py

正しく準備したサーバーでは('lunch', 8000)が返ります。金額はKRWの整数単位です。一定のexternal_idにより、再実行は同じ取引を更新し、別の一件を増やしません。

パラメーター・取り消し・終了を確認する

PyMySQLは数値にも文字列にも%sの値プレースホルダーを使います。executeの引数で値を渡し、Pythonの%やf文字列でSQLへ埋め込まないでください。表名や列名はSQLの構造なので、動的なら管理した候補から選びます。

誤ったパスワードの接続失敗、-1の制約違反と以前の値の保持、限定権限での許可・拒否をそれぞれ確認し、設定を戻します。接続失敗時には接続オブジェクトは作られていません。接続後はカーソルの寿命、確定・取り消し、接続終了を分けて管理します。rollbackで過去の通信やファイル操作までは戻せません。SQLiteのトランザクションにも同じ境界があります。

サーバーと専用アカウントを準備する

ドライバーと秘密情報を用意する

接続と保存の全体を実行する

パラメーター・取り消し・終了を確認する

関連する公式リファレンス