【完成版】PythonでMySQLにCSVファイルをインポートする【Docker & Jupyterlab環境】

はじめに

今回は、郵便番号データをMySQLに追加する操作をPythonでしてみようと思う。
いきなり10万件超のデータを処理してみる。

Docker ComposeとDockerfileの解説は、別ブログで解説する。
PythonとMySQLのコマンド解説も、各項目ごとの別ブログで解説する。
簡単な説明は、コマンド内にコメントする。

必要なもの

目次の項目中、【必要】【ファイル】【フォルダ】【コマンド】を揃えることになる。

開発環境

Python、MySQL、phpMyAdminは、Docker内に環境を整備し、Jupyterlabで操作していく。

• マシン：MacBook Pro
• OS：macOS Big Sur（11.6.3）
• 仮想環境：Docker Desktop（4.4.2 (73305)）
• エディタ：JupyterLab（3.2.9）、Sublime Text（Build 4126）
• python：3.8.10
• MySQL：8.0.28
• phpMyAdmin：5.1.3（TerminalでMySQLを確認するより楽なので導入）

【必要】Docker Desktopをインストール

Docker Desktopをインストールしていない場合は、下記ブログの1と2を参考にしていただければ。

MacにDockerをインストールしPythonが使えるまでのまとめ【初心者向け】

初心者がDockerをインストールするのはハードルが高いですよね。初心者である筆者がDockerのインストール方法を調べて試してみたのでその手順を公開します。用語については、理解しないままとりあえず放置します。理解しようと用語を調べだしたらそれに時間を取られてインストールに進めないので。

blog.eszett-design.com

【ファイル】MySQLのDockerの構築と階層構造

以下のブログをご覧あれ。
phpと、MySQLと、phpMyAdminの各々のContainerを構築する。
phpMyAdminを導入すると、MySQLの確認が楽である。

【完成版】DockerにPHP＋Apache＋MySQL開発環境を設定してみた

初心者がDockerにPHP＋Apache＋MySQL開発環境をDocker Composeにまとめ、検証ファイルまで作成するまでの長いお話。

blog.eszett-design.com

準備が大変に思われるかもしれないが、ゆっくり作業していただきたい。

【フォルダ】python用の階層構造

ファイルのディレクトリは以下の通りに予め設定する。

desktop
  └python_csvフォルダ
  　　　　　├Docker Composeファイル
  　　　　　├Dockerfile
  　　　　　└workフォルダ
            └pythonファイル（Jupyterlabで作成・保存するまで生成しない）

php・MySQL・phpMyAdmin用のフォルダも別途必要。
詳細は上記【ファイル】MySQLのDockerの構築と階層構造をご覧あれ。

pythonのDockerを構築

【ファイル】Docker Compose

version: "3.8"
services:
  web:
    build:
      context: .
      dockerfile: Dockerfile
    environment:
      - DISPLAY=host.docker.internal:0.0
    volumes:
      - ./work:/work
    ports:
      - 8888:8888
    image: python_csv:latest
    container_name: python_csv
    command:
      jupyter lab
        --ip=0.0.0.0
        --allow-root
        --notebook-dir='/work'
        --LabApp.token=''

Docker Composeの中身の解説は、以下をご覧あれ。

【完成版】MacでJupyterlabからPythonのGUIライブラリが使えるDockerの設定方法

PythonのGUIライブラリ（Tkinter、pillow、openpyxl）をDocker Container内にインストールし、JupyterlabからXQuartzを介して稼働させる方法と、Docker ComposeとDockrfileの作成方法を紹介します。

blog.eszett-design.com

php・MySQL・phpMyAdmin用のDocker Composeも別途必要。
詳細は上記【ファイル】MySQLのDockerの構築と階層構造をご覧あれ。

【ファイル】Dockerfile

FROM ubuntu:latest

ENV DEBIAN_FRONTEND=noninteractive

RUN apt-get update && apt-get install -y \
		python3 \
		python3-pip

RUN pip3 install jupyterlab \
		pandas \ # 本来はデータ分析（データサイエンス）用のライブラリだが、Excel操作用にも使用可能
		mysql-connector-python # pythonからMySQLにアクセスするためのライブラリ。正式名称とインストール用名称、pythonにインポート用名称がそれぞれ異なる。

Dockerfileの中身の解説は、以下をご覧あれ。

