MySQLでCSVファイルからデータベースを作る

MySQLをインストールして、CSVファイルからテーブルを作るところまでやってみた過程を記録する。
きっかけは、趣味で計測している時間の使い方記録が11,000行を超え、Excelによる取り回しが重くなってきていたこと。CSVのままPythonで操作するのでもよかったけど、せっかくなのでデータベース化をやってみた。

バージョン

  • macOS 10.13.6
  • MySQL 8.0.15

具体的な操作

以下、リンク先は公式ドキュメントの参照したページ

1 – MySQLのインストール

まず、homebrewでMySQLをインストールした。

$ brew install mysql

(前略)
==> Caveats
We've installed your MySQL database without a root password. To secure it run:
mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
mysql -uroot

To have launchd start mysql now and restart at login:
brew services start mysql
Or, if you don't want/need a background service you can just run:
mysql.server start
(後略)

一番目に書かれている$ mysql_secure_installationをやろうとする。

$ mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
Error: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

パスワードの設定をしていなかったので、Enter password for user root:でそのままEnterを押したが、エラーになった。

can-not-connect-to-server

A MySQL client on Unix can connect to the mysqld server in two different ways: By using a Unix socket file to connect through a file in the file system (default /tmp/mysql.sock), or by using TCP/IP, which connects through a port number.

とあり、接続方法の一つであるソケット接続に失敗したようだ。

結局、MySQLの起動($ mysql.server start)が必要だったようで、その後なら$ mysql_secure_installationを行えた。

$ mysql.server start

Starting MySQL
........ SUCCESS!
$ mysql_secure_installation

Securing the MySQL server deployment.
(長いので後略)

これにより、どのようにセキュリティが向上するかがmysql-secure-installation に書かれている。

  • anonymousユーザー(匿名ユーザー)の削除
  • リモートホストからroot ユーザー(MySQLの操作に対し全権限を持つユーザー)でのログイン禁止
  • testデータベースの削除

などを行った。

2 – MySQLに接続する

$ mysql -u user -pという形で接続する。-uでユーザー名を指定し、-pとすると Enter password: というプロンプトが表示されるのでパスワードを入力する。

rootユーザーでつないでみる。

$ mysql -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.15 Homebrew

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

つながった!

どんなデータベースがあるか見てみる(testデータベースが消えていることが確認できる)。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.06 sec)

3 – データベースを作る

lifeというデータベースを作り、その中にtime_bookというテーブルを作成する。

データベースを作る

mysql> CREATE DATABASE life;
Query OK, 1 row affected (0.08 sec)

テーブルを作る

mysql> CREATE TABLE life.time_book(
-> id INTEGER PRIMARY KEY AUTO_INCREMENT,
-> start_datetime DATETIME NOT NULL,
-> end_datetime DATETIME NOT NULL,
-> duration TIME NOT NULL,
-> content VARCHAR(24) NOT NULL,
-> memo VARCHAR(60)
-> );

Query OK, 0 rows affected (0.69 sec)

CREATE TABLE db_name.table_name(col_name data_type, …);という形で作れる。

  • data_typeはデータ型 今回使用しているのはDATETIME(日付と時刻),TIME(時刻),VARCHAR(可変長文字列)
  • NOT NULL:そのカラムにはNULLを格納することができなくなる
  • PRIMARY KEY:主キー制約 行の一意性を確保する
  • AUTO_INCREMENT:連番を自動的に振る

テーブル一覧を確認する

mysql> SHOW TABLES FROM life;
+----------------+
| Tables_in_life |
+----------------+
| time_book      |
+----------------+
1 row in set (0.09 sec)

テーブルのカラム一覧を確認する

mysql> SHOW COLUMNS FROM time_book FROM life;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| start_datetime | datetime    | NO   |     | NULL    |                |
| end_datetime   | datetime    | NO   |     | NULL    |                |
| duration       | time        | NO   |     | NULL    |                |
| content        | varchar(24) | NO   |     | NULL    |                |
| memo           | varchar(60) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
6 rows in set (0.06 sec)

4 – CSVファイルからデータを挿入する

まずデータベースを選択する。

mysql> use life;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

CSVを準備する
~/time_book.csv

id,start_datetime,end_datetime,duration,content,memo
1,2017-01-02 09:45,2017-01-02 10:34,00:49,'移動',
2,2017-01-02 10:34,2017-01-02 11:34,01:00,'家事','料理'
…

データをローカルから読み込もうとする。

mysql> LOAD DATA LOCAL INFILE '~/time_book.csv'
-> INTO TABLE time_book
-> FIELDS TERMINATED BY ','
-> IGNORE 1 LINES;

ERROR 1148 (42000): The used command is not allowed with this MySQL version
  • FIELDS TERMINATED BY ','はカンマ区切りを指定している
  • IGNORE 1 LINESはCSVの1行目がカラム名なので、読み込まないようにするため

エラーになった。
load-data-localを参照すると

  • セキュリティの問題から、デフォルトではLOAD DATA LOCALを使えないようにしてある
  • サーバー側、クライアント側それぞれで許可の設定が必要

なことがわかった。

サーバーサイド

The local_infile system variable controls server-side LOCAL capability. Depending on the local_infile setting, the server refuses or permits local data loading by clients that have LOCAL enabled on the client side. By default, local_infile is disabled.

option-modifiers

The “enabled” form of the option may be specified in any of these ways:
(中略)
–column-names=1

とあるので、1にすれば有効になる。

mysql> SET PERSIST local_infile = 1;
Query OK, 0 rows affected (0.08 sec)

