SQLite3を使ってデータベース化したティッカー情報抽出に挑戦してみた

データベースの操作処理　

前回では、データベースのファイルを作って、テーブルを設定し、ティッカーの情報を格納するところまで終わった。

今回は、データベースからデータを加工編集抽出して、Pythonにリストとして保存するまで挑戦しようと思う。

データを連番にして情報を入れる

前回までのコードは、こちら。前回は、SQLite3データベースを作成して、テーブルを登録しデータを格納するところまでだった。

# -*- coding: utf-8 -*- #
"""
We crush capitalism.
"""

import pybitflyer
import csv
import time
import sqlite3
"""
初期設定
"""
Data_Base = sqlite3.connect("furture.db")
Data_cur = Data_Base.cursor()
Data_cur.execute('CREATE TABLE IF NOT EXISTS furture(symbol text, timestamp datatime, lpt real, ask real, bid real, ask_size real, bid_size real, ask_depth real, bid_depth real, volume_product real, volume real)')
Data_Base.commit()
Data_Base.close()

Start_time = time.perf_counter()
Ticker_symbol = ["BTC", "ETH"]

while True:
   print(Start_time)
   print(time.perf_counter())
   """
   ティッカー操作処理
   """
   for SYMBOL in Ticker_symbol:
      """
      ティッカーからデータ取得
      """
      CODE = SYMBOL + "_JPY"
      tick = pybitflyer.API().ticker(product_code=CODE)
      timestamp = tick["timestamp"]
      ltp = tick["ltp"]
      ask = tick["best_ask"]
      bid = tick["best_bid"]
      ask_size = tick["best_ask_size"]
      bid_size = tick["best_bid_size"]
      ask_depth = tick["total_ask_depth"]
      bid_depth = tick["total_ask_depth"]
      volume_product = tick["volume_by_product"]
      volume = tick["volume"]
      """
      データベースへ格納
      """
      Data_Base = sqlite3.connect("furture.db")
      Data_cur = Data_Base.cursor()
      Data_cur.execute('INSERT INTO furture values (?,?,?,?,?,?,?,?,?,?,?)',[SYMBOL, timestamp, ltp, ask, bid, ask_size, bid_size, ask_depth, bid_depth, volume_product, volume])
      Data_Base.commit()
      """
      実行結果の表示
      """
      Data_cur.execute("SELECT * FROM furture")
      for Row in Data_cur:
         print(str(Row[0]) + "," + str(Row[1]) + "," + str(Row[2]))
      Data_Base.close()
      """
      CSVへの書き込む
      """
      with open('furture.csv','a',newline='') as files:
         writer = csv.writer(files)
         writer.writerow([SYMBOL,str(timestamp),str(ltp),str(ask),str(bid),str(ask_size),str(bid_size),str(ask_depth),str(bid_depth),str(volume_product),str(volume)])
   time.sleep((10 + Start_time ) - time.perf_counter())
   Start_time = Start_time + 10

前回のコードでは、データベースにIDが設定されていなかった。そこで、今回は、検索を簡単にするため、IDに連続で番号が設定されるようにしよう。

カラムに対して INTEGER PRIMARY KEY を設定した場合、データを追加した時に INTEGER PRIMARY KEY を設定したカラムの値を指定しないと自動的に値が格納されます。自動的に格納される値は、対象のカラムに格納されている最大の値に1を加えた値となります。この値は以前に割り当てられたことがあるかどうか関係がないため、データの追加と削除を繰り返していると以前に格納されたことがある値が再度カラムに格納される場合があります。
カラムに INTEGER PRIMARY KEY を設定する時に AUTOINCREMENT を合わせて設定すると自動的に設定される値のルールが変わります。対象のカラムに現在格納されている最大の値に1が加えられるのではなく、対象のカラムに今までに格納されたことのある最大の値に1が加えられた値が格納されるようになります。

そこで、初期設定の一部を修正する。前回は、ティッカーの11情報を格納するようテーブルに設定したけど、ID項目を加えよう。設定方法は項目名にスペースを空けてINTEGER PRIMARY KEYと書けばいい。

Data_cur.execute('CREATE TABLE IF NOT EXISTS furture(ID INTEGER PRIMARY KEY, symbol text, timestamp datatime, lpt real, ask real, bid real, ask_size real, bid_size real, ask_depth real, bid_depth real, volume_product real, volume real)')

次に、データベースへ格納するコードを書き直す。テーブル名furtureの後ろの()の中にID以外の情報を記載する。こうすれば、IDは自動的に連番の状態で格納される。

Data_cur.execute('INSERT INTO furture(symbol, timestamp, lpt, ask, bid, ask_size, bid_size, ask_depth, bid_depth, volume_product, volume) values (?,?,?,?,?,?,?,?,?,?,?)',[SYMBOL, timestamp, ltp, ask, bid, ask_size, bid_size, ask_depth, bid_depth, volume_product, volume])

データの並び替えと抽出

次はデータベースの抽出方法だ。テーブルのデータを取得するにはSELECT文を使う。Pythonの関数execute()の中にクォーテーションで囲まれたものが、SQLite3の命令文だ。

テーブルに格納されたデータを取得するには SELECT 文を使用します。基本となる書式は次の通りです。

SELECT カラム名1, カラム名2, ... FROM テーブル名;

SELECTの後ろに、抽出する条件を設定することができる。データベースでは、ifではなくwhereを使う。

