Python講座 / MariaDBへ接続しトランザクションを管理する
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として扱います。
ドライバーと秘密情報を用意する
仮想環境へ導入します。
python -m pip install PyMySQL
OSの環境設定機能やプロジェクトの秘密管理で、現在のプロセスへMARIADB_USERとMARIADB_PASSWORDを設定します。実際の値をソース管理やログへ残さず、シェル履歴にも注意します。
例は127.0.0.1へ接続します。リモートではサーバーとドライバーの資料に従い、適切なCAとホスト名検証を含むTLSを設定します。アドレスだけの変更でローカル接続と同じ前提を保てるわけではありません。
接続と保存の全体を実行する
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のトランザクションにも同じ境界があります。