SET文でシステム変数を変更できる(再起動すると設定した内容が失われる)。 SET PERSISTでパラメーターの値を設定すると、再起動後も値が保持される。

mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
| 1              |
+----------------+
1 row in set (0.00 sec)

クライアントサイド

For the mysql client, local data loading is disabled by default. To disable or enable it explicitly, use the –local-infile=0 or –local-infile[=1] option.

接続時に–local-infile=1と指定する
$ mysql -u root --local-infile=1 -p

サーバー側、クライアント側それぞれで設定できたので、改めてLOAD DATA LOCAL INFILEしたところ、先ほどのエラーは出なくなったが、以下の結果になった。

Query OK, 0 rows affected (0.10 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0

OKって出てるけど、Records: 0ということはつまり…中身を確認する。

mysql> select * from time_book;
Empty set (0.04 sec)

やっぱり読み込まれていない。

試行錯誤しながら問題がある部分を修正していったので、以下1つずつ示す。

datetimeとtimeの型の部分を””で囲っていなかった

最初はLOAD DATA LOCAL INFILEでやっているのが原因かと思い、試しにinsert文で読み込めるかやっていた中で気づいた。

mysql> insert into time_book (start_datetime,end_datetime,duration,content,memo) values(1990-01-01 00:00, 1990-01-01 00:01, 00:01:00, "a","b");

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00, 1990-01-01 00:01, 00:01:00, "a","b")' at line 1
mysql> insert into time_book (start_datetime,end_datetime,duration,content,memo) values("1990-01-01 00:00", "1990-01-01 00:01", "00:01:00", "a","b");
Query OK, 1 row affected (0.03 sec)

“”で日時を囲ったら読み込めた。ごく基本的な話なんだけど、わかっていなかった。
CSVファイルを修正し、LOAD DATA LOCAL INFILEにFIELDS ENCLOSED BY "'"を追加した(これは要素を囲むのに’ ‘を使っていることを指定している)。

文字コードを変えた

まず、MySQL側での文字コードを確認する。

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

utf8mb4って何…?と思い内容を見てみる。

mysql> SHOW CHARACTER SET LIKE "utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    | 3      |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4      |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

utf8mb4もUTF-8の一種っぽい。

csvファイルの文字コードを確認する

$ file --mime time_book.csv

time_book.csv: text/plain; charset=unknown-8bit

UTF-8でなかったので、$ iconvで文字コードを変えようとする。

$ iconv -f unknown-8bit -t utf8 time_book.csv > time_book.csv

iconv: conversion from unknown-8bit unsupported
iconv: try 'iconv -l' to get the list of supported encodings

unknown-8bitという文字コードは対応していないと言われる。$ iconv -lで見てみると、確かにunknown-8bitというのはない。$ iconvでは出来ないのか…と思ったが、unknown-8bitはshift_JISのことのようだとわかった。

$ iconv -f shift_JIS -t utf8 time_book.csv > time_book.csv
$ file --mime time_book.csv

time_book.csv: text/plain; charset=utf-8

CSVファイルをUTF-8に変換できた。

改行コードを指定していなかった

上記を修正しても、LOAD DATA LOCAL INFILEで1行目しか読み込まれなかったことから気づいた。
LOAD DATA LOCAL INFILEにLINES TERMINATED BY '\r'を追加した。

  • (これは、読み込みができなかったのと直接関係しないが)id列について、auto_incrementなのに数字を入れていた

mysql> SHOW COLUMNS FROM time_book FROM life;で見ると、id列のNullがnoになっていたので、auto_incrementだけど数値を入れていた。
example-auto-increment

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled.

とあったので、id列は0に変更した。

結果、CSVはこのような形になった。

id,start_datetime,end_datetime,duration,content,memo
0,'2017-01-02 09:45','2017-01-02 10:34','00:49','移動',
0,'2017-01-02 10:34','2017-01-02 11:34','01:00','家事','料理'

再度csvを読み込み

mysql> LOAD DATA LOCAL INFILE '~/time_book.csv'
-> INTO TABLE time_book
-> FIELDS TERMINATED BY ',' ENCLOSED BY "'"
-> LINES TERMINATED BY '\r'
-> IGNORE 1 LINES;

Query OK, 11348 rows affected, 1 warning (0.59 sec)
Records: 11348 Deleted: 0 Skipped: 0 Warnings: 1

読み込みができたっぽいぞ!

mysql> SELECT * FROM life.time_book LIMIT 2;
+----+---------------------+---------------------+----------+---------+--------------+
| id | start_datetime      | end_datetime        | duration | content | memo         |
+----+---------------------+---------------------+----------+---------+--------------+
|  1 | 2017-01-02 09:45:00 | 2017-01-02 10:34:00 | 00:49:00 | 移動    |              |
|  2 | 2017-01-02 10:34:00 | 2017-01-02 11:34:00 | 01:00:00 | 家事    | 料理          |
+----+---------------------+---------------------+----------+---------+--------------+
2 rows in set (0.00 sec)

できた!

感想

なるべく公式ドキュメント(英語)のみで理解しようとして進めたけど、けっこうつらかった。今回よりも古いバージョンなら日本語のドキュメントもあって、わからない場合はそちらも参照したけど、英語で読んでわからない場合は日本語でもだいたいわからず、英語力でなく知識不足が原因か、と思いつつ不明な単語等を調べて進めていった。

単に1つのテーブルを作るだけなのに各所で色々引っかかったけど、作業自体は全般的に楽しかった。やっとデータが読み込めた時はうれしくて、ひとり小躍りした。

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