シンプルに解説。SQLAlchemyで株価ローソク足をデーターベースに保存する。株価分析で楽しく学ぶPythonの小ネタ。[SQLite] | 独学・Pythonで株式投資（旧トレードステーションのある生活）株式投資分析・自動売買で生活を豊かに

Pythonでデーターベースを扱う際に、最も多く利用されているといわれているのがSQLAlchemy。

今回は、SQLAlchemyで株価ローソク足を保存するまでの手順を紹介しながら、この上なくシンプルに解説します。

まず、楽しみながら動くものを作る。

細かいことは、その後に学ぶ方がだんぜん効率的です。

次のステップに進みやすいように、SQLAlchemy公式チュートリアルの手順や表記に出来るだけ近づけました。

公式ページのリンクも、このブログ記事の後半に貼っていますので参考にしてください。

今回はSQLiteのデーターベースを作成します。

動画で解説

SQLAlchemyのインストール

SQLAlchemyのインストールはpipで行います。

pip install SQLAlchemy

1	pip install SQLAlchemy

バージョンの確認（Version Check）

インストールされているSQLAlchemyのバージョンを確認します。

import sqlalchemy

sqlalchemy.__version__

import sqlalchemy

sqlalchemy.__version__

出力結果：’1.3.20′

私のPCにはバージョン’1.3.20’がインストールされていました。

ローソク足をデーターベースに保存するまでの手順

ここでローソク足を保存するまでの手順を紹介します。

手順が多いなぁと感じるかも知れません。

初学者は、意味不明の言葉が連発して多くの？？？が頭に浮かぶでしょう。

意味が分からなくても結構です。

手順がここに書いてあるって事だけを覚えておいてください。

私がSQLAlchemyを学んだ際に、この手順が頭に入っていなかったために、理解に時間が掛かってしまいました。

なお、下の手順のうち[ローソク足]と記載しているものはローソク足をデーターベースに保存するために必要な手順です。

他のデーターを保存する際には必須ではありません。

データーベースファイルの作成・Connecting（接続）
Ｂａｃｅクラスを作成・Declare a Mapping(マッピング)
Baseクラスを継承してテーブルを定義
データベース作成・Create a Schema（スキーマを作成）
セッション・Ｓｅｓｓｉｎオブジェクトを作る
データーを書き込むためのオブジェクトを作成・Create an Instance of the Mapped Class
日時のデーターを扱う準備・datetimeインポート[ローソク足]
セッションにａｄｄする・Adding and Updating Objects
コミットして永続化・ここでやっとデーターベースに書き込まれる
ローソク足データーを用意する[ローソク足]
ローソク足データーをデーターベースに読み込む[ローソク足]

どうですか？　「手順が多いなぁ」と感じましたか？

私は「えっ面倒だなぁ」と思いました。

ただし、あまりあるメリットがありますので、めげずに先に進みましょう。

データーベースファイルの作成・Connecting（接続）

ここで作るのは、データーベースのファイルだけです。

今回はSQLiteのデーターベースを作成します。

データーベースを作成するにはcreate_engine（）を使用します。

## Connecting（接続）
#SQLiteのデーターベースを作成します。

#必要なライブラリーのインポート
from sqlalchemy import create_engine

#enginとは　どのデーターベースにどうやって接続するのか、設定を行いその設定内容を保存したもの
#エコー　有効にすると、生成されたすべてのSQLが出力されます
engine = create_engine('sqlite:///candle.db', echo=True)

## Connecting（接続）

#SQLiteのデーターベースを作成します。

#必要なライブラリーのインポート

from sqlalchemy import create_engine

#enginとは　どのデーターベースにどうやって接続するのか、設定を行いその設定内容を保存したもの

#エコー　有効にすると、生成されたすべてのSQLが出力されます

engine = create_engine('sqlite:///candle.db', echo=True)

create_engine（）を使用してengineを作ります。

enginとは、どのデーターベースにどうやって接続するのかの設定を行い、その設定内容を保存したものと考えてください。

engine = create_engine(‘sqlite:///candle.db‘, echo=True)

sqlite：使用するデーターベース

candle.db：作成するデーターベースのファイル名（任意に決められます）

echo=True：このオプションをTrueにすると、SQLAlchemyが生成したSQLを出力する

※ＳＱＬとは、リレーショナルデーターベースの操作を行うための、標準的な言語です。

Ｂａｃｅクラスを作成・Declare a Mapping(マッピング)

declarative_base（）関数を使用して基本クラス（Baceクラス）を作成します。

#必要なライブラリーをインポート
from sqlalchemy.ext.declarative import declarative_base

#declarative_base（）関数を使用して基本クラスを作成します。
Base = declarative_base()

#必要なライブラリーをインポート

from sqlalchemy.ext.declarative import declarative_base

#declarative_base（）関数を使用して基本クラスを作成します。

Base = declarative_base()

ベース（Base）ができたので、マップされたクラスを定義する事ができます。

Baseクラスを継承してテーブルを定義

Baseを継承してCandleというクラスを作ります。

Candleという新しいクラスが、このテーブルをマップするクラスになります。

クラス内で、マッピング先のテーブルに関する詳細、主にテーブル名、列の名前とデータ型を定義します。

Declarativeを使用するクラスには、少なくともtablename属性と、主キ－が必要です。

#クラスを継承してテーブルを定義する

#必要なライブラリーをインポート
from sqlalchemy import Column
from sqlalchemy import DateTime
from sqlalchemy.types import Float
from sqlalchemy.types import Integer
from sqlalchemy.types import String


class Candle(Base):
    __tablename__ = 'appl_1D'
    id = Column(Integer, primary_key=True, autoincrement=True) # 主キーautoincrement設定
    time = Column(DateTime, nullable=False)
    name = Column(String(64), nullable=True)
    open = Column(Float)
    close = Column(Float)
    high = Column(Float)
    low = Column(Float)
    volume = Column(Integer)

#クラスを継承してテーブルを定義する

#必要なライブラリーをインポート

from sqlalchemy import Column

from sqlalchemy import DateTime

from sqlalchemy.types import Float

from sqlalchemy.types import Integer

from sqlalchemy.types import String

class Candle(Base):

__tablename__ = 'appl_1D'

id = Column(Integer, primary_key=True, autoincrement=True) # 主キーautoincrement設定

time = Column(DateTime, nullable=False)

name = Column(String(64), nullable=True)

open = Column(Float)

close = Column(Float)

high = Column(Float)

low = Column(Float)

volume = Column(Integer)

class Candle(Base):

Baseを継承してCandleというクラスを作っています。

__tablename__ = ‘appl_1D’

テーブルの名前を指定しています。

今回はサンプルとしてアップルの株価データー（日足）をデーターベースに保存しますので、テーブルの名前を’appl_1D’としました。

id = Column(Integer, primary_key=True, autoincrement=True)

主キーの設定をしています。

id：データーの列名

Integer：:データー型はInteger

primary_key=True：Trueで主キーであることを指定

autoincrement=True：自動で一意のシーケンス番号を生成

time = Column(DateTime, nullable=False)

time：データーの列名

DateTime：データー型はdatatime

nullable=False：nullを許さない

name = Column(String(64), nullable=True)

name：データーの列名

String(64)：データー型は文字列String(64)

nullable=True：nullを許す

※1銘柄のローソク足データーをデーターベースに登録するだけならば、無くてもよい。

open = Column(Float)

open：データーの列名（始値）

Float：データー型は小数点

nullable=True：nullを許す

volume = Column(Integer)

volume：データーの列名（出来高）

Integer：データー型は整数

データベース作成・Create a Schema（スキーマを作成）

データベース作成を作成します。

先ほどCandleクラスを使用して、テーブルメタデータと呼ばれるテーブルに関する情報を定義しました。

まず、tableの属性を調べてみましょう。

Candle.__table__

1	Candle.__table__

出力結果：

Table(‘appl_1D’, MetaData(bind=None), Column(‘id’, Integer(), table=<appl_1D>, primary_key=True, nullable=False), Column(‘time’, DateTime(), table=<appl_1D>, nullable=False), Column(‘name’, String(length=64), table=<appl_1D>), Column(‘open’, Float(), table=<appl_1D>), Column(‘close’, Float(), table=<appl_1D>), Column(‘high’, Float(), table=<appl_1D>), Column(‘low’, Float(), table=<appl_1D>), Column(‘volume’, Integer(), table=<appl_1D>), schema=None)

キャンドルクラス（class Candle(Base)）で設定した通りですね。

いよいよデーターベースを作成します。

#####このコードでデーターベースが作成される#####
Base.metadata.create_all(engine)

1 2	#####このコードでデーターベースが作成される##### Base.metadata.create_all(engine)

出力結果：

2021-09-20 08:09:58,321 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test plain returns’ AS VARCHAR(60)) AS anon_1
2021-09-20 08:09:58,323 INFO sqlalchemy.engine.base.Engine ()
2021-09-20 08:09:58,324 INFO sqlalchemy.engine.base.Engine SELECT CAST(‘test unicode returns’ AS VARCHAR(60)) AS anon_1
2021-09-20 08:09:58,326 INFO sqlalchemy.engine.base.Engine ()
2021-09-20 08:09:58,328 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(“appl_1D”)
2021-09-20 08:09:58,330 INFO sqlalchemy.engine.base.Engine ()
2021-09-20 08:09:58,331 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(“appl_1D”)
2021-09-20 08:09:58,333 INFO sqlalchemy.engine.base.Engine ()
2021-09-20 08:09:58,335 INFO sqlalchemy.engine.base.Engine
CREATE TABLE “appl_1D” (
id INTEGER NOT NULL,
time DATETIME NOT NULL,
name VARCHAR(64),
open FLOAT,
close FLOAT,
high FLOAT,
low FLOAT,
volume INTEGER,
PRIMARY KEY (id)
)

2021-09-20 08:09:58,336 INFO sqlalchemy.engine.base.Engine ()
2021-09-20 08:09:58,347 INFO sqlalchemy.engine.base.Engine COMMIT

今は、出力結果の意味が分からなくてもＯＫ！

データーベースファイルの作成・Connecting（接続）で下の設定をしました。

#エコー　有効にすると、生成されたすべてのSQLが出力されます
engine = create_engine(‘sqlite:///candle.db’, echo=True)

この設定により、生成されたすべてのSQLが出力されました。

※ＳＱＬとは、リレーショナルデーターベースの操作を行うための、標準的な言語です。

セッション・Ｓｅｓｓｉｎオブジェクトを作る

セッション・Ｓｅｓｓｉｎオブジェクトを作ります。

データベースとの通信を開始する準備が整いました。

データベースに対するORMの「ハンドル」はセッションです。

Sessionクラスを定義します。

#必要なライブラリーをインポート
from sqlalchemy.orm import sessionmaker

#Sessionクラスを定義します。
Session = sessionmaker(bind=engine)

#必要なライブラリーをインポート

from sqlalchemy.orm import sessionmaker

#Sessionクラスを定義します。

Session = sessionmaker(bind=engine)

セッションオブジェクトを作ります。

###### セッションオブジェクトを作る ######
session = Session()

1 2	###### セッションオブジェクトを作る ###### session = Session()

また意味の分からない言葉が出てきましたか？

大丈夫です！

モヤモヤしている方は、全体の流れを一通りご覧いただいてから、また戻って来て下さい。

データーを書き込むためのオブジェクトを作成・Create an Instance of the Mapped Class

データーを書き込むためのオブジェクトを作成します。

まずやってみましょう。

Candle_dataオブジェクトを作成して,データーの中身を確認します。

#データーを書き込むためのオブジェクト　下の例では　candle_data1　オブジェクト

candle_data1 = Candle(name='name_test_data1', open=200, close=300, high=400, low=100)

#データーを書き込むためのオブジェクト　下の例では　candle_data1　オブジェクト

candle_data1 = Candle(name='name_test_data1', open=200, close=300, high=400, low=100)

株価のローソク足は、ある一定期間の始値・終値・高値・安値で構成されます。（4本値と呼ばれます）

今回の解説では、4本値に加えて、nameも追加しています。

candle_data1 = Candle(name=’name_test_data1′, open=200, close=300, high=400, low=100)

candle_data1：データーベースにデーターを書き込むためのオブジェクトを作成（オブジェクト名はcandle_data1）

name=’name_test_data1’：nameは　name_test_data1

open=200：始値200

close=300：終値300

high=400：高値400

low=100：安値100

オブジェクトcandle_data1内nameの値を確認

candle_data1.name

1	candle_data1.name

出力結果：’name_test_data1′

オブジェクトcandle_data1内openの値を確認

candle_data1.open

1	candle_data1.open

出力結果：200

日時のデーターを扱う準備・datetimeインポート[ローソク足]

日時のデーターを扱う準備をします。

#必要なライブラリーをインポート
import datetime

#現在の日付時刻を取得    
now = datetime.datetime.now( )

print (now)

#必要なライブラリーをインポート

import datetime

#現在の日付時刻を取得

now = datetime.datetime.now( )

print (now)

出力結果：2021-09-20 08:59:26.213423

私がこのコードを走らせた日付と時刻が出力されています。

2021年9月20日　8時59分26秒＋小数点以下

Candle_dataオブジェクトに日時データーを追加します。

Candle_data1 = Candle(time=now, name='name_test_data1', open=200, close=300, high=400, low=100)

1	Candle_data1 = Candle(time=now, name='name_test_data1', open=200, close=300, high=400, low=100)

Candle_dataオブジェクト内timeの中身を確認します。

Candle_data1.time

1	Candle_data1.time

出力結果：datetime.datetime(2021, 9, 20, 8, 59, 26, 213423)

セッションにａｄｄする・Adding and Updating Objects

やっと終わりが見えてきました。

あともう一息です。

先ほど作成したcandle_data1オブジェクトを、

Candle_data1 = Candle(time=now, name=’name_test_data1′, open=200, close=300, high=400, low=100)

セッションにａｄｄします。

Candle_data1オブジェクトを永続化するには、Session.add（）をSessionに追加します。

candle_data1 = Candle(time=now, name='name_test_data1', open=200, close=300, high=400, low=100)

######　セッションにａｄｄする ######
session.add(candle_data1)

candle_data1 = Candle(time=now, name='name_test_data1', open=200, close=300, high=400, low=100)

######　セッションにａｄｄする ######

session.add(candle_data1)

この時点でも、インスタンスは保留中で、SQLはまだ発行されていません。

よってオブジェクトはデータベースに登録されていません。

コミットして永続化・ここでやっとデーターベースに書き込まれる

長らくお待たせしました。

ここでやっとデーターベースに書き込まれます。

##### コミットして初めて永続化決定 ########
session.commit()

1 2	##### コミットして初めて永続化決定 ######## session.commit()

出力結果：

2021-09-20 09:11:51,242 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-09-20 09:11:51,245 INFO sqlalchemy.engine.base.Engine INSERT INTO “appl_1D” (time, name, open, close, high, low, volume) VALUES (?, ?, ?, ?, ?, ?, ?)
2021-09-20 09:11:51,246 INFO sqlalchemy.engine.base.Engine (‘2021-09-20 09:11:51.177072’, ‘name_test_data1’, 200.0, 300.0, 400.0, 100.0, None)
2021-09-20 09:11:51,250 INFO sqlalchemy.engine.base.Engine COMMIT

長い道のりでしたが、session.commit()によってデーターベースへの書き込みが完了します。

別の言い方で「永続化」されます。

「永続化」なんて一般的に使われることは少ない言葉ですよね。

パソコンの電源が切れても、失われない状態になった状態を言います。

余計に分かりにくいですか？

無事にデーターがデーターベースに保存された状態になったと理解して良いでしょう。

ローソク足データーを用意する[ローソク足]

データーベースに登録するローソク足は、たまたま手元にあったアップルの日足データーを使います。

CSV形式のファイルなので、まずデーターフレームに読み込んで中身を確認しましょう。

#必要なライブラリーをインポート
import pandas as pd

#CSVデーターをデーターフレームに読み込む
appl_df = pd.read_csv('aapl_2015_2019.csv')

#データーフレームを表示させる
appl_df

#必要なライブラリーをインポート

import pandas as pd

#CSVデーターをデーターフレームに読み込む

appl_df = pd.read_csv('aapl_2015_2019.csv')

#データーフレームを表示させる

appl_df

出力結果：

データーは2015年1月2日から2019年12月31日まで。

データー型を確認して変換

データーの型を確認します。

appl_df.dtypes

1	appl_df.dtypes

出力結果：

date object
open float64
high float64
low float64
close float64
volume int64
dtype: object

date がoblect型なのでdatetime型に変換します。

#pandasのto.datetimeを使う
#フォーマットを予め指定しておくかinfer_datetime_format=Trueにしておくと高速化できる。

appl_df['date'] = pd.to_datetime(appl_df['date'], infer_datetime_format=True)

#pandasのto.datetimeを使う

#フォーマットを予め指定しておくかinfer_datetime_format=Trueにしておくと高速化できる。

appl_df['date'] = pd.to_datetime(appl_df['date'], infer_datetime_format=True)

もう一度データーの型を確認しましょう。

appl_df.dtypes

1	appl_df.dtypes

出力結果：

date datetime64[ns]
open float64
high float64
low float64
close float64
volume int64
dtype: object

date がdatetime64[ns]型になっています。

今回用意したアップルのローソク足は日足ですが、分足にも対応できるようにdatetimeにしておきます。

ローソク足データーをデーターベースに読み込む[ローソク足]

ローソク足データーをデーターベースに読み込みましょう。

データフレームとして読み込んだローソク足データーを、iterrows()で1行づづ読み込みます。

最初の1件だけデーターをみてみましょう。

#データーの中身を見てみる

for index, _df in appl_df.iterrows():
    print(_df)
    break

#データーの中身を見てみる

for index, _df in appl_df.iterrows():

print(_df)

break

出力結果：

date 2015/1/2
open 111.39
high 111.44
low 107.35
close 109.33
volume 53204626
Name: 0, dtype: object

for index, _df in appl_df.iterrows():

for文によりループ処理で1行データーを読み込みます。

print(_df)

読み込んだデーターを出力

break

for文から抜ける

これにより、1行分のデーターを見る事が出来ました。

データーベースに読み込む関数を作る

データーベースに格納する関数を作ります。

#データーベースに読み込む

def read_data():
    for index, _df in appl_df.iterrows():
        row = Candle(time=_df['date'],
                     name='appl',
                     open=_df['open'], 
                     close=_df['close'],
                     high=_df['high'], 
                     low=_df['low'],
                     volume=_df['volume'])
        session.add(row)
    session.commit()