MySQL + Python で utf8mb4 対応テーブルの挙動を確認する

概要

• MySQL に文字セット utf8mb4 に設定したテーブルを作成して、Python 3 からデータ操作(参照・追加・更新・削除)を試す
• 環境: GMOデジロックのレンタルサーバー「コアサーバー」 + MySQL 5.7 + Python 3.6 + mysqlclient (MySQLdb)

MySQL のバージョンを確認

$ mysqld --version
mysqld  Ver 5.7.27 for Linux on x86_64 (MySQL Community Server (GPL))

MySQL の文字セットと照合順序を確認

起動している MySQL デーモンの文字セットと照合順序の設定を MySQL monitor にて確認。

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 5.1.4 サーバーシステム変数
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.4 接続文字セットおよび照合順序
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14.1 Unicode 文字セット

データベースを作成

MySQL monitor にてデータベースを作成。

mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test_db            |
+--------------------+
2 rows in set (0.01 sec)

mysql> use test_db;
Database changed

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.10 CREATE DATABASE 構文

テーブルを作成

テーブル作成用の SQL を書いてファイルに保存。

create-table.sql

CREATE TABLE IF NOT EXISTS test_table (
  id INTEGER NOT NULL AUTO_INCREMENT,
  name VARCHAR(256) NOT NULL,
  PRIMARY KEY (id)
)
ENGINE=InnoDB
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

mysql コマンドでテーブルを作成。

$ mysql -h localhost -u alice -p test_db < create-table.sql

MySQL monitor にてテーブルを確認。

mysql> show tables from test_db;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_table        |
+-------------------+
1 row in set (0.00 sec)

mysql> show create table test_db.test_table;
+------------+-----------------------------------------------
| Table      | Create Table                                  
+------------+-----------------------------------------------
| test_table | CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+------------+-----------------------------------------------
1 row in set (0.00 sec)

参考:

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.1.17 CREATE TABLE 構文
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 3.5 バッチモードでの MySQL の使用

Python のバージョンを確認

$ python3 --version
Python 3.6.8

Python のパッケージ mysqlclient をインストール

mysqlclient は Python 2 で使えるパッケージ MySQL-python (MySQLdb1) から派生して Python 3 に対応したパッケージ。

今回は mysqlclient のバージョン 1.3.4 を使う。

$ python3 -m pip install mysqlclient==1.3.4

今回の動作確認環境であるGMOデジロックのレンタルサーバー「コアサーバー」では該当バージョンがすでにインストール済みだった。

$ python3 -m pip list | grep mysqlclient
mysqlclient            1.3.4  

参考:

• Python モジュールのインストール — Python 3.6.10rc1 ドキュメント
• GitHub - PyMySQL/mysqlclient-python: MySQL database connector for Python (with Python 3 support)
• GitHub - farcepest/MySQLdb1: MySQL database connector for Python (legacy version)

Python + mysqlclient でデータ操作

データを追加・取得・更新・削除する Python のソースコードを用意。

crud.py

# mysqlclient を使う
import MySQLdb

# connect db
connection = MySQLdb.connect(host="localhost",
                             user="alice",
                             passwd="your-password",
                             db="test_db",
                             charset="utf8mb4")
cursor = connection.cursor(MySQLdb.cursors.DictCursor)

# insert
insert_sql = "INSERT INTO test_table (name) VALUES (%s)"
cursor.execute(insert_sql, ("寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで",))
print(f"insert count={cursor.rowcount}")
connection.commit()

# select
select_sql = "SELECT id, name FROM test_table"
cursor.execute(select_sql)
print(f"select count={cursor.rowcount}")
for row in cursor:
  print(f"{row['id']}: {row['name']}")
  target_id = row["id"]

# update
update_sql = "UPDATE test_table set name=%s WHERE id=%s"
cursor.execute(update_sql, ("Alice", target_id))
print(f"update count={cursor.rowcount}")
connection.commit()

# delete
delete_sql = "DELETE FROM test_table WHERE id=%s"
cursor.execute(delete_sql, (target_id,))
print(f"delete count={cursor.rowcount}")
connection.commit()

connection.close()

実行結果。
寿司ビール絵文字などが文字化けせずに追加・取得・表示できている。

$ python3 crud.py 
insert count=1
select count=1
1: 寿司ビール🍣🍺𩸽🐟🐠🐡文字化けしないで
update count=1
delete count=1

参考:

• Welcome to MySQLdb’s documentation! — MySQLdb 1.2.4b4 documentation
• 【Python3】MySQL 操作をひと通りマスター！導入方法とCRUDサンプルコード集 | ITエンジニアラボ
• Python3でMySQLを使う – 基本操作からエラー処理までサンプルコード付 | Crane & to.

テーブルとデータベースを削除

MySQL monitor から使い終わったテーブルとデータベースを削除。

mysql> drop table test_db.test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database test_db;
Query OK, 0 rows affected (0.00 sec)