環境：MacOS Bigsur
Mysql8
CSV カンマ区切り UTF8
Pythonを使ってテキストファイルをCSVに出力して、
そのCSVファイルをMysqlのテーブルにターミナル手動でインサート処理する。
コマンドラインで実行する
LOAD DATA LOCAL INFILE
内容
注意点はMySQLバージョン8では、デフォルトでは無効化されている。
mysql> SELECT @@local_infile;
を実行して「1」になっているかを確認する。「0」なら無効なので有効にする必要がある。
以下、下方に記載
参考サイト
http://jigsaw.hatenablog.jp/entry/2013/06/12/113016
参考、エラーになった
https://mita2db.hateblo.jp/entry/2020/01/13/163218
MySQL 8.0 で LOAD DATA LOCAL INFILE は無効化されている
以下のDBとテーブルは作成済み
作成方法については、他の記事を参照してほしい
ユーザ：maseda
pass:Tes****
DB:Stock
Table:Table_StockOption
・カラム構成
ID:id , int auto_increment、自動採番
出来高1 Volume1
前日比1 Change1
価格1 Price1
行使価格 ExercisePrice
価格2 Price2
前日比2 Change2
出来高2 Volume2
月限 Month , 例 12月限 12
取引日付、株探掲載日付 OptionDate ,date , DATE(NOW()) >> 2014-01-15
テーブル取り込み作成日 CreateDate ,datetime, NOW() >>2014-01-15 17:05:43
予備YOBI （テキスト）,text
—
CSVファイルをテーブルのカラムにインサート処理する。
事前に与える値
「月限」はファイル名に記載していないので、取り込み毎に与える。
今後ファイル名に12月限みたいに記載してそこから取得するようにするか？
また「取引日付、株探掲載日付 OptionDate」については、任意の日付を与える。
「テーブル取り込み作成日」は、NOW()で与える。
@1から@7はCSVのカラムの左側からの順番で、列数は7つ
なおIDカラムが存在するが自動採番なので何も指定しない
mysqlのコマンドラインで実行する。ファイル名とテーブル名、カラム等を変更。
CSVは7つあり、テーブルのカラムは12こある。そしてIDは自動採番設定あり。
CSVの1番目を@1として該当のカラムVolume1に入れる。
SET Volume1=@1
以降CSVの@番号をカラム名に入れていく。
|
1 2 3 4 5 6 7 8 |
LOAD DATA LOCAL INFILE "/Users/toshiromaseda/Documents/2020年株関連/kabu_python/c_pythontest_2020年12月17日.csv" INTO TABLE Table_StockOption FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@1,@2,@3,@4,@5,@6,@7) SET Volume1=@1, Change1=@2, Price1=@3, ExercisePrice=@4, Price2=@5,Change2=@6,Volume2=@7,Month='1',OptionDate='2020/12/01', CreateDate=NOW(),YOBI=''; |
画像CSVの構成7つの列がある
テーブルカラム一覧画像、Volume1にCSVの1列目が入る。IDは自動採番なのでCSVにはない
LOAD DATAを実行するとエラーになった。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> use Stock; 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 mysql> LOAD DATA LOCAL INFILE "/Users/toshiromaseda/Documents/2020年株関連/kabu_python/c_pythontest_2020年12月17日.csv" -> INTO TABLE Table_StockOption -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> (@1,@2,@3,@4,@5,@6,@7) -> SET Volume1=@1, Change1=@2, Price1=@3, ExercisePrice=@4, -> Price2=@5,Change2=@6,Volume2=@7,Month='1',OptionDate='2020/12/01', -> CreateDate=NOW(),YOBI=''; ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides mysql> |
エラーになった。↑
LOAD DATA INFILE LOCAL は セキュリティ上のリスクになるため、デフォルトでは無効化されているらしい。
・以下の２つを設定する
１ターミナル
$ mysql -u root -p –local_infile=1
２mysql側
mysql> SET GLOBAL local_infile=on;
確認
SELECT @@local_infile;
|
1 2 3 4 5 6 7 |
mysql> SELECT @@local_infile; +----------------+ | @@local_infile | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) |
1になっているのでOK。
user:masedaでMysqlに接続して再度確認して問題なし。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> SELECT @@local_infile; +----------------+ | @@local_infile | +----------------+ | 1 | +----------------+ 1 row in set (0.00 sec) mysql> LOAD DATA LOCAL INFILE "/Users/toshiromaseda/Documents/2020年株関連/kabu_python/c_pythontest_2020年12月17日.csv" -> INTO TABLE Table_StockOption -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> (@1,@2,@3,@4,@5,@6,@7) -> SET Volume1=@1, Change1=@2, Price1=@3, ExercisePrice=@4, -> Price2=@5,Change2=@6,Volume2=@7,Month='1',OptionDate='2020/12/01', -> CreateDate=NOW(),YOBI=''; Query OK, 85 rows affected, 222 warnings (0.04 sec) Records: 85 Deleted: 0 Skipped: 0 Warnings: 222 |
warningはあるがCSVデータは登録されているようだ。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select OptionDate ,CreateDate,YOBI from Table_StockOption limit 5; +------------+---------------------+------+ | OptionDate | CreateDate | YOBI | +------------+---------------------+------+ | 2020-12-01 | 2020-12-18 10:59:06 | | | 2020-12-01 | 2020-12-18 10:59:06 | | | 2020-12-01 | 2020-12-18 10:59:06 | | | 2020-12-01 | 2020-12-18 10:59:06 | | | 2020-12-01 | 2020-12-18 10:59:06 | | +------------+---------------------+------+ 5 rows in set (0.00 sec) |
一旦、データを消して再度CSVを登録してワーニングを見る
ワーニングを表示させるには
show warnings;
データを全部削除
mysql> delete from Table_StockOption;
Query OK, 85 rows affected (0.02 sec)
再度、Loadを実行
そして
show warnings;
warningが表示された。
どうやら、値がない（空）でワーニングが表示されたようだ。空でもいいので
これは無視していいようだ。
INTで空の場合は０が入っている
| Warning | 1366 | Incorrect integer value: ” for column ‘Volume1’ at row 85 |
| Warning | 1366 | Incorrect integer value: ” for column ‘Change1’ at row 85 |
| Warning | 1366 | Incorrect integer value: ” for column ‘Price1’ at row 85 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> select count(*) from Table_StockOption; +----------+ | count(*) | +----------+ | 85 | +----------+ mysql> select * from Table_StockOption limit 3; +-----+---------+---------+--------+---------------+--------+---------+---------+-------+------------+---------------------+------+ | ID | Volume1 | Change1 | Price1 | ExercisePrice | Price2 | Change2 | Volume2 | Month | OptionDate | CreateDate | YOBI | +-----+---------+---------+--------+---------------+--------+---------+---------+-------+------------+---------------------+------+ | 128 | 95 | 0 | 1 | 32000 | 0 | 0 | 0 | 1 | 2020-12-01 | 2020-12-18 11:03:16 | | | 129 | 52 | 0 | 2 | 30750 | 0 | 0 | 0 | 1 | 2020-12-01 | 2020-12-18 11:03:16 | | | 130 | 12 | 0 | 3 | 30250 | 0 | 0 | 0 | 1 | 2020-12-01 | 2020-12-18 11:03:16 | | +-----+---------+---------+--------+---------------+--------+---------+---------+-------+------------+---------------------+------+ 3 rows in set (0.00 sec) |
IDはリセットしていないので、削除した後に追加すると次の番号がふられるから、
実際の行数とは一致しない。
・mysql auto_increment リセット
ALTER TABLE テーブル名 AUTO_INCREMENT = 1;
なので、データをdeleteしてから実行するとよいはず。
delete from Table_StockOption;
ALTER TABLE Table_StockOption AUTO_INCREMENT = 1;
|
1 2 3 4 5 6 |
mysql> delete from Table_StockOption; Query OK, 85 rows affected (0.01 sec) mysql> ALTER TABLE Table_StockOption AUTO_INCREMENT = 1; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 |
>>削除してリセットまで済。次は、必要なデータを順次取り込む。今ここ、2020-12-18、11時27分
—適宜、ファイル名を変更していく
mysqlコマンド
|
1 2 3 4 5 6 7 8 |
LOAD DATA LOCAL INFILE "/Users/toshiromaseda/Documents/2020年株関連/kabu_python/c_pythontest_2020年12月17日.csv" INTO TABLE Table_StockOption FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (@1,@2,@3,@4,@5,@6,@7) SET Volume1=@1, Change1=@2, Price1=@3, ExercisePrice=@4, Price2=@5,Change2=@6,Volume2=@7,Month='1',OptionDate='2020/12/01', CreateDate=NOW(),YOBI=''; |
こちらの記事もどうぞ
pythonでTXTからCSV変換してMysqlへのインサート処理 #pythonメソッドは外部ファイルpy
#2020/12/21
#CSVをMysqlにインサ […]
mysql、日付のみCURDATEを使うかDATE_FORMAT now()だと日付と時刻が出力される
mysql> select […]
MySqlでDB、テーブル作成メモ 備忘録 MySqlでDB、テーブル作成メモ（条件はユーザはすでに作成済）
株式市場の企業の株価と出来高を […]
pythonでCSVを読み込みクラスオブジェクトを配列に入れる CSVを読み込みクラスオブジェクトを配列に入れる
CSV例,6列必要。1行目は除外する。米ヤ […]- 今日のエラー、Mysql、Can’t connect to local MySQL server A・症状2021/01/06
iMacを再起動した後で、Anaconda […]
pythonでCSVをMysqlへのインサート処理 #pythonでCSVをMysqlへのインサート処理を入れたい
#2020/12/21
#CS […]