Pythonで始めるデータベース操作：MySQLとBLOBデータの基本

Python
2023.12.21

PythonでMySQLを操作するには？

MySQLは、広く使用されているオープンソースのリレーショナルデータベース管理システム（RDBMS）で利用料金がかからず様々なオペレーティングシステム上で動作し、多くのプログラミング言語から利用することが可能です。

本記事では、PythonでMySQLに対してINSERT、UPDATE等を実行するexec関数とSELECTを実行するselect関数を等作成して処理する方法を紹介します。

事前準備

サンプルコード実行用にMySQL.zipファイルをダウンロードします。

MySQLダウンロード

ダウンロード後は以下の図のようにCドライブにtestdbというフォルダを作成し、zipファイル内の商品マスタ.sql、candy.pngその中にコピーしてください。

次に商品マスタ.sqlをphpMyAdminなどで実行してテーブルを作成してください。

CREATE TABLE 商品マスタ (
  品番 varchar(4) NOT NULL,
  品名 varchar(20) DEFAULT NULL,
  価格 int(3) DEFAULT NULL,
  商品画像 mediumblob DEFAULT NULL,
  PRIMARY KEY (品番)
)

MySQLを利用する場合には、mysql-connector-pythonが必要なのでコンソールから以下を入力してインストールします。

pip install mysql-connector-python

MySQLの接続情報を記載します。筆者は以下に記載のようにconfig.iniを利用して設定値を取得しています。

import common.get_ini as get_ini

# config.iniよりデータベース接続情報の取得
# MySQL設定セクションから接続情報を取得する
DB_HOST = get_ini.get_value('MySQL', 'dbhost')
DB_PORT = get_ini.get_value('MySQL', 'dbport')
DB_USER = get_ini.get_value('MySQL', 'dbuser')
DB_PASSWORD = get_ini.get_value('MySQL', 'dbpassword')
DB_NAME = get_ini.get_value('MySQL', 'dbname')

iniファイル読み込み関数の作り方はこちらを参照ください。

Pythonでiniファイルを読み込み、書き込みする。
Pythonでiniファイルを読み込みする方法を紹介しています。
kamedassou.com

直接ソースに記載する場合には次のように環境に合わせて記載しても構いません。

DB_HOST = "localhost"
DB_PORT = "3306"
DB_USER = "mysqluser"
DB_PASSWORD = "mysqlpassword"
DB_NAME = "mysqldatabase"

サンプルコード

次に、exec関数とselect関数のサンプルコードを紹介します。
関数の下にはテスト用のコードも記載していますので参考にしてください。

# pip install mysql-connector-python
import mysql.connector
import pandas as pd
import common.get_ini as get_ini

# config.iniよりデータベース接続情報の取得
# MySQL設定セクションから接続情報を取得する
DB_HOST = get_ini.get_value('MySQL', 'dbhost')
DB_PORT = get_ini.get_value('MySQL', 'dbport')
DB_USER = get_ini.get_value('MySQL', 'dbuser')
DB_PASSWORD = get_ini.get_value('MySQL', 'dbpassword')
DB_NAME = get_ini.get_value('MySQL', 'dbname')

def select(select_query, param=None):
    """
    mysqlでSELECT文を実行する。

    Args:
        select_query (str): SELECT文のクエリ文字列
        param (tuple/list, optional): クエリのパラメータ。デフォルトはNone。

    Returns:
        tuple: 実行結果のタプル
    """
    # データベースに接続
    conn = mysql.connector.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    # カーソルを取得
    cur = conn.cursor()
    # SELECT文を実行して結果を取得
    cur.execute(select_query, param)
    rows = cur.fetchall()
    
    # 接続を終了
    cur.close()
    conn.close()
    
    # 取得した結果を返す
    return rows

