mysql」カテゴリーアーカイブ

mysqlの説明

MySqlでDB、テーブル作成メモ 備忘録

MySqlでDB、テーブル作成メモ(条件はユーザはすでに作成済)
株式市場の企業の株価と出来高を登録する。
pythonを使ったスクレイピング処理をこれから行う。この記事にはPythonの記載はない。
mysqldbを使用していたがmysql-connector-pythonが良いらしいのでpip install済

ターミナルでの作業

最近忘れっぽくて、使っていないコマンドをどんどん忘れている。SQL文も忘れてるので、備忘録メモとして記載しておく。記憶力の低下に抵抗せずに、メモをしていつでも思い出せるかメモを見ながら作業するようにする。

 

今日のエラー、Mysql、Can’t connect to local MySQL server

A・症状2021/01/06

iMacを再起動した後で、Anaconda NavigatorからPythonを実行した
するとエラーが発生。PythonでMysqlを使用しているエラーのようだ。
エラーの一部:Can’t connect to local MySQL server through
Mysqlサーバに接続できない

・対応

Mysqlを使用しているPythonでエラーが発生したとき、Can’t connect to local MySQL server through
たぶんMysqlが起動していないから(PCを起動した直後や再起動するとMysqlは停止しているからMysqlを起動する)
(1)mysqlサーバを起動する。ターミナルから実行する
$ mysql.server start
このrootはいつもの(秘密)パスワードで、CSV取り込みは、Test1030のパスワード

(2)Mysqlに接続する(以下は個別のアカウントmasedaを使用した場合。rootの場合はmysql -u root -p)
$ mysql -u maseda -p
Test1030(これは知られてもよい)のパスワード

(3)通常のMysqlコマンドやPythonから実行する
実行できた。

 

B SyntaxError

・症状

pythonを実行したらエラーメッセージが表示された

・エラーメッセージ

SyntaxError: invalid syntax

対応

指定された行の上の行の閉じカッコの数があっていなかった。
1個少なかったために、次の行でエラーが表示されていた。
test((a,a),(1,1,1)   <<<閉じカッコが足りない
testB()<<<ここでエラーが表示


test((a,a),(1,1,1))

pythonでTXTからCSV変換してMysqlへのインサート処理

#pythonメソッドは外部ファイルpy
#2020/12/21
#CSVをMysqlにインサート
#Test_Table_StockOption
#TXTからCSVに変換してCSVファイルを読み込んでMysqlのテーブルに登録を一括して行えるようにする。DB,テーブルは作成済とする。スタンドアローンでの利用

環境:MacOS BigSur、Python3、Mysql、JupiterLab Anaconda

メソッド 外部ファイルにしたmethodMysql.py これをimport methodMysql として読み込む

 

pythonでCSVをMysqlへのインサート処理

#pythonでCSVをMysqlへのインサート処理を入れたい
#2020/12/21
#CSVをMysqlにインサート
#Test_Table_StockOption
#CSVファイルを読み込んでMysqlのテーブルに登録を一括して行えるようにする
#
“””
考え方2020/12/22
TXTをCSVで出力
CSVファイルを読み込み
Mysqlに接続
ループしながらMysqlのテーブルに入れていく

現在はCSV出力まで終わっているので
CSVファイルを読み込み
テーブルにデータを挿入する部分から書いていく

・テーブルを作成例 Mysql

・データを全部削除
delete from Table_StockOption;
・自動採番をリセット
ALTER TABLE Test_Table_StockOption AUTO_INCREMENT = 1;

 

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)
##################やっとインストールできた。