Python で MySQL のストアドプロシージャを実行し、結果セットを辞書のリストに変換する

ここでは Python で MySQL のストアドプロシージャを実行して、結果セットを辞書のリストに変換する方法についてご説明します。

MySQL Connector をインストールしていない方は、「Python で MySQL に接続する」をご参考に、MySQL に接続できる状態にしておいてください。

Python で MySQL のストアドプロシージャを実行する

Python で MySQL Connector を使って、MySQL のストアドプロシージャを実行するするには MySQLCursor の callproc() メソッドを使います。

実際にストアドプロシージャを実行してみましょう。


test データベースの student テーブルに次のようなデータが入っています。

Python で MySQL のストアドプロシージャを実行し、結果セットを辞書のリストに変換する 1


そして、次のような search_student_by_name という名前のストアドプロシージャがあります。

入力引数で渡した文字が first_name か last_name に含まれている student のレコードを返すだけのシンプルなストアドプロシージャです。

DROP PROCEDURE IF EXISTS search_student_by_name;
DELIMITER //
CREATE PROCEDURE search_student_by_name (
	in_name VARCHAR(50)
)
BEGIN

	SELECT	student_id,
			first_name,
			last_name,
            birthday,
            gender
    FROM 	student
    WHERE 	first_name LIKE CONCAT('%', in_name ,'%')
			OR last_name LIKE CONCAT('%', in_name ,'%');
    
END//
DELIMITER ;

Python で MySQL のストアドプロシージャを実行し、結果セットを辞書のリストに変換する 2


Python でこのストアドプロシージャを実行して、結果セットを辞書のリストに変換するには、次のようにできます。

import mysql.connector

cnx = None

try:
    cnx = mysql.connector.connect(
        user='root',  # ユーザー名
        password='password',  # パスワード
        host='localhost',  # ホスト名(IPアドレス）
        database='test'  # データベース名
    )

    cursor = cnx.cursor()

    args = ('ka',)
    cursor.callproc('search_student_by_name', args)

    students = []
    for result in cursor.stored_results():
        for row in result:
            students.append(dict(zip(result.column_names, row)))

    cursor.close()

    print(students)

    for student in students:
        print(f"{student['first_name']} {student['last_name']} ({student['gender']})")

except Exception as e:
    print(f"Error Occurred: {e}")

finally:
    if cnx is not None and cnx.is_connected():
        cnx.close()

上のコードを実行すると、search_student_by_name が実行され、'ka' という文字が名前に含まれた student 情報が次のように出力されます。

[{'student_id': 2, 'first_name': 'Hiroki', 'last_name': 'Takagi', 'birthday': datetime.date(2000, 4, 5), 'gender': 'M'}, {'student_id': 3, 'first_name': 'Yuka', 'last_name': 'Kimura', 'birthday': datetime.date(2001, 3, 27), 'gender': 'F'}]
Hiroki Takagi (M)
Yuka Kimura (F)

まず、1 行目で mysql.connector をインポートして、6 ~ 11 行目で、connect() メソッドに、MySQL のユーザー名、パスワード、ホスト名、データベース名を渡してコネクションを生成しています。

13 行目で cursor を生成し、15 行目で引数を定義し、16 行目で callproc(ストアドプロシージャ名, 引数) を使って、ストアドプロシージャを実行しています。

引数はタプルで定義しますが、要素がひとつのタプルを定義したい時は、ひとつ目の要素の後ろにカンマをつけないと、タプル型にならず文字列型になってしまい、エラーになりますのでご注意ください。

18 ~ 21 行目で、cursor.stored_results() で取得されたストアドプロシージャの実行結果セットをループして zip(result.column_names, row) でカラム名と値のタプルを結合し、辞書に変換して students リストに追加しています。

カラム名が必要なければ、20、21 行目の代わりに rows = result.fetchall() のようにして、結果セットをタプルのリストとして一度に取得することも可能です。

25 行目では students リストの内容を一括でプリントしていて、27 ~ 28 行目では students リストをループして student の情報をプリントしています。


以上、Python で MySQL のストアドプロシージャを実行して、結果セットを辞書のリストに変換する方法についてご説明しました。

© 2024 やさしい Python 入門