SELECT 文でデータを取得する時に条件を指定しなければテーブルに含まれる全てのデータを取得しますが、 WHERE 句を使うことで条件に一致したデータだけを取得することができます。ここでは WHERE 句を使って取得するデータの条件を設定する方法について解説します。

SELECT カラム名 , ... FROM テーブル名 WHERE 条件式;

次は並び替えだ。whereの条件式の後ろにorder byと書く。ちなみにASCが昇順、DESCが降順。

SELECT 文を使ってデータを取得すると取得元のテーブルに格納されている順番に従って値を取得しますが、 ORDER BY 句を使用することで指定したカラムを基準に並べ替えを行ってからデータを取得することもできます。ここでは ORDER BY 句を使ってデータをソートした上で取得する方法について解説します。

SELECT カラム名 , ... FROM テーブル名 ORDER BY カラム名 [ASC | DESC];

今回は、選択した仮想通貨のシンボルコードだけを抽出して、降順に並べた。出力されるデータは、タプル形式なので二行目のコードの後ろは、(A,SYMBOL)ではないから注意。

A = 'select * from furture where symbol = ? ORDER BY ID DESC'
   Data_cur.execute(A, (SYMBOL,))

そして、最後がデータの取得方法。全部のデータを取得したいならfetchall、一番最初の一つならfetchone、指定された行数ならfetchmanyと使い分けることができる。

- データ取得方法　その１
　　Cursor.fetchall
　　　全部のレコードの結果をもってくる
- データ取得方法　その２
　　Cursor.fetchmany
　　　指定された行数のデータをもってくる。
- データ取得方法　その３
　　Cursor.fetchone
　　　１件ずつデータをもってくる

今回は、抽出したデータのうち、24を抽出するので、fetchmany(24)にして、NewDataにタプルとして代入した。
そして、処理結果を画面に表示している。最後の行のrow[数字]の中に抽出するテーブル番号を入れると、それだけを表示してくれる。

  NewData = Data_cur.fetchmany(24)
   Data_Base.close()
   print([row[2] for row in NewData])

順番に説明すると、データベースの中からETHだけを抽出して、降順にソートしてから、上から24番目までのデータをリストとして引数に代入した。プログラムを動かしてみると、24件のデータを抽出しているのがわかる。ボクが検証のために抽出パターンを変えているので、実際に動かすと下の画像と違う結果になるので注意。

完成したコードはこちら。データベースの扱いがわかれば大容量データも高速で処理できる。データベースで出力されたリストは、二次配列で作成されている。次回は、二次元配列の展開に挑戦しよう。

# -*- coding: utf-8 -*- #
"""
We crush capitalism.
"""

import pybitflyer
import csv
import time
import sqlite3
"""
初期設定
"""
Data_Base = sqlite3.connect("furture.db")
Data_cur = Data_Base.cursor()
Data_cur.execute('CREATE TABLE IF NOT EXISTS furture(ID INTEGER PRIMARY KEY, symbol text, timestamp datatime, lpt real, ask real, bid real, ask_size real, bid_size real, ask_depth real, bid_depth real, volume_product real, volume real)')
Data_Base.commit()
Data_Base.close()

Start_time = time.perf_counter()
Ticker_symbol = ["BTC", "ETH"]

while True:
   print(Start_time)
   print(time.perf_counter())
   """
   ティッカー操作処理
   """
   for SYMBOL in Ticker_symbol:
      """
      ティッカーからデータ取得
      """
      CODE = SYMBOL + "_JPY"
      tick = pybitflyer.API().ticker(product_code=CODE)
      timestamp = tick["timestamp"]
      ltp = tick["ltp"]
      ask = tick["best_ask"]
      bid = tick["best_bid"]
      ask_size = tick["best_ask_size"]
      bid_size = tick["best_bid_size"]
      ask_depth = tick["total_ask_depth"]
      bid_depth = tick["total_ask_depth"]
      volume_product = tick["volume_by_product"]
      volume = tick["volume"]
      """
      データベースへ格納
      """
      Data_Base = sqlite3.connect("furture.db")
      Data_cur = Data_Base.cursor()
      Data_cur.execute('INSERT INTO furture(symbol, timestamp, lpt, ask, bid, ask_size, bid_size, ask_depth, bid_depth, volume_product, volume) values (?,?,?,?,?,?,?,?,?,?,?)',[SYMBOL, timestamp, ltp, ask, bid, ask_size, bid_size, ask_depth, bid_depth, volume_product, volume])
      Data_Base.commit()
      """
      CSVへの書き込む
      """
      with open('furture.csv','a',newline='') as files:
         writer = csv.writer(files)
         writer.writerow([SYMBOL,str(timestamp),str(ltp),str(ask),str(bid),str(ask_size),str(bid_size),str(ask_depth),str(bid_depth),str(volume_product),str(volume)])
   """
   データの抽出
   """
   A = 'select * from furture where symbol = ? ORDER BY ID DESC'
   Data_cur.execute(A, (SYMBOL,))
   NewData = Data_cur.fetchmany(24)
   Data_Base.close()
   print([row[2] for row in NewData])
   """
   10秒タイマー
   """
   time.sleep((10 + Start_time ) - time.perf_counter())
   Start_time = Start_time + 10

目次

資本主義社会を、誰も傷つけずに修正させる方法を考える｜中島充｜note

次の記事

Pythonで自作関数を作ることに挑戦してみた｜中島充｜note

前の記事

Pythonでティッカー情報をデータベースへの格納に挑戦してみた｜中島充｜note