SQLで時刻を指定して抽出する
結構な時間ハマったのでメモ。
データベースのカラムで時刻の項目を持ってるケースもあると思います。で、ある特定の時刻のデータを抽出したいケースってありますよね。SQLのWHERE句でこう書けばいけました。
WHERE TIME_FORMAT(column_name, '%H:%i:%s') = '09:00:00'
これで時刻を指定して抽出できます。はじめ「convert」使って色々やってたんだけど、中々うまくいかなかったので誰かのお役に立てば。
MySQLに日経先物の５分足データCSVをインポートしたらフリーズした。
相変わらず日経先物の過去データを集めています。過去の日足および最近の日中分足はwebからスクレイピングで取得しました。
ちなみにスクレイピングはこちらのサイト様にお世話になりました。
（サイト運営者様、サーバーに負荷かけてしまったかもしれないです。申し訳ありません。）
ただし、日中分足については最近のデータ（30分足は過去４ヶ月。１５分足、５分足は過去４日）しかweb上にありません。そのため過去のデータについては別のところから取得する必要があります。ということで、ここから過去の日中分足をダウンロード。2012年〜のデータがあります。
225labo.com
で、このCSVをMySQLに格納するためのプログラムをrubyで書きました。MySQLのコマンドで単純にインポートしても良かったんですが、取引日を加工してMySQLに格納したかったので、プログラムでやりました。
require 'mechanize' require "active_record" require "date" require "time" ActiveRecord::Base.establish_connection( adapter: "mysql2", host: "localhost", username: "root", password: "xxxxxx", database: "nikkei", ) class Test < ActiveRecord::Base end today = 0 tomorrow = 0 regist = 0 FILE_NAME = 'n225_5.csv' File.open(FILE_NAME) do |file| file.each_line do |line| regist += 1 vals = line.split(',') #------取引日の加工----------# #ナイト・セッション（２４時まで）は当日の日付に変更する。 if vals[1].to_i == 9 today = vals[0] end if vals[1].to_i >= 16 vals[0] = today end #ナイト・セッションの２４時以降は当日の翌日日付とする。（金曜ナイトの２４時以降は土曜の日付となる。） if vals[1].to_i >= 0 && vals[1].to_i <= 5 tomorrow = Date.parse(today) vals[0] = tomorrow + 1 end #30分足の場合、朝のスタート時間をデータ上８時半にする。（ほんとのスタート時間は８時４５分） # if vals[1].to_i == 8 # vals[1] = "08:30:00" # end #-----取引日の加工終了----------# attr = {date: vals[0], time: vals[1], start: vals[2], high: vals[3],low: vals[4],end: vals[5],volume: vals[6]} test = Test.new(attr) if test.save puts "data insert done!! #{regist}" else #データの保存でエラーとなったときはABENDさせる。 puts "error" exit(1); end end puts "data insert END!!" end
こんなプログラムで３０分足、１５分足、５分足のCSVデータをMySQLに格納していきます。５分足については2012年〜のデータで約３０万件ありました。。この３０万件を処理してたら、MySQLが動かなくなります。。
MySQLがフリーズしたときは以下のようにやれば解決しました。これでようやくデータの準備は完了です。やっと分析フェーズに入れそうです。長かった。１週間くらいかかったぜ！！
mysql> show processlist; +-----+------+-----------+--------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+--------+---------+------+----------+------------------+ | 11 | root | localhost | nikkei | Sleep | 58 | | NULL | | 12 | root | localhost | NULL | Sleep | 18 | | NULL | | 116 | root | localhost | NULL | Query | 0 | starting | show processlist | +-----+------+-----------+--------+---------+------+----------+------------------+ mysql> kill 11; mysql> kill 12;
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で分析しよう。