【完成版】MacでJupyterlabからPythonのGUIライブラリが使えるDockerの設定方法

PythonのGUIライブラリ（Tkinter、pillow、openpyxl）をDocker Container内にインストールし、JupyterlabからXQuartzを介して稼働させる方法と、Docker ComposeとDockrfileの作成方法を紹介します。

blog.eszett-design.com

今回のDockerfileは、上記ブログよりもインストールするものは少ない。

php・MySQL・phpMyAdmin用のDockerfileも別途必要。
詳細は上記【ファイル】MySQLのDockerの構築と階層構造をご覧あれ。

JupyterLabのガイド
ブラウザ上で動作するプログラムの対話型実行環境。要はブラウザ上で動作するエディタ。コードを書いて即実行できる。

JupyterLab Documentation — JupyterLab 3.3.0b0 documentation

JupyterLab is the next-generation web-based user interface for Project Jupyter.

jupyterlab.readthedocs.io

MySQL Connector/Pythonのガイド
MySQLのコードをpythonで稼働可能にする。

MySQL :: MySQL Connector/Python Developer Guide

This manual describes how to install and configure MySQL Connector/Python, a self-contained Python driver for communicating with MySQL servers, and how to use it to develop database applications.

dev.mysql.com

pandasのガイド
CSVでなくExcelファイルを扱う時に使用。ExcelからCSVに変換できるコードがある。

pandas - Python Data Analysis Library

pandas is a fast, powerful, flexible and easy to use open source data analysis and manipulation tool, built on top of the Python programming language.

pandas.pydata.org

Docker ImageのビルドとContainerのラン

Terminalを起動し、TerminalにてDocker Composeのある階層に移動する。

$ cd ~/Desktop/python_csv

次に、TerminalにてDocker Imageをビルドし、かつContainerをランする。

$ docker-compose up -d --build

Docker DesktopにImageとContainerが現れていたら成功。

php・MySQL・phpMyAdmin用のDocker ImageとContainerも別途必要。
詳細は上記【ファイル】MySQLのDockerの構築と階層構造をご覧あれ。

使用するもの

phpMyAdmin

phpMyAdminを立ち上げるのは、以下の画像内の左端のボタンをクリックする。するとブラウザ上にphpMyAdminが現れる。

JupyterLab

JupyterLabを立ち上げるのは、以下の画像内の左端のボタンをクリックする。するとブラウザ上にJupyterLabが現れる。

JupyterLabが立ち上がったら、上の画像内一番上「Notebook」の「Python 3」ボタンをクリックする。するとコード編集画面が現れる。

以上で、pythonコードを書く準備が整った。

MySQLにインポートするファイル形式とファイルのダウンロード

CSV形式にしてMySQLにインポートする。
郵便番号データはCSVファイルである。
ただし、郵便番号データのエンコーディング形式がShift-JISであることに注意。

Excel形式ファイルは、pandasでCSVに変換する。

郵便番号データのダウンロードは以下から。

郵便番号データダウンロード - 日本郵便

www.post.japanpost.jp

青枠で囲ったリンクをクリック。

青枠で囲ったリンクをクリックすると、郵便番号データがダウンロードされる。
ファイルは、とりあえず分かりやすいところに置いておく（後に、置き場所を定める）。

【コマンド】MySQLにデータベースを新規作成

MySQL Connector/Pythonを使用して、MySQLにデータベースを新規作成する。
新規データベースは、データを収納する大元になる。
pythonコマンドではMySQL Connector/Pythonは、「import mysql.connector」と指定する。

# Database新規作成
import mysql.connector
 
db=mysql.connector.connect(host="host.docker.internal", user="root", password="root")
cursor=db.cursor()

cursor.execute("CREATE DATABASE default_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")

7行目、作成するデータベースのエンコーディング形式と照合順序をそれぞれ標準のutf8mb4とutf8mb4_general_ciを指定していることに注意（郵便番号データのエンコーディング形式と異なっている）。

「default_db」という名のデータベースが作成された。

【コマンド】MySQLにテーブルを新規作成

同じく、MySQL Connector/Pythonを使用して、MySQLにテーブルを新規作成する。
新規テーブルに、実際のデータを収納する。

郵便番号データには、項目名が含まれていないので、テーブル作成時に項目名を追加する。
項目名は、下記サイトを参考にした。

郵便番号データの説明 - 日本郵便

www.post.japanpost.jp

