PythonでCSVファイルの株価データをデーターベースに入れて、ローソク足チャートを描いてみた。

# 始値,高値,安値,終値(１時間足)
CREATE TABLE `ohlc_1h` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `sec_code` SMALLINT(5) UNSIGNED NOT NULL,
    `at` DATETIME NOT NULL,
    `open` FLOAT NULL DEFAULT NULL,
    `high` FLOAT NULL DEFAULT NULL,
    `low` FLOAT NULL DEFAULT NULL,
    `close` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 出来高,売買代金(１時間足)
CREATE TABLE `extra_1h` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `sec_code` SMALLINT(5) UNSIGNED NOT NULL,
    `at` DATETIME NOT NULL,
    `volume` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `turnover` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 始値,高値,安値,終値(１日足)
CREATE TABLE `ohlc_1d` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `sec_code` SMALLINT(5) UNSIGNED NOT NULL,
    `at` DATE NOT NULL,
    `open` FLOAT NULL DEFAULT NULL,
    `high` FLOAT NULL DEFAULT NULL,
    `low` FLOAT NULL DEFAULT NULL,
    `close` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;
# 出来高,売買代金(１日足)
CREATE TABLE `extra_1d` (
    `id` BIGINT(20) UNSIGNED NOT NULL,
    `sec_code` SMALLINT(5) UNSIGNED NOT NULL,
    `at` DATE NOT NULL,
    `volume` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `turnover` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

import datetime
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.sql import text

# 証券コードと日付時刻から主キーを生成する関数
def make_ID(sec_code, datetime):
    return 10**13 * sec_code + int(datetime.timestamp())

# CSVファイルの1レコードに主キーと証券コードを追加する関数
def append_key(sec_code, row):
    datetime = pd.to_datetime (row['at'])
    row['id'] = make_ID (sec_code, datetime)
    row['sec_code'] = sec_code
    return row

# CSVファイルを読み取ってデーターベースのテーブルに格納する関数
def csv_to_DB(engine, sec_code, tnames, csvfilename):
    # CSVファイルの1行目は列名なので無視して
    # 無視した列名の代わりを与える
    df_csv = pd.read_csv (csvfilename, na_values='', header=None, skiprows=1)
    if len(df_csv.columns) == 7:
        df_csv.columns = ['date','open','high','low','close','volume','turnover']
    else:
        df_csv.columns = ['date','time','open','high','low','close','volume','turnover']
    # date, time列を統合してat列を作る関数
    def combine_date_time(row):
        date_and_time = row['date']
        del row['date']
        if 'time' in row:
            date_and_time = date_and_time + " " + row['time']
            del row['time']
        datetime = pd.to_datetime (date_and_time)
        row['at'] = str(datetime)
        return row
    df = df_csv.apply (combine_date_time, axis=1)
    df = df.apply (lambda r:append_key(sec_code,r), axis=1)
    # ４値(ohlc)とその他に分ける
    df_ohlc = df.loc[:,['id', 'sec_code', 'at', 'open', 'high', 'low', 'close']]
    df_extra = df.loc[:,['id', 'sec_code', 'at', 'volume', 'turnover']]
    # 分けた物をそれぞれのテーブルへ格納する
    df_ohlc.to_sql (tnames['ohlc'], engine, if_exists='append', index=False) 
    df_extra.to_sql (tnames['extra'], engine, if_exists='append', index=False) 
    return df_csv

# 1時間足を日足へ変換してDBへ
def batch_resample_1day(engine, sec_code, tname_hourly, tname_daily, strategy):
    # 日足テーブルに登録されている最後の日を得る
    sql="SELECT MAX(at) AS 'latest' FROM `{}`".format (tname_daily)
    sql=sql+" WHERE `sec_code`='{}'".format (sec_code)
    sql=sql+";"
    with engine.connect() as conn:
        r = conn.execute(sql).fetchall()
        latest = r[0].latest
        # 日足テーブルに無いデーターを1時間足から計算して日足テーブルへ
        sql="SELECT at,{} FROM `{}`".format (",".join(strategy.keys()), tname_hourly)
        sql=sql+" WHERE `sec_code`='{}'".format (sec_code)
        if latest:
            nextday = latest + datetime.timedelta(days=1)
            sql=sql+" AND `at` > '{}'".format (nextday.strftime('%Y-%m-%d'))
        sql=sql+";"
        df = pd.read_sql(sql, engine, index_col='at')
        if not df.empty:
            df = df.resample('1D').agg(strategy)
            df = df.reset_index().apply (lambda x:append_key(sec_code,x), axis=1)
            df.to_sql (tname_daily, engine, if_exists='append', index=False)

sql="SELECT at,open,high,low,close"
sql=sql+" FROM `{}`".format('ohlc_1d')
sql=sql+" WHERE `sec_code`='{}'".format(8306)
sql=sql+" AND `at` BETWEEN '{}' AND '{}'".format('2017-01-01','2017-12-31')
sql=sql+";"
df = pd.read_sql(sql, engine,index_col='at')

from plotly.offline import init_notebook_mode, iplot
from plotly import figure_factory as FF

init_notebook_mode(connected=True) # Jupyter notebook用設定

fig = FF.create_candlestick(df.open, df.high, df.low, df.close, dates=df.index)

iplot(fig)