【計算列追加】PythonでMySQLテーブルに計算結果の列を追加する方法【Docker & Jupyterlab環境】

投稿日 2022/02/17

はじめに

全国地方公共団体コード、というものがある。
総務省が提供している。
元来、都道府県コード（2桁数字）と市区町村コード（3桁数字）があり、JIS X 0401とJIS X 0402として制定されてもいる。
全国地方公共団体コードはこの２つのコードを合わせた、5桁の数字である。

しかし、総務省提供の全国地方公共団体コードでは、なぜか6桁である。
他の、例えば郵便番号コードでは、全国地方公共団体コードが5桁であるのに、である。
実は、6桁の全国地方公共団体コードは、5桁に付随して下1桁に検査数字（チェックデジット）が付加されているのである。

今回は、全国地方公共団体コードの桁数を5桁に合わせるために、6桁の全国地方公共団体コードを5桁にし、新たな項目を作成してそこに5桁コードを記述する方法を紹介する。

コードの解説

import mysql.connector

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

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

cursor.execute("ALTER TABLE JLGcode_table ADD JLGcode_5 INT(5) UNSIGNED ZEROFILL GENERATED ALWAYS AS (SUBSTRING(全国地方公共団体コード, 1, 5)) STORED;")
db.commit()

1行目〜7行目は、をご覧あれ。

8行目：commit()は「トランザクションをコミットして、その変更を永続的なものに」する処理を行う。大抵はexecute()の直後に置いてexecute()で実行した司令を完了させる。詳細は参考サイトへ。→【2022.2.17追記】commit()の必要な時、不要な時も見よ。も見よ。

【2022.2.17追記】commit()の必要な時、不要な時｜【まとめ】PythonでMySQLにCSVファイルをインポートする【Docker & Jupyterlab環境】

10行目：

execute()内のSQL文「ALTER TABLE」はpostal_tableという名のテーブルに変更を行うという意味
「ALTER TABLE」に付随した「ADD」はpostal_tableに新しいカラムを追加するという意味
JLGcode_5は追加するカラム名（任意名）
INT(5)はカラムのデータ型を整数型と指定し、かつ桁数を5桁と指定
UNSIGNEDはINTが負数を使用せず正数のみ使用することを指定するが、下記の通りなので不要
ZEROFILLは、指定した桁数を満たすようにゼロで埋める。これを指定すると自動的にUNSIGNED属性が指定される
[GENERATED ALWAYS] AS (式)は、新たなカラムを生成させ、式の計算値を収納する。Excelでの関数定義されたカラムに似ている。
SUBSTRING(str, pos, len)は、str（文字列）において、指定されたpos（位置）からlen（文字数）分を抽出できる。この場合は、「全国地方公共団体コード」カラム全体から文字列左始めから5文字分抽出することになる。
STOREDは、カラムを追加した際、計算結果をカラムに実際に保存し、通常のカラムと同様に扱うことができる。

参照元

ZEROFILLとUNSIGNEDについて

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

数値カラムに ZEROFILL を指定すると、MySQL によって UNSIGNED 属性が自動的に追加されます。

[GENERATED ALWAYS] AS (式)について

MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20.8 CREATE TABLE および生成されるカラム

AS (expr) は、カラムが生成されることを示し、カラム値の計算に使用される式を定義します。カラムの生成された性質をより明確にするために、AS の前に GENERATED ALWAYS を付けることができます。

SUBSTRING(str, pos, len)について

MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.8 文字列関数および演算子

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
len 引数を付けない形式では、位置 pos で始まる文字列 str からの部分文字列が返されます。 len 引数を付けた形式では、位置 pos で始まる文字列 str からの部分文字列 len 文字長が返されます。