# Table新規作成
import mysql.connector
 
db=mysql.connector.connect(host="host.docker.internal", user="root", password="root")
cursor=db.cursor()

# データベースを選択
cursor.execute("USE default_db") #先に作成したデータベース名を指定

# Table新規作成
cursor.execute("DROP TABLE IF EXISTS postal_table;")
cursor.execute("CREATE TABLE IF NOT EXISTS postal_table (
    全国地方公共団体コード INT(5) UNSIGNED ZEROFILL NOT NULL,
    （旧）郵便番号（5桁） INT(5) UNSIGNED ZEROFILL NOT NULL,
    郵便番号（7桁） INT(7) UNSIGNED ZEROFILL NOT NULL,
    ﾄﾄﾞｳﾌｹﾝﾒｲ TEXT NOT NULL,
    ｼｸﾁｮｳｿﾝﾒｲ TEXT NOT NULL,
    ﾁｮｳｲｷﾒｲ TEXT NOT NULL,
    都道府県名 TEXT NOT NULL,
    市区町村名 TEXT NOT NULL,
    町域名 TEXT NOT NULL,
    stat1 char(1) NOT NULL,
    stat2 char(1) NOT NULL,
    stat3 char(1) NOT NULL,
    stat4 char(1) NOT NULL,
    stat5 char(1) NOT NULL,
    stat6 char(1) NOT NULL
    ) ENGINE InnoDB CHARACTER SET cp932 COLLATE cp932_japanese_ci;")

29行目、作成するテーブルのエンコーディング形式と照合順序をそれぞれcp932とcp932_japanese_ciを指定していることに注意（郵便番号データおよび新規データベースのエンコーディング名称と異なっている）。

後で紹介する【コマンド】MySQLテーブルの削除は、自覚して作成済みテーブルを削除するコードであるが、
上記12行目にも「DROP TABLE IF EXISTS postal_table」を挿入している。
これは、テーブルを残したまま、同じ名のテーブルを作成するのを防ぐ目的のため。
なおかつ13行目は、「CREATE TABLE postal_table」とせず、「CREATE TABLE IF NOT EXISTS postal_table」として、同じ名のテーブルを作成するのを防ぐようにもしている。

【2022.2.17追記】
13〜15行目、MySQLリファレンスより、「数値カラムに ZEROFILL を指定すると、MySQL によって UNSIGNED 属性が自動的に追加され」るということで、UNSIGNEDは削除した。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 11.1.6 数値型の属性

dev.mysql.com

「postal_table」という名のテーブルが、「default_db」内に作成された。

「postal_table」の項目名も作成されている。

CSVデータのMySQLのテーブルへのインポートを試みる

SQL文の「LOAD DATA LOCAL」では…

ここで数多の時間を消費した。
検索すると大抵は、SQL文の「LOAD DATA LOCAL」でCSVデータをMySQLにインポートする解説がされている。
が、このコマンドでは上手くいかなかった。

3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides

上記エラーが発生。これは、phpMyAdminではなくJupyterlab上で出現し、CSVデータはインポートされない。
というのも、今回使用しているMySQLはバージョン8系で、先のコマンドはデフォルトでは「local_infile」というサーバーシステム変数がOFFになっていて使用できないようになっている。
下記を参照すればインポート出来るらしいが、セキュリティ上のリスクになる可能性があるらしい、というのでやめておく。

MySQL 8.0 の LOAD DATA で The used command is not allowed with this MySQL version エラー - mita2 database life

TL;DR MySQL 8.0 で LOAD DATA INFILE LOCAL を利用するには、 サーバとクライアント両方で local-infile パラメータを ON にする必要がある セキュリティ強化のため、8.0から LOAD DATA INFILE LOCAL はデフォルトで無効にされた MySQL 8.0 で LOAD DATA INFILE LOCAL が通らない Loading local data is disabled; this must be enabled on both the client and server sides エラー*1で LOAD DATA IN…

mita2db.hateblo.jp

phpMyAdminから直接インポート…

先にDocker Containerで構築したphpMyAdminから直接インポートを試みるも、各種エラー出現や、項目名の追加が途中までしか出来なくて、上手くいかなかった。

phpMyAdminの「インポート」項目から、郵便番号コードCSVファイルを選択しインポートを実行するが、

上のようなエラーメッセージが出て、

本来12万件超あるデータのうち、上の赤枠の件数しかインポートされず。

