今回はMySQLにcsvをインポートしていく。
前回の記事でも書いたが、少しでもエラーと格闘する人を減らすためにこの記事は書かれている。
だから、今回も言わせてください。「俺の屍を越えて行け！」
テーブルの作成
まずは前回作成したDatabeseを選択して、テーブルの作成
（※ここの値は後ほど修正していくが、エラーを共有するため今回は修正前の値を載せています。）
mysql> use django_db
mysql> CREATE TABLE `map_table` (`store_name` varchar(50) not null, `rank` int(11) not null, `store_hensa` float(20,10) not null, `cost_hensa` float(20,10) not null, `cospa` float(20,10) not null, `address` varchar(100) not null, `lunch_or_dinner` varchar(30) not null, `longitude` float(20,10) not null, `latitude` float(20,10) not null);「store_name」も「rank」も「store_hensa」も「cost_hensa」も「cospa」も「address」も「lunch_or_dinner」も「longitude」も「latitude」も今回インポートするcsvに設定されているカラムの値。
👹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
⚠️ここでこんなエラー出ませんでした？
これ以下のようにバッククォート(`)を使用してないと発生。
CREATE TABLE map_table (store_name varchar(50) not null, rank int(11) not null, store_hensa float(20,10) not null, cost_hensa float(20,10) not null, cospa float(20,10) not null, address varchar(100) not null, lunch_or_dinner varchar(30) not null, longitude float(20,10) not null, latitude float(20,10) not null);
ちなみにこちらではハイフン(-)が含まれるとこのエラーが発生すると書いてありましたが、ハイフンが無くても発生。（とりあえず全部バッククォート付けました）
csvのインポート
1度目のチャレンジ
今回の本題であるcsvのインポート
まずは以下のように実行→エラー😇（{csvのpath}のところには各自自分の用意したcsvファイルへのpathを記載してください）
mysql> LOAD DATA INFILE '{csvのpath}' INTO TABLE map_table;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement👹ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
こちらのエラーはこのサイトを参考にして修正した。
以下の設定ファイルの一番下に
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf以下の行を追加。
secure-file-priv = ""mysqlを再起動するとこのエラーは解消された。
再度インポートコマンドを実行→エラー😇😇
mysql> LOAD DATA INFILE '{csvのpath}' INTO TABLE map_table;
ERROR 1300 (HY000): Invalid utf8mb4 character string: ','今回用意したcsvの文字コードはsjis
これはpython側で出力したときにutf-8だと文字化けになるから
こちらのエラーについては以下の記事が参考になると思います。
MySQLでCSV形式のファイルをLOAD DATA INFILE – Qiita
[MySQL]文字コードの設定を変更してutf8で統一する │ TEAM T3A
🧙これで解決
/etc/mysql/mysql.conf.d/mysqld.cnfファイルの最後に以下を追加
character-set-server=cp932MySQLに入って以下を実行
mysql> ALTER TABLE map_table CONVERT TO CHARACTER SET sjis;これも実行
mysql> alter database django_db character set cp932 collate cp932_japanese_ci;
Query OK, 1 row affected (0.07 sec)
mysql> show create database django_db;
+-----------+--------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+-----------+--------------------------------------------------------------------------------------------------------+
| django_db | CREATE DATABASE `django_db` /*!40100 DEFAULT CHARACTER SET cp932 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+-----------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)2度目のチャレンジ
再度csvを読み込ませようとする（調査中に見つけたのですが「FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n\r’」が必要ということなのでそれも追加しています）→エラー😇😇😇
mysql> LOAD DATA INFILE '{csvのpath}' INTO TABLE `map_table` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n\r';
ERROR 1366 (HY000): Incorrect integer value: 'rank' for column 'rank' at row 1👹ERROR 1366 (HY000): Incorrect integer value: ‘●●’ for column ‘●●’ at row 1
これはcsvでは確かに数値の値が入っているのですが、数字の文字列として認識されるよう。
なので、rankカラムは1~5の数字が入ってはいますが、文字列としてvarchar(10)を設定します。
👹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 ‘rank varchar(10)’ at line 1
⚠️ここでこんなエラー出ませんでした？
mysql> ALTER TABLE `map_table` MODIFY COLUMN rank varchar(10);
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 'rank varchar(10)' at line 1文字列に設定しようとした時に発生したエラー。
これはrankにバッククォートを付けると解決するのだが、rankだけなぜかこのエラーが発生する。
なので以下のようにするとこのエラーは解消。
上記でも同じエラーが発生している事を書いているが、これは「rank」の部分が問題だったのかも。
3度目のチャレンジ
3度目のチャレンジはまたエラー😇😇😇😇
👹ERROR 1261 (01000): Row 1 doesn’t contain data for all columns
ERROR 1261 (01000): Row 1 doesn't contain data for all columnscsv側のデータにNullが入っているところがあり、現状NullがNoになっているところもあるので、
mysql> DESC map_table;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| store_name | varchar(500) | YES | | NULL | |
| rank | int | NO | | NULL | |
| store_hensa | float(20,10) | NO | | NULL | |
| cost_hensa | float(20,10) | NO | | NULL | |
| cospa | float(20,10) | NO | | NULL | |
| address | varchar(100) | NO | | NULL | |
| lunch_or_dinner | varchar(30) | NO | | NULL | |
| lat | float(20,10) | NO | | NULL | |
| lon | float(20,10) | NO | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
9 rows in set (0.01 sec)以下のように変更
mysql> DESC map_table;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| store_name | varchar(500) | YES | | NULL | |
| rank | int | YES | | NULL | |
| store_hensa | float(20,10) | YES | | NULL | |
| cost_hensa | float(20,10) | YES | | NULL | |
| cospa | float(20,10) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| lunch_or_dinner | varchar(30) | YES | | NULL | |
| lat | float(20,10) | YES | | NULL | |
| lon | float(20,10) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
9 rows in set (0.13 sec)これでもエラーが消えない場合は、読み込むcsvと用意したtableに違いがあるのかと思います。
僕の場合、csvのカラムの方が用意したtableより多くてこのエラーが発生してたりしました。
もう一度csvデータを確認してみるのがいいと思います。
4度目のチャレンジ
csvの最初の行はカラムになるのでIGNORE 1 LINESを付けて以下を実行→これでうまくいった。
LOAD DATA INFILE '{csvのpath}' INTO TABLE `map_table` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;【おさらい】今回格闘し、討伐したエラー達👹
①
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
②
ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
③
ERROR 1366 (HY000): Incorrect integer value: ‘●●’ for column ‘●●’ at row 1
④
ERROR 1261 (01000): Row 1 doesn’t contain data for all columns
