【画像保管計画】Pillow Image to Blob in MySQL

背景

　かつて誰もが為し得なかった神への道を目指し、僕はPythonで動画サイトのサムネ画像をスクレイピングで色々収集してまして、それをPillowモジュールで加工したものをMySQLで作ったテーブルのBlobカラムに保管しておきたいという必要に迫られました。画像の数は大量にあるので、ストレージの節約のため、いちいちファイルに書き出したりせずにオンメモリでBlobカラムに挿入する方法が欲しかった。その過程で調べて得られた手順をここに残しておきます。

BLOBフィールド、展開！

以下のようなBLOB型のカラムimgfileを含んだimg_materialテーブルをMySQLのDBで定義しておきます。

CREATE TABLE `img_material` (
  `video_id` char(11) NOT NULL DEFAULT '' COMMENT '動画ID',
  `imgfile` blob DEFAULT NULL COMMENT '画像ファイル'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

P.I.L.緊急排出！！

次のように指定したURLからrequestsで取得した画像があり、それをpillowのImageオブジェクトに格納し、トリミング加工をしています。

from PIL import Image
import urllib.request as req
import io

thumbnail_url = "https://xxx.xxx.xxx/xxxxxx.jpg" # 取り込みたいサムネ画像のURL
f = io.BytesIO(req.urlopen(thumbnail_url).read())#URLからサムネ画像を展開
thumbnail = Image.open(f).convert('RGB') #Imageオブジェクトに格納
thumbnail = thumbnail.crop((0, 11, 120, 79))#黒帯部分をトリミング

一旦Imageオブジェクトに変換して加工しましたが、これを上記のテーブルのBlobカラムであるimgfileへ格納したい！なのでもう一度ByteIOによりバイナリデータに戻します。

imgdata = io.BytesIO()
thumbnail.save(imgdata, "JPEG") 
imgdata.seek(0) #ファイルの先頭へ
imgfile = imgdata.read() #バイナリデータを格納

こうしてできたimgfileを先ほど定義したBlobカラムのあるテーブルへINSERTします。

#挿入、カラム並べて
MySQLdbにより、DBへの接続を行います。db_configの中身は、ご自分の環境に合わせて適宜変えてください。

import MySQLdb

# DB設定
db_config = {
    'host': 'localhost',
    'db': 'database_name',
    'user': 'root',
    'passwd': 'your_password',
    'charset': 'utf8',
}

# 接続する
conn = MySQLdb.connect(
  host=db_config['host'], 
  db=db_config['db'], 
  user=db_config['user'],
  passwd=db_config['passwd'], 
  charset=db_config['charset']
  )

上記ののコネクションから得られたカーソルにより、先述のimgfileを挿入するINSERT文を実行します。

# SQLクエリ
SQL_INSERT_IM = '''
  insert into img_material
  values(%s,%s)
'''

# DB操作用にカーソルを作成
cur = conn.cursor()

try:
    # 挿入データをタプルに格納
    insert_blob_tuple = ("VIDEO000001", imgfile)
    # INSERT文を実行
    result = cur.execute(SQL_INSERT_IM , insert_blob_tuple)
    conn.commit()
    print("Image data inserted successfully as a BLOB into table", result)

except MySQLdb.Error as error:
    print("Failed inserting BLOB data into MySQL table {}".format(error))

finally:
    if conn.open:
        cursor.close()
        conn.close()
        print("MySQL connection is closed")

参考資料

Image Module — Pillow (PIL Fork) 7.2.0 documentation
https://pillow.readthedocs.io/en/stable/reference/Image.html
io --- ストリームを扱うコアツール — Python 3.8.6 ドキュメント
https://docs.python.org/ja/3/library/io.html
BLOB 型と TEXT 型
https://dev.mysql.com/doc/refman/5.6/ja/blob.html
裏死海文書
https://w.atwiki.jp/evacommu/pages/92.html