「LOAD DATA」でのインポートは…

で、よくよく考えたら、php/MySQL/phpMyAdmin用のDocker Composeファイルで、下の画像の赤枠の通り、ローカル側の「/mysql/sql」とサーバー側の「/docker-entrypoint-initdb.d」を紐付けているので、ローカル側の「/mysql/sql」フォルダにCSVファイルを入れると、サーバー内にあると認識するので、実質CSVファイルはサーバー側にあると見なせる。

つまりは、SQL文は「LOAD DATA LOCAL」ではなく「LOAD DATA」が使用できるはずである。

で、試した結果、サーバー側からインポートもエラーが出て上手くいかない。

ERROR 1290 (HY000): 〜のエラーとその解決策…

【ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement】

上記エラーを調べ、その解決法を見つける。

MySQL8(多分それ以前のバージョンでも)にCSVデータをインポートするときのベストな方法 - Qiita

前提
この記事は以下のような方にお勧めいたします。

• DBの開発中で、MySQLサーバーとクライアントは同じマシン
• MySQLでcsvファイルから大量のデータを入力したい
• MySQL Workbenchからのインポートは遅すぎると苛立ちがちの方
• "ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"と表示されて実行できない方

qiita.com

上記から、下の画像の赤枠の通り、MySQL用の設定ファイルであるmy.cnfの[mysqld]に以下を追加しrestart。

secure_file_priv = /docker-entrypoint-initdb.d
	# Docker Composeにて「/mysql/sql」と紐付けたディレクトリ

「secure_file_priv = （ディレクトリ名）」と指定すると、「ディレクトリの名前に設定すると、サーバーはインポートおよびエクスポート操作をそのディレクトリ内のファイルでのみ機能するように制限」出来る。

故に、「secure_file_priv = /docker-entrypoint-initdb.d」の設定により、Docker Composeにてホスト側の「/mysql/sql」とサーバー側の「/docker-entrypoint-initdb.d」を紐付け設定を確定させている。

これで、「/mysql/sql」に入れたCSVファイルは「/docker-entrypoint-initdb.d」にあるという認識が確定し、インポート可能になる。

secure_file_priv | MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.8 サーバーシステム変数

この変数は、LOAD DATA ステートメント、SELECT ... INTO OUTFILE ステートメントおよび LOAD_FILE() 関数によって実行される操作など、データインポートおよびエクスポート操作の影響を制限するために使用されます。 これらの操作は、FILE 権限を持つユーザーにのみ許可されます。

dev.mysql.com

別のエラーERROR 1300 (HY000): 〜の発生とその解決策…

先のエラーの解決策を施し、再度SQL文：LOAD DATAをしたが、別のエラーが出て上手くいかない。

【ERROR 1300 (HY000): Invalid utf8mb4 character string】

そのエラーを調べ、その解決法を見つける。

MySQL ERROR 1300 (HY000): Invalid utf8mb4 character string 文字コード設定エラー解決方法 | BACK NUMBER

データのSJISがutf8mb4環境で文字化けして、utf8mb4が認識しない謎の文字列が発生していたためのエラー
全ての文字コードを揃える必要が一時的に必要である

back-number.com

ここで、最初の方で注意していた文字エンコーディング形式の相違の問題が、ここで出てくるのである。

SQL文：LOAD DATAの直前に、「set character_set_database=cp932;」を追加する（次項「【コマンド】CSVデータをMySQLのテーブルにインポート」の、完成したコードの11行目）。

再々度SQL文：LOAD DATAで、見事にインポート出来た。

上記赤枠の通り、全ての件数がインポートされた。

【コマンド】CSVデータをMySQLのテーブルにインポート

以下が完成した、CSVデータをMySQLのテーブルにインポートするコマンド。

# CSVをインポート
import mysql.connector
 
db=mysql.connector.connect(host="host.docker.internal", user="root", password="root")
cursor=db.cursor()

# データベースを選択
cursor.execute("USE default_db")

