CODING FACTORY

MariaDB講座 / Pythonから接続してパラメーター化クエリーを使う

MariaDB講座

PythonからMariaDBへ接続するときは、SQL文と入力値をドライバーへ別々に渡します。パラメーター化クエリー、必要最小限のアカウント、明示したcommitとrollbackで、安全に値を保存する流れを作ります。

環境と制限アカウントを準備する

Python 3とPyMySQL 1.1.2、ローカルTCPを使います。この教材の中核処理の検証環境はPython 3.12とMariaDB 12.3.3です。PyMySQLはDBドライバーで、この例の値プレースホルダーは%sです。他のドライバーの?をそのまま写しません。

管理者で未使用の表とアカウントを確認し、仮パスワードを自分の値へ変えて実行します。

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

SELECTとINSERTだけです。Host127.0.0.1はローカルTCPを前提とし、コンテナーや遠隔では見える接続元が異なり得ます。権限と接続経路を調べ、どこからでも管理者で接続する回避策にしません。

仮想環境と完全なプログラムを保存する

新規のプロジェクトフォルダーでWindows PowerShellを使います。

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

仮想環境のPythonを直接呼ぶため、起動スクリプトの実行方針を変えずに使えます。Linux・macOSは.venv/bin/pythonへ読み替えます。次をUTF-8のnotes_app.pyとして保存します。

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

接続・読取・書込タイムアウト、utf8mb4とautocommit=Falseを明示します。パスワードは実行時入力でソースへ埋め込みません。このPC内の実習と違い、外部ネットワークではサーバー証明書を検証するTLSも必要です。パスワードだけで通信が保護されるわけではありません。

普通の名前と引用符入りの名前を実行する

二回の別実行で、各回パスワードを入力します。

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

最初はNotebook、次はO'Reillyという名前を含む行が表示されます。新規表ならIDが1、2となりますが、過去の実行後は実際のIDを読みます。繰り返すと新しい行を追加し、同名検索は以前の行も返し得ます。引用符をSQLの構文へ連結していないことを確認します。

値の束縛と識別子の許可一覧

cur.execute(sql, (name,))でSQLと値を別々に渡し、一要素タプルのカンマも必要です。%sを引用符で囲んだり、Pythonの%やf文字列で入力を挿入したりしません。ドライバーに値の処理を任せます。

列名やASC/DESCは通常の値プレースホルダーでは任意指定できません。例のcolumnsは固定した許可列だけを対応させ、入力を直接ORDER BYへ足しません。長さ1～80も保存前に検証しますが、束縛は入力方針や権限の代わりではありません。

成功と例外の境界を確認する

保存と検索が成功してからcommit、失敗ならrollbackし、finallyで切断します。同じ接続の未確定範囲を使うため、接続中に勝手な自動確定を混ぜません。入力エラー、接続、権限、SQL実行を分けて診断し、秘密をログへ出さないようにします。

許可されていないUPDATEが制限アカウントで拒否されることも確認できます。終える場合は実習専用アカウントと表であること、必要データがないことを確かめてから削除します。

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;

業務アカウントや表へ名前を読み替えません。表削除は通常のROLLBACKでは復元できません。

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

MariaDB講座
同じカテゴリーの他の記事