SQLSERVER、ストアドプロシージャ、クエリ」カテゴリーアーカイブ

MysqlにCSVファイルをインサート

環境: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の@番号をカラム名に入れていく。

画像CSVの構成7つの列がある

Number
テーブルカラム一覧画像、Volume1にCSVの1列目が入る。IDは自動採番なのでCSVにはない

キャプチャ

LOAD DATAを実行するとエラーになった。

エラーになった。↑
LOAD DATA INFILE LOCAL は セキュリティ上のリスクになるため、デフォルトでは無効化されているらしい。

・以下の2つを設定する
1ターミナル
$ mysql -u root -p –local_infile=1

2mysql側
mysql> SET GLOBAL local_infile=on;

確認
SELECT @@local_infile;

1になっているのでOK。

user:masedaでMysqlに接続して再度確認して問題なし。

 

warningはあるがCSVデータは登録されているようだ。

 

一旦、データを消して再度CSVを登録してワーニングを見る
ワーニングを表示させるには
show warnings;

データを全部削除
mysql> delete from Table_StockOption;
Query OK, 85 rows affected (0.02 sec)

再度、Loadを実行
そして
show warnings;
warningが表示された。
どうやら、値がない(空)でワーニングが表示されたようだ。空でもいいので
これは無視していいようだ。
INTで空の場合は0が入っている
| 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 |

 

IDはリセットしていないので、削除した後に追加すると次の番号がふられるから、
実際の行数とは一致しない。

・mysql auto_increment リセット
ALTER TABLE テーブル名 AUTO_INCREMENT = 1;

なので、データをdeleteしてから実行するとよいはず。
delete from Table_StockOption;
ALTER TABLE Table_StockOption AUTO_INCREMENT = 1;

 

>>削除してリセットまで済。次は、必要なデータを順次取り込む。今ここ、2020-12-18、11時27分
—適宜、ファイル名を変更していく

mysqlコマンド

 

 

MySQLでDB、テーブルを作成する、MacOS BigSur 2020年12月

MySQLのセキュリティ設定している人はここは飛ばす

$マークはMacのターミナルを使用している。
ターミナルは、アプリの「その他」の中にある。(何故かわかりにくいところに入れてある)

mysqlサーバを起動する
$ mysql.server start
Starting MySQL
. SUCCESS!

止めるときは
$ mysql.server stop

次にセキュリティ設定
このサイトを参考にすると良い
https://style.potepan.com/articles/19020.html

MySQLのセキュリティ設定

>>>>>>>>>>>>>>>>>>>以下入力した内容 ここから↓

 

>>>>>>>>>>>>>>>>>>>>ここまで

mysqlにRootでログインする。

 

exitで抜ける
mysql> exit
Bye

ユーザの作成・削除

user:maseda
pass:Test1030 >>ここのパスワードは知られても気にしない。このパスワードを他で使わないから。

ユーザの作成
まずは
rootでログインする
mysql -u root -p
(pass)
以下はmysqlコマンドとして入力する。masedaユーザを作成
一人でしか使わないが、今後誰かと共同作業があるかもしれないので勉強のために
ユーザを作る。

create user ‘maseda’@localhost identified by ‘Test1030’;
作成したユーザの確認

select user, host from mysql.user;

 

MySQLの設定をしている人はここから作業をする。

root権限でユーザにグローバルレベルでcreate権限を与える。すべてのDBで使える。
制限するときは、DB名を指定するが今はしない。

権限を与える。必要なときに必要な分を与える
grant create on *.* to maseda@localhost;
GRANT SELECT,UPDATE,INSERT,DELETE ON *.* to maseda@localhost;
GRANT DROP,CREATE VIEW,ALTER,EXECUTE,INDEX,SHOW DATABASES,SHOW VIEW,FILE ON *.* to maseda@localhost;

exitでrootを抜ける。

→次はここから、2020/12/15
ユーザでログイン
mysql -u maseda -p
psssはTes*****にした。上に記載。このパスワードはデモ用なので知られてOK

次にデータベースを作成して、テーブルを作る。
そしてCSVファイルを読み込む

自動採番の例 auto_increment
>>create table staff(id int auto_increment, name varchar(10), index(id));

今回作成するDB(Stock)とテーブル(Table_StockOption)を作成

株探のオプション記事の数値をDB化する。
作成の例
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
予備 (テキスト),text

//正負を表す ‘-‘ や ‘+’ を記述することができる。+10の+記号はINT型で可能
挿入時は+記号はない。10となり、マイナスは-10となる
NULLは処理速度が遅くなるのでできるだけ使わない。空にしたくないなら-1とか’unknown’とか入れる
大量のデータがないときはそれほど考慮しなくてよいだろう。

・ユーザはmaseda

・DBを作成
CREATE DATABASE Stock DEFAULT CHARACTER SET utf8mb4;

・DBを指定する
use Stock;

・テーブルを作成
CREATE TABLE Table_StockOption(
ID int(11) NOT NULL AUTO_INCREMENT,
Volume1 int(11),
Change1 int(11),
Price1 int(11),
ExercisePrice int(11) NOT NULL,
Price2 int(11),
Change2 int(11),
Volume2 int(11),
Month int(2),
OptionDate DATE,
CreateDate DATETIME,
YOBI text,
PRIMARY KEY (ID)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

>>PRIMARY KEY(ID)が必要。ないとエラーになる。

mysql> show columns from Table_StockOption;
ERROR 1142 (42000): SELECT command denied to user ‘maseda’@’localhost’ for table ‘table_stockoption’
mysql>
この場合権限がないので、権限を与える。
GRANT SHOW DATABASES ON *.* to maseda@localhost;
これでいいはず。もしこれでだめな場合は、必要な権限を与えて。
以下はカラム一覧を表示

 

これでテーブルの作成まで終わった。
次は、株探からコピペしたテキストをCSVファイルに置き換えて
mysqlに取り込む作業。
まずは、PythonでCSVファイルに変更するスクリプトを書く

BigSurにbrew,mysqlインストール、環境MacOSXからBigSurにアップグレードしたら

詳細な説明はこっち
https://style.potepan.com/articles/19020.html

簡潔な説明はこっち
https://qiita.com/fuwamaki/items/194c2a82bd6865f26045

mysql –versionを実行したところmysqlはまだインストールされていなかった。
次にbrewのバージョンを確認、
$ brew -v

以下のようなエラーが発生した。調べるとMac OSをBigSurにバージョンアップしたことが原因で、
brewもアップグレードが必要
>>エラーの内容

以下の3つのコマンドを順に実行した。brew doctorは必須ではないかも、、、

$ brew update
>>暫く待つ、アップデートが裏で始まっている。5分ほど待つ
$ brew doctor
>>Warningがでるけどよくわかんないのでそのまま
$ brew upgrade
>>暫く待つ、画面上に色々とインストール状況が表示される

やっとインストールできた。

mysqlをインストール
$ brew install mysql

バージョンを確認
$ mysql –version
mysql Ver 8.0.22 for osx10.16 on x86_64 (Homebrew)
##################やっとインストールできた。

UnityでSQLiteの使い方 リンク

UnityでSQLiteの使い方

UnityにSQLiteを導入する
https://qiita.com/CreateVector3/items/b15dc570faed517360d4

UnityでSQLiteを扱う方法
https://qiita.com/hiroyuki7/items/5335e391c9ed397aee50

UnityでSQLite!SQLiteUnityKitに潜む4つの問題点と対応方法
https://fantastic-works.com/archives/406#i-3

UnityのSQLiteUnityKitのマルチバイト問題 (。・ω・。)
https://anz-note.tumblr.com/post/91559515821/unity%E3%81%AEsqliteunitykit%E3%81%AE%E3%83%9E%E3%83%AB%E3%83%81%E3%83%90%E3%82%A4%E3%83%88%E5%95%8F%E9%A1%8C-%CF%89

SQLSERVER SELECT TOP にはできるだけORDER BYをつける

先日、サーバによってSELECT TOP 1 AA FROM T_TABLE(このときはORDERを入れていない)でWHILEで回すクエリの結果が異なることがあった。
なにが悪いのかさっぱりわからなかった。Aサーバで実行するときちんと出力されるが、Bサーバで出力すると出力件数が少ない。
メモリが足りないからとか負荷がかかったとか考えたが違った。
賢者に聞くと全件検索によるSELECTでORDERがないと出力順が変わることがあるらしい。しかも数万件の出力の場合はそれが顕著に現れるらしい。
必ずしも期待した結果になるかは不明らしいから気づかない。
続きを読む

SQLSERVER パズル63 MINの扱いが難しく理解できない、相関サブクエリ

パズル63 問題文
dataのまとまりで連続しているnumを表示したい。

MINというと結果が1つのはずだが、ある条件になると複数を出力するみたい。
その条件がよくわかっていない。>>わかった。相関サブクエリだと繰り返し実行されるために複数回実行される
MINはたいていGROUPを使っていくつかのまとまり単位で最小値を出力する。つまりまとまりの中で1つだ。
この条件に当てはまらないのは、結合した条件で、結合されていないテーブルとの条件が加わったときに
MINが複数出力されるのかもしれない。がよくわかっていないので、またいつか勉強することにする。

続きを読む

買った書籍 独習C# 新版 |Excel VBA逆引き辞典パーフェクト 第3版  |SQLアンチパターン|確かな力が身につくC#「超」入門

買った書籍 独習C# 新版 |Excel VBA逆引き辞典パーフェクト 第3版  |SQLアンチパターン|確かな力が身につくC#「超」入門

C#は会社で新しい環境の開発をすることになるかもしれず、また覚えなくては。
昔Unityで使ったけど単なる関数を作るレベルでしか使っていない。
C#の基礎だけ学んでも使えるようにはならないのでUnityがC#を使うので自宅でゲームを作ってみるか。

続きを読む