price_flask.py #３ sqlite3とjinja2について

【なぜSQLite3】

SQLite を使ってアプリケーションのプロトタイプを作り、その後そのコードを PostgreSQL や Oracle のような大規模データベースに移植するらしい。つまり、SQLite 使えばデータベースでめんどくさいことしなくていいらしい。

【SQL超基本】　

①最初にデータベースを表す Connection オブジェクトを作ります。 ②example.db に格納されているデータにアクセスする。 ③connができれば、 Cursor オブジェクトを作る ④そして、execute() メソッドを呼んで SQL コマンドを実行すると言う流れ。　

import sqlite3
conn = sqlite3.connect('example.db')
c = conn.cursor()

# Create table
c.execute('''CREATE TABLE stocks
             (date text, trans text, symbol text, qty real, price real)''')

# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")

# Save (commit) the changes
conn.commit()

# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()

【実際に自分が作ったこCodeを読み解く】

import sqlite3 as sql

@app.route('/price')
def list():

con = sql.connect("price.db")
con.row_factory = sql.Row
cur = con.cursor()
cur.execute("select count(*) from event")
rows = cur.fetchall()

【SQLの説明】

import sqlite3 as sql　#インポートする
con = sql.connect("price.db")　#データベースprice.dbに接続します。
con.row_factory = sql.Row　
cur = con.cursor()　#接続できれば接続を保持したオブジェクトを作成します。
cur.execute("select count(*) from event")　#これでexcute()でSQL文を実行します。
rows = cur.fetchall() #selectで取得した中身はfetchall()で配列にします。全てのrowを読みこんで、リストを返します。

ちょっと待って、3.のrow_factoryってなんや。 row_factoryを使うと、属性を変更して、カーソルと元の行をタプル形式で受け取り、本当の結果の行を返す呼び出し可能オブジェクトにすることができます。これによって、より進んだ結果の返し方を実装することができます。

つまり、各列に列名でもアクセスできるようなオブジェクトを返すことが出来るようになる

【因みにリストとタプルの違い】リストには要素を追加したり、要素を消したりすることもできますが、タプルではできません。

# リスト
list_sample = [1, 2, 3, 4, 5]
 
# タプル
tuple_sample = (1, 2, 3, 4, 5)

【rowsの中身】

ここでrowsの中身がどうなっているか？

rows = [
{"last": "894819.0", "ask": "895166.0", "high": 900000.0, 省略 }, 
{"last": "894819.0", "ask": "895166.0", "high": 900000.0, 省略 }, 
....省略...
]

となっている。そのため、rows[1]["last"]とアクセスすることもできます。実際にターミナルで確認してみると。

>>> import sqlite3 as sql
>>> 
>>> con = sql.connect("price.db")
>>> con.row_factory = sql.Row
>>> cur = con.cursor()
>>> cur.execute("select count(*) from event")
<sqlite3.Cursor object at 0x10165cc70>
>>> rows = cur.fetchall();
>>> rows[1]["last"]
'893881.0'
>>> rows[1][1]
'893881.0'
>>> rows[2][1]
'894445.0'

【ページングの為にデータの総件数を求める】

SQLで件数などをカウントするには

構文
SELECT COUNT(列名) FROM テーブル名;

100件中10件表示させたりするためにはまづ何件あるか知る必要がある。そこで総件数を確認する。

@app.route('/price')
def list():
    con = sql.connect("price.db")
    con.row_factory = sql.Row
    cur = con.cursor()

    cur.execute("select count(*) from event")
    result = cur.fetchall()
    total = result[0][0]

SQL文ではデータの数を求める時count(*)を使う。 cur.execute("select count(*) from event")　とすることで総件数が求めれる。 result = cur.fetchall()　とした時点で結果は数字になっているので、total = result[0][0]をやってtotalには総数が代入する。

ターミナルで確認して見ると

>>> cur.execute("select count(*) from event")
<sqlite3.Cursor object at 0x10165cce0>
>>> result = cur.fetchall()
>>> total = result[0][0]
>>> total
14

ちょっとした応用。例えば「askが894819.0以上の数」を求める場合は以下のようにします。 select count(*) from event wehere ask >= 894819.0

cur.execute("select count() from event") cur.execute("select count() from event wehere ask >= 800.0")

SQLで最大値を取得するには
SELECT MAX(列名) FROM テーブル名;

参考：SQL カウント(COUNT)