def select_df(select_query, param=None):
    """
    mysqlでSELECT文を実行し、結果をpandasのDataFrameで返す。

    Args:
        select_query (str): SELECT文のクエリ文字列
        param (tuple/list, optional): クエリのパラメータ。デフォルトはNone。

    Returns:
        pandas.DataFrame: 実行結果のDataFrame
    """
    # データベースに接続
    conn = mysql.connector.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    # カーソルを取得
    cur = conn.cursor()
    # SELECT文を実行して結果を取得
    cur.execute(select_query, param)
    rows = cur.fetchall()
    # Dataflameに入れる
    df = pd.DataFrame(rows, columns=[col[0] for col in cur.description])
    
    # 接続を終了
    cur.close()
    conn.close()
    
    # 取得した結果をDataflameで返す
    return df

def exec(conn, sql, param=None):
    """
    SQL文を実行するための関数。

    Args:
        conn: データベースの接続オブジェクト
        sql (str): 実行するSQL文
        param (tuple/list, optional): SQLコマンドのパラメータ。デフォルトはNone。

    """
    # SQLを実行する。
    cur = conn.cursor()
    cur.execute(sql, param)
    
    cur.close()

def connect_db():
    """
    データベースに接続するための関数。
    """
    # データベースに接続してその接続オブジェクトを返す
    conn = mysql.connector.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, password=DB_PASSWORD, database=DB_NAME)
    return conn

def commit_tran(conn):
    """
    トランザクションをコミットするための関数。

    Args:
        conn: データベースの接続オブジェクト
    """
    # トランザクションをコミット
    conn.commit()

def rollback_tran(conn):
    """
    トランザクションをロールバックするための関数。

    Args:
        conn: データベースの接続オブジェクト
    """
    # トランザクションをロールバック
    conn.rollback()

def close_db(conn):
    """
    データベースの接続を閉じるための関数。

    Args:
        conn: データベースの接続オブジェクト
    """
    # 接続を閉じる
    conn.close()
    
    
    

##############################################################
# 関数のテスト
if __name__ == "__main__":

    ###################################
    #商品マスタへデータを作成する。
    dbconn = connect_db()

    ###DELETE
    strsql = "DELETE FROM 商品マスタ"
    exec(dbconn,strsql)

    ###INSERT
    strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格) VALUES('3301', 'チョコレート', 100)"
    exec(dbconn,strsql)

    ###INSERT プリペアードステートメント
    strsql = "INSERT INTO 商品マスタ (品番, 品名, 価格) VALUES(%s, %s, %s)"
    parameters = ("3302", "クッキー", 80)
    exec(dbconn,strsql,parameters)

    ###INSERT BLOB
    file_path = "c:\\testdb\\candy.png"
    with open(file_path, 'rb') as file:
        binary_data = file.read()
    strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格, 商品画像) VALUES(%s, %s, %s, %s)"
    parameters = ("3303", "キャンディ", 70, binary_data)
    exec(dbconn,strsql,parameters)

    commit_tran(dbconn)
    close_db(dbconn)


    ###################################
    #商品マスタを読み込みする。

    ###SELECT 
    print("\nSELECT")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = '3302'"
    ret = select(strsql)
    print(ret[0])

    ###SELECT プリペアードステートメント
    print("\nSELECT プリペアードステートメント")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s or 価格 >= %s"
    parameters = ("3302", 100)
    ret = select(strsql, parameters)
    for i in range(len(ret)):   
        print(ret[i])

    ###SELECT BLOB
    print("\nSELECT BLOB")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s"
    parameters = ("3303",) #パラメータが１つなのでタプルにする。
    ret = select(strsql, parameters)

    file_path = "c:\\testdb\\candy2.png"
    print(len(ret[0][3])) #ファイルサイズ
    with open(file_path, 'wb') as file:
        file.write(ret[0][3]) #バイナリファイルを書き込む
        

    ###SELECT BLOB Dataflame
    print("\nSELECT BLOB Dataflame")
    strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s"
    parameters = ("3303",) #パラメータが１つなのでタプルにする。
    retdf = select_df(strsql, parameters)

    file_path = "c:\\testdb\\candy3.png"
    print(len(retdf.iloc[0]["商品画像"]))
    with open(file_path, 'wb') as file:
        file.write(retdf.iloc[0]["商品画像"]) #バイナリファイルを書き込む

    #データフレームをEXCEL出力する。
    print("\nDataflame to_excel")
    retdf.to_excel("c:\\testdb\\candy4.xlsx", index=False, engine="openpyxl", sheet_name="candy")

実行結果

SELECT
('3302', 'クッキー', 80, None)

SELECT プリペアードステートメント
('3301', 'チョコレート', 100, None)
('3302', 'クッキー', 80, None)

SELECT BLOB
127579

SELECT BLOB Dataflame
127579

Dataflame to_excel

コンソールには上記実行結果が出力されますが、mediumblobにcandy.pngをINSERTした後にselect関数でcandy2.pngとして保存、select_df関数でcandy3.pngとして保存しEXCEL出力もしているので以下のようにファイルが増えます。

解説

commit処理について

dbconn = connect_db()でデータベースを開いてから、INSERT処理を行った後にcommit_tran(dbconn)を行いデータを確定させ、close_db(dbconn)でデータベースを閉じています。

MySQLはcommit処理にオーバーヘッドがかかるので多数のDMLを処理する場合には注意が必要です。

###################################
#商品マスタへデータを作成する。
dbconn = connect_db()

 .
 .
 .

commit_tran(dbconn)
close_db(dbconn)

プリペアードステートメント利用時の注意点について

###SELECT BLOB
print("\nSELECT BLOB")
strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s"
parameters = ("3303",) #パラメータが１つなのでタプルにする。
ret = select(strsql, parameters)

この処理ではparametersにタプルを渡していますが、パラメータが一つの場合には「,」を入れる必要があります。忘れがちですので記載するようにしましょう。

BLOBへの格納

INSERT時にはbinary_dataをパラメータとして引数に渡す必要があります。

###INSERT BLOB
file_path = "c:\\testdb\\candy.png"
with open(file_path, 'rb') as file:
    binary_data = file.read()
strsql = "INSERT INTO 商品マスタ(品番, 品名, 価格, 商品画像) VALUES(%s, %s, %s, %s)"
parameters = ("3303", "キャンディ", 70, binary_data)
exec(dbconn,strsql,parameters)

書き込みした結果はphpMyAdminを利用すると以下の図のように見えます。

BLOBから読み込み

SELECTしてファイルに戻す時にはfile_pathを指定してバイナリファイルを書き込む必要があります。

###SELECT BLOB
print("\nSELECT BLOB")
strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s"
parameters = ("3303",) #パラメータが１つなのでタプルにする。
ret = select(strsql, parameters)

file_path = "c:\\testdb\\candy2.png"
print(len(ret[0][3])) #ファイルサイズ
with open(file_path, 'wb') as file:
    file.write(ret[0][3]) #バイナリファイルを書き込む

BLOBから読み込み Dataflameの場合

select_dfでDataflameを利用する場合には以下の書き方となります。

###SELECT BLOB Dataflame
print("\nSELECT BLOB Dataflame")
strsql = "SELECT 品番, 品名, 価格, 商品画像 FROM 商品マスタ WHERE 品番 = %s"
parameters = ("3303",) #パラメータが１つなのでタプルにする。
retdf = select_df(strsql, parameters)

file_path = "c:\\testdb\\candy3.png"
print(len(retdf.iloc[0]["商品画像"]))
with open(file_path, 'wb') as file:
    file.write(retdf.iloc[0]["商品画像"]) #バイナリファイルを書き込む

#データフレームをEXCEL出力する。
print("\nDataflame to_excel")
retdf.to_excel("c:\\testdb\\candy4.xlsx", index=False, engine="openpyxl", sheet_name="candy")

また、to_excelを行った場合にも出力は可能ですが、candy.pngのようなバイナリ部分は
b’\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x03\xe8\x00\x00\x03…
のような出力となります。

最後に

以上がPythonでMySQLを操作する方法です。

PythonからMySQLを利用する場合mysql-connector-pythonを利用することにより簡単に利用できます。

データを大量に扱う場合や恒久的にデータを保存する場合でも無償で利用できるのでそのようなシーンで検討してみてはいかがでしょうか。

コメント

タイトルとURLをコピーしました