O/R マッパー経由でデータベースを使う¶

CSV ファイルから読み込んだ情報をデータベースに登録します。データベースには SQLite を使い、O/R マッパー (オブジェクト関係マッピング) として SQLAlchemy を使います。

SQLite - sqlite.org
SQLAlchemy - sqlalchemy.org

SQLAlchemy を使っておくと、後から MySQL や PostgreSQL へ変更することもできますし、複数の環境で異なるデータベースに接続して動かすこともできるようになります。

SQLAlchemy の準備¶

requirements.txt に SQLAlchemy を追加し、 pip を実行します。

$ echo "SQLAlchemy" >> requirements.txt
$ pip install -r requirements.txt

Python のインタラクティブシェルからモジュールを読み込めることを確認します。

$ python
>>> import sqlalchemy
>>> help(sqlalchemy)

help() 関数でモジュールのヘルプが (大量に) 表示されますので眺めてみてください。

O/R マッパー経由でデータベースに保存¶

O/R マッパーを使うためにはいくつかの準備が必要になります。

sqlalchemy モジュールの読み込み
データオブジェクトの定義
データベースエンジンの作成 (SQLite の場合はファイル)
オブジェクトからデータベースのテーブルを生成
オブジェクトをデータベースに投入
- トランザクションを開始
- オブジェクトを生成 (必要な回数だけ繰り返し)
- トランザクションをコミット

以上をスクリプトにまとめると次のようになります。(csv-6.py)

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""Parse daily Tokyo stock prices.
"""

import csv
import datetime

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, Float, String, Date

from pyschool.cmdline import parse_args

# SQLite On-Memory storage for test run.
DEFAULT_SQLITE_FILE = ':memory:'

# Declare input fields definition.
FIELDS = (
    {'id': 'day', 'type': 'datetime', 'format': '%Y-%m-%d'},
    {'id': 'price_begin', 'type': 'float'},
    {'id': 'price_max', 'type': 'float'},
    {'id': 'price_min', 'type': 'float'},
    {'id': 'price_end', 'type': 'float'}
)

Session = sessionmaker()
Base = declarative_base()


class StockPrice(Base):

    __tablename__ = 'stock_price'

    id = Column(Integer, primary_key=True)
    day = Column(Date, nullable=False, unique=True)
    price_begin = Column(Float, nullable=False)
    price_max = Column(Float, nullable=False)
    price_min = Column(Float, nullable=False)
    price_end = Column(Float, nullable=False)

    def __repr__(self):
        return "<StockPrice('{}')>".format(self.day)

    def diff(self):
        return self.price_end - self.price_begin


def process(args):
    """Parse daily Tokyo stock prices, and calculate up/down.
    After that, import them into SQLite database.
    """
    # Prepare database connection, table, and session.
    dsl = 'sqlite:///' + (args.output or DEFAULT_SQLITE_FILE)
    engine = create_engine(dsl, echo=True)
    Base.metadata.create_all(engine)
    Session.configure(bind=engine)
    session = Session()
    with open(args.filename[0]) as fp:
        reader = csv.reader(fp)  # Instantiate CSV reader with file pointer.
        for t in reader:
            # Convert input values to declared name and type.
            dt = {}
            for i, f in enumerate(FIELDS):
                if f['type'] == 'integer':
                    dt[f['id']] = int(t[i])
                elif f['type'] == 'float':
                    dt[f['id']] = float(t[i])
                elif f['type'] == 'datetime':
                    dt[f['id']] = datetime.datetime.strptime(t[i], f['format'])
                else:
                    dt[f['id']] = t[i]
            # Instantiate SQLAlchemy data model object.
            p = StockPrice(**dt)
            # Show the same things with previous scripts.
            diff = p.diff()
            if diff > 0:
                message = 'up'
            elif diff < 0:
                message = 'down'
            else:
                message = 'same'
            # Write out day, up/down/same, and diff.
            print('{}\t{:5}\t{}'.format(p.day, message, round(diff, 2)))
            session.add(p)
    # Don't forget to commit the changes you add.
    session.commit()


def main():
    args = parse_args()
    process(args)


def test():
    pass

if __name__ == '__main__':
    main()

# vim: set et ts=4 sw=4 cindent fileencoding=utf-8 :

１列目を「日付」として扱うために datetime モジュールを利用しています。日付と時刻の処理は多くのプログラムで必要になりますので、少しずつ使い方に慣れていってください。

コマンドラインで実行するときは、 –output オプションにファイル名を指定するとそのファイルをデータベースとして使用します。オプション未指定の場合はオンメモリで動作します。 ファイルを読み込む で利用したデータを入力として与えてください。

$ python csv-6.py csv-1.csv
$ python csv-6.py --output=csv-6.sqlite csv-1.csv

Note

トランザクションを開きっ放しにして大量のデータを扱うとメモリエラーになります。マシンのメモリより大きいか、メモリをたくさん使うようなデータを扱う場合はこまめにコミットさせましょう。組み込み関数 enumerate() を使ってループ回数をカウントし、適当な回数ごとにコミットすると良いでしょう。また、処理の進捗状況をレポートしておくと、バッチ実行したときに安心感があります。

宿題¶

１年分のデータを SQLite に登録してください。株価データダウンロードサイト (k-db.com) から日経平均株価の日足を利用させて頂きましょう。
データベースからレコードを抽出する処理を記述し、１年間の株価の動きをまとめてみましょう。始値から終値が上がった日と下がった日の数を集計してください。
データベースを SQLite から MySQL または PostgreSQL に切り替えるために必要な手順をドキュメントにまとめてください。
日付フィールド (day) には UNIQUE 制約を課していますので、オンメモリでないデータベースに対して同じ入力ファイルで複数回実行すると、制約違反になってしまいます。入力された日付のレコードがデータベースに存在するかを確認する処理を実装してください。

以上で、CSV に関する処理は一通り学習できたと思います。手持ちのデータを処理するために、自分用のプロジェクトを作成してみましょう。

Python School 2.0.0 documentation