cursor.execute("set character_set_database=cp932;")
cursor.execute("LOAD DATA INFILE '/docker-entrypoint-initdb.d/KEN_ALL.CSV' INTO TABLE postal_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"';")

db.commit()

最初、郵便番号コードはShift-JIS形式だ、といったが、正確には恐らくそのWindows用であるcp932形式と推定した。

理由は、郵便番号コードCSVファイルをテキストエディタで確認したら、改行コードが「CR+LF (Windows)」（miエディタで確認）だったから。
（因みに、OS 9以前のMacの改行コードは「CR」、OS XとLinuxの改行コードは「LF」）

上記画像の赤枠が、郵便番号コードの文字エンコーディング形式と改行コードを表示している。
因みに、青枠は郵便番号コードの件数。

その他追加したもの

【コマンド】MySQLテーブルに新規列追加

CSVデータをインポートしたMySQLのテーブルにid列を追加する。
Keyとなるid列を追加すると、phpMyAdminの画面で各行の編集などが出来るようになる。

# 新規列追加
import mysql.connector

db=mysql.connector.connect(host="host.docker.internal", user="root", password="root")
cursor = db.cursor()

# データベースを選択
cursor.execute("USE default_db")

cursor.execute("ALTER TABLE postal_table ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL FIRST;")

MySQLのコマンドなどは以下のエントリで詳説しているのでご覧あれ。

PythonでMySQLテーブルにid列を追加する方法【Docker & Jupyterlab環境】

郵便番号データを、Docker & Jupyterlab環境下で、MySQLに追加する操作をPythonでした中で、id列を追加する方法を解説。

blog.eszett-design.com

id列を設定していない時。編集などが出来ない旨の注意書きが出ている。

MySQLテーブルに新規id列追加後の画面。赤枠の通り、編集などが出来るようになった。
id番号は、自動的に振られる。

【コマンド】MySQLテーブルの削除

不具合等のため、インポートしたテーブルを削除したい時用に用意。
削除後、再度「MySQLにテーブルを新規作成」以下を実行する。

# Tableを削除する
import mysql.connector
 
db=mysql.connector.connect(host="host.docker.internal", user="root", password="root")
cursor=db.cursor()

# データベースを選択
cursor.execute("USE default_db")

cursor.execute( "DROP TABLE postal_table;")

先の【コマンド】MySQLにテーブルを新規作成のコード内にも、念の為「"DROP TABLE postal_table;"」を挿入している。

【2022.2.17追記】commit()の必要な時、不要な時

今回のコマンドでは、【コマンド】CSVデータをMySQLのテーブルにインポートの最後にcommit()があるのみで、他のコマンドには書かれていない。そのコマンドでは、「LOAD DATA」を使用しており、かつ【コマンド】MySQLにテーブルを新規作成時に「ENGINE InnoDB」を指定しているため、commit()が必要になるらしい。

そもそもcommit()は、「トランザクション処理を完了させる」コマンドだが、MySQL構文の中には自動的に処理をcommitさせるものがある。そして「LOAD DATA」以外はそれに該当しcommit()は不要であるらしいので、commit()は書いていない。

MySQL構文の「USE」は、この件については明示されていないが、commit()なしでも大丈夫だったのでcommit()は書いていない。

「LOAD DATA」は、「NDBストレージエンジンを使用するテーブルに対してのみ暗黙的なコミットが発生」する、とのことなので、「ENGINE InnoDB」を指定した今回はcommit()は必要ということになる。
実際、commit()なしでコマンドを実行してみたが、何の変化もなかった。が、その直後にcommit()を実行したら、処理が完了した。そういうことなのである。

詳細はMySQLのマニュアルをご覧あれ。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント

このセクションに示されているステートメント (およびそのすべてのシノニム) は、ユーザーがこのステートメントを実行する前に COMMIT を実行したかのように、現在のセッション内でアクティブなすべてのトランザクションを暗黙的に終了します。

dev.mysql.com

他の参考サイト

MySQL 8.0に、LOAD DATA、Parallel Table Import Utility（MySQL Shell）、JDBCでCSVロードしてみる - CLOVER🍀

これは、なにをしたくて書いたもの？ MySQLにCSVロードをしようとした時に、LOAD DATAを使ったり、ふつうにINSERT文を使ったりといくつか方法が あると思うのですが、どれくらい違うものだろう？ということで試してみることにしました。 お題 CSVファイルを用意して、以下の方法を試してみます。 LOAD DATA（正確には、LOAD DATA LOCAL） Parallel Table Import Utility（MySQL Shell） CSVを読み込み、INSERT文を実行するJavaプログラム シンプルにINSERTを実行 JDBCでのバッチ更新 rewriteBatched…

kazuhira-r.hatenablog.com