MySQLにタイムスタンプを追加する。
「test」テーブルの最後のカラムの後ろに、レコードの作成時間を格納するカラムを追加します。
カラム名の後ろに「TIMESTAMP DEFAULT CURRENT_TIMESTAMP」と書きます。
ALTER TABLE tests ADD create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP AFTER last_column;
次に「test」テーブルの最後のカラム（先程追加した「create_time」）の後ろに、レコードの更新時間を格納するカラムを追加します。レコードを更新したときに更新時間を格納するにはカラム名の後ろに「TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP」をくっつけます。
ALTER TABLE tests ADD update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER create_time;
では試してみます。
まず「tests」テーブルにレコードを追加します。
INSERT INTO tests(date,time) values('20170101','090000');
SELECT * FROM tests;中身はこうなってます。
レコード作成時間が格納されてます。（右にスクロールさせてください）
+----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+ | id | date | time | start | high | low | end | volume | value | create_time | update_time | +----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+ | 2 | 2017-01-01 | 09:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | 2017-02-23 21:35:53 | 2017-02-23 21:35:53 | +----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+
次に先程追加したレコードを変更します。
UPDATE tests set date = '20170401'; SELECT * FROM tests;
中身はこう。update_timeだけ変更されてます。（右にスクロールさせてください。）
+----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+ | id | date | time | start | high | low | end | volume | value | create_time | update_time | +----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+ | 2 | 2017-04-01 | 09:00:00 | NULL | NULL | NULL | NULL | NULL | NULL | 2017-02-23 21:35:53 | 2017-02-23 21:38:35 | +----+------------+----------+-------+------+------+------+--------+-------+---------------------+---------------------+
pythonでNT倍率を表示してみる。
MySQLに保存した日経先物とTOPIX先物の日足データを使ってNT倍率をグラフ化してみました。
出力したグラフはこんな感じ。２００８年１０月のリーマンショック時に９．５をつけてから右肩上がり。
ソースコードはこれ。グラフの縦横比ってどうやって変更するんだろう。plt.figure(figsize=(x, y)) でも変更されなかったんだよな。。
%matplotlib inline import pandas as pd import numpy as np import mysql.connector import pandas_datareader.data as pdr import matplotlib.pyplot as plt import datetime import seaborn as sns day_array = [] nk_array = [] tp_array = [] nt_array = [] #データベース「nikkei」の「nk225days」テーブルから日時順に並び替えて、「年月日、時刻、終値」のデータを取り出す。 conn = mysql.connector.connect(user='root', password='xxxxxx', host='localhost', database='nikkei') #日経先物、TOPIX先物の日中終値を取得 cur_nk = conn.cursor(buffered=True) cur_nk.execute("SELECT date,time,end FROM nk225days WHERE time='09:00:00' order by date asc,time asc ;") cur_tp = conn.cursor(buffered=True) cur_tp.execute("SELECT date,time,end FROM tpdays WHERE time='09:00:00' order by date asc,time asc;") #終値がゼロ（取引がない）ときを除いて、データを配列に格納する。 for row in cur_nk.fetchall(): if not row[2] == 0: day_array.append(row[0]) nk_array.append(row[2]) #終値がゼロ（取引がない）ときを除いて、データを配列に格納する。（日経先物のデータがある取引日のみ） for row in cur_tp.fetchall(): for day in day_array: if row[0] == day: if not row[2] == 0: tp_array.append(row[2]) #NT倍率を格納 for i in range(len(day_array)): nt_array.append(nk_array[i]/tp_array[i]) # #NT倍率を表示 # for i in range(len(day_array)): # print (day_array[i],nt_array[i]) #NT倍率のグラフを表示 plt.plot(day_array, nt_array,color='red') plt.grid(which='major', color='k',linestyle=':') plt.title("NIKKEI / TOPIX ratio") plt.tick_params(labelsize=12) plt.figure(figsize=(3, 1)) plt.show() cur.close conn.close
pythonでMySQLに接続しようとしたときに「Mysql.connector.errors.InternalError: Unread result found.」のエラー
pythonでMySQLに保存した日経先物データからチャートを作成する。
こんな感じ。日経先物の日足チャートをpythonで作成。グダグダしながらやってたから５時間くらいかかった。pythonのドキュメント少ない。。
出力のグラフはこれ。
ソースコードはこれ。
%matplotlib inline import pandas as pd import numpy as np import mysql.connector import pandas_datareader.data as pdr import matplotlib.pyplot as plt import datetime #データベース「nikkei」の「nk225days」テーブルから日時順に並び替えて、「年月日、時刻、終値」のデータを取り出す。 conn = mysql.connector.connect(user='root', password='xxxxxx', host='localhost', database='nikkei') cur = conn.cursor() cur.execute("select date,time,end from nk225days order by date asc,time asc;") year_array = [] price_array = [] #終値がゼロ（取引がない）ときを除いて、データを配列に格納する。 for row in cur.fetchall(): if not row[2] == 0: year_array.append(row[0]) price_array.append(row[2]) plt.plot(year_array, price_array) plt.show() cur.close conn.close
pythonで日経先物分析をするために、データを格納するmysqlを作成する②
前回MySQLを作成しましたが、データベースを作るだけではあまり意味がありません。中にデータを入れなきゃ使えません。ということでwebサイトから日経先物のデータを取得して、そのデータをMySQLに格納することにようやく成功しました。ちなみに言語はRubyでやりました。分析はpythonでやるんだからデータ取得、格納もpythonでやればよかったかなとちょっと後悔してますが、Rubyのほうが馴染みがあったので。今回はつまづいたポイントをいくつかメモとして残しておきます。
ruby on railsではMySQLを利用したことあったんだけど、単純なrubyプログラムからのMySQL利用はやったことありませんでした。そのため割と時間がかかりました。ていうか、railsと同じくActiveRecordで出来るんやね。知らなったよ。てっきり「MySQL2」ライブラリとか使ってinsertするのかと思ってたよ。時間かかった。（そもそもpythonでやればもっと簡単だったのかも）
require "active_record" class Nk225day < ActiveRecord::Base end # スクレイピングで取得した項目を「nk225day」テーブルの各項目に格納して、保存する。 nk225day = Nk225day.where(date: date, time: time).first_or_create nk225day.start = start nk225day.high = high nk225day.low = low nk225day.end = endx nk225day.volume = volume nk225day.value = value nk225day.save
TOPIX先物のデータを格納するMySQLのテーブルを日経先物と同じように作成していました。で、データを格納した後に気づいたこと。。小数点が落ちてる。。てっきり忘れてました。ターミナルから以下のコマンドで型を変更
#「tpdays」テーブルの「start」カラムをFLOAT型に変更。FLOATの後ろのカッコは６桁表示でうち２桁は小数点という定義。 mysql> ALTER TABLE tpdays CHANGE start start FLOAT(6,2);
３，売買代金が格納できない。
以下のエラーメッセージ。
`ensure_in_range': 7395934775 is out of range for ActiveRecord::Type::Integer with limit 4 (RangeError)
ふむふむ、どうやらint型では桁数が足りないとのこと。調べているとrubyのint型の範囲は「-2147483647〜2147483648」らしい。うん、これじゃ売買代金多い日は足りない。もっと大きな範囲を指定できる型にしないといけないようだ。
ということで「int」型を「bigint」型に変更します。mysqlのコマンドで「days」テーブルの売買代金「value」の型変更。
mysql> alter table days change value value bigint;
ちなみにTOPIXの売買代金は小数点を表示しないといけないので以下のようになる。
#topixの売買代金は12桁、うち2桁は小数点以下で指定。 mysql> alter tpdays days change value value DOUBLE(12,2);
４，CSVのインポート
csv名を指定するときにパス名が必要って多くのサイトに記載あったけど、パス名は不要。（パスつけるとエラーになる。）
#「n225.csv」を「nk225days」テーブルにインポート。 mysql> LOAD DATA LOCAL INFILE 'n225.csv' INTO TABLE nk225days FIELDS TERMINATED BY ','
結局csvのインポートは使いませんでした。
５，CSVの出力
MySQLで色んなエラーが出てえらい時間かかった。解決方法をメモ。
mysql> SELECT * FROM nk225fifteens INTO OUTFILE 'nikkei.csv' FIELDS TERMINATED BY ',';; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
ここを真似した。ちなみに/usr/はターミナルで「open /usr」で開く。
qiita.com
#/usr/local/etc/my.cnfに追記 # *** upgrade to a newer version of MySQL. [mysqld] secure-file-priv = ""
上を解決したら、次は以下のエラー
$ mysql -uroot -p Enter password: xxxxxx ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
とか
ERROR! MySQL server PID file could not be found!
これは
$ ps ax | grep mysql 76560 ?? S 0:00.03 /bin/sh /usr/local/opt/mysql/bin/mysqld_safe --bind-address=127.0.0.1 --datadir=/usr/local/var/mysql 76780 ?? S 0:01.21 /usr/local/Cellar/mysql/5.7.17/bin/mysqld --basedir=/usr/local/Cellar/mysql/5.7.17 --datadir=/usr/local/var/mysql --plugin-dir sudo kill TERM 76560 sudo kill TERM 76780
で解決。
ERROR! MySQL server PID file could not be found! - 世界を疑え
で、普通にMySQLを起動してようやくｃｓｖ化の完了。
mysql> SELECT * FROM nk225fifteens INTO OUTFILE 'nikkei.csv' FIELDS TERMINATED BY ','; #格納場所は「/usr/local/var/mysql/nikkei」
疲れた。。
さて、ようやくpythonで分析しよう。
日経先物のデータをmysqlに格納しようとしたら売買代金でエラーになった
とあるwebサイトからスクレイピングして、日足の「始値」「高値」「安値」「終値」を取得。そしてmysqlに格納しようとしています。プログラムも作成し（なぜかプログラム作成はruby。。）いざデータベースに取り込む時に、、途中で異常終了しました。ターミナルのエラーメッセージ見てると以下のようなもの。（「7395934775」は、とある日の日経先物の売買代金です。）
`ensure_in_range': 7395934775 is out of range for ActiveRecord::Type::Integer with limit 4 (RangeError)
ふむふむ、どうやらint型では桁数が足りないとのこと。調べているとrubyのint型の範囲は「-2147483647〜2147483648」らしい。うん、これじゃ売買代金多い日は足りない。もっと大きな範囲を指定できる型にしないといけないようだ。
ということで「int」型を「bigint」型に変更します。mysqlのコマンドで「days」テーブルの売買代金「value」の型変更。
mysql> alter table days change value value bigint;
これで売買代金が多い日でもエラーなくmysqlに格納できるようになりました。
pythonで日経先物分析をするために、データを格納するmysqlを作成する①
pythonで日経先物のデータ分析をするためにはデータが必要です。普通の日経平均だとyahoo等のサイトから取ってこれるのですが、日経先物のデータを取ってこれるサイトは現状中々ありません。いくつかサイトはあるのですが、そのサイトが無くなったらオシマイ！って状況は避けたいので、自分用にmysqlを使って日経先物のデータを保存することにしました。ちなみに保存しようかなあと考えてるのは５分足、１５分足、３０分足、１時間足、日足くらいです。ということでデータベース構築を以下の手順でやりましたのでメモ。
ちなみにこの先の展望としては、「スクレイピングで先物データ自動取得→データベースに格納」「そのデータを使ってpythonで分析」ってところでしょうか。しかし、まだ日経先物データのpythonでの分析はサイトがあまりないですね。素人がどれだけやってもしれてると思うけど楽しいし頑張ろうっと。正直、rubyよりも楽しいです。
$ brew install mysql
mysqlのスタート
$ mysql.server start
パスワードの設定。いくつか質問でてきますが、全部YESでよいはずです。
$ mysql_secure_installation
mysqlを使用。このコマンド入れるとパスワード入力されるよう求められるので先程設定したパスワードを入力。これでmysqlのコマンドが入力できるようになります。
$ mysql -uroot -p
データベースを作成します。テーブル名は「nikkei」。これでデータベースが作成されました。確認は「Sequel Pro」を使ってます。」
mysql> CREATE DATABASE nikkei;
「nikkei」を選択します。
mysql> USE nikkei
データベースにテーブルを作成して項目を追加します。まずは日足（日中とナイトで分割）のテーブル（テーブル名：day）を作成します。必要な項目は「年月日」「日中・ナイト区分」「始値」「高値」「安値」「終値」「出来高」「売買代金」でしょうか。とりあえず年月日のみデータベースにカラムを作ってみます。（レコードのユニークキーになるidも指定します。）
mysql> CREATE TABLE day(
-> id INTEGER NOT NULL AUTO_INCREMENT,
-> date DATE NOT NULL,
-> PRIMARY KEY(id)
-> );dayテーブルにカラムが出来ていることを確認します。
mysql>DESCRIBE day;
+-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | date | NO | | NULL | | +-------+---------+------+-----+---------+----------------+
データベースにまず始値（カラム名:start）のカラムを追加します。
ALTER TABLE day ADD start INTEGER;
カラムが追加されていることを確認します。
（startのNULLがYESでいいのかは疑問だけど）
mysql> DESCRIBE day; +-------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | date | NO | | NULL | | | start | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+----------------+
全項目追加したらこんな感じ。
mysql> DESCRIBE day; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | date | date | NO | | NULL | | | day_night | int(1) | YES | | NULL | | | start | int(11) | YES | | NULL | | | high | int(11) | YES | | NULL | | | low | int(11) | YES | | NULL | | | end | int(11) | YES | | NULL | | | volume | int(11) | YES | | NULL | | | value | int(11) | YES | | NULL | | +-----------+---------+------+-----+---------+----------------+
ちなみに「日中・ナイト区分（day_night）」は、カラム追加するの忘れていたので、後から追加しました。そのときのコマンドはこちら。dateカラムの後に１桁で追加してます。
mysql> ALTER TABLE day ADD day_night INTEGER(1) after date;
「Sequel Pro」で見るとこんな感じ。「nikkei」データベースの「day」テーブルに各項目が出来てるのが確認できます。
次は日中足のテーブルを作成してみようと思います。以下は15分足。日足に時間だけ追加してます。それ以外はまったく同じ。同じように5分足、30分足、1時間足のテーブルも作成します。
mysql> CREATE TABLE minute_15(
-> id INTEGER NOT NULL AUTO_INCREMENT,
-> date DATE NOT NULL,
-> time TIME NOT NULL,
-> day_night INTEGER,
-> start INTEGER,
-> high INTEGER,
-> low INTEGER,
-> end INTEGER,
-> volume INTEGER,
-> value INTEGER,
-> PRIMARY KEY(id)
-> );テーブル名の変更は以下。
mysql> alter table salestable rename to salestable_back;
