price_flask.py #5 表示件数と範囲の指定 & 指定のページ内のデータを取得
price_flask.py # 表示件数 length = 10 # ページ数 pages = math.floor(total / length) + 1 # クエストリングにページ数を渡す。 # pageを指定しない場合=0, つまり先頭を表示する。 page = request.args.get("page", default=1, type=int) page = 1 if page < 1 else page page = pages if page > pages else page offset = (page - 1) * length limit = length # 指定の範囲を抽出 cur.execute("select *, strftime('%Y-%m-%d %H:%M:%S', cast( Timestamp as BIGINT), 'unixepoch') as datetime from event limit {0} offset {1}".format(limit, offset)) rows = cur.fetchall() # 指定の範囲を抽出 cur.execute("select *, strftime('%Y-%m-%d %H:%M:%S', cast( Timestamp as BIGINT), 'unixepoch') as datetime from event") all_rows = cur.fetchall() pagination = { "page": page, "length":length, "pages": pages, "total": total } return render_template("price_table.html",rows = rows,all_rows = all_rows, pagination=pagination)
ここでは10件ずつの表示としています。
# 表示件数 length = 10
【ページの範囲の指定】
◆ページの総数は以下で求めます。 半端な数も表示できるよう、少数を切り捨て1足しています。
# ページ数 pages = math.floor(total / length) + 1
例)総数45件、表示件数10件とした場合、 total / length = 4.5 pages = math.floor(total / length) + 1 = 5 よって、ページの総数は5ページです。
◆次に、指定のページが範囲内か確認してます。
エラーが起きないようにチェックしています。
# クエストリングにページ数を渡す。 # pageを指定しない場合=1, つまり先頭を表示する。 page = request.args.get("page", default=1, type=int) # 渡されたページ数が1未満の場合は1としてます。 page = 1 if page < 1 else page # 渡されたページ数がページの総数を超えた場合は最後のページを渡します。 page = pages if page > pages else page
【指定のページ内のデータを取得】
◆最後に指定のページ内のデータを取得します。
offset: 指定ページの最初のデータの位置
limit:ページに表示する件数
offset = (page - 1) * length limit = length
例)総数45件、表示件数10件として3ページ目を指定した場合
offset = (3 - 1) * 10 = 20
limit = 10
データの先頭はrows[0]
となるため、
・1ページ目は0 - 9
・2ページ目は10 - 19
・3ページ目は20 - 29
・4ページ目は30 - 39
・5ページ目は40 - 45
となります。
【例&まとめ】
offset: 指定ページの最初のデータの位置 limit:ページに表示する件数
<条件> length = 10 total = 65の場合 4ページ目を指定した場合 <コード> pages = math.floor(total / length) + 1 offset = (page - 1) * length limit = length #pages = 7 #offset = (4 - 1) * 10 つまり offset = 30 #limit = 10
つまり、4ページ目を指定したら30番目から10件表示する。
【SQLで範囲の指定】
formatメソッド
year = 2018 month = 'April' day = '21th' print('Today is {0} of {1} {2}'.format(day,month,year))
実際に使ったコード
# 指定の範囲を抽出 cur.execute("select *, strftime('%Y-%m-%d %H:%M:%S', cast( Timestamp as BIGINT), 'unixepoch') as datetime from event limit {0} offset {1}".format(limit, offset)) rows = cur.fetchall()
順に読み解いていくと
① select * from event limit {0} offset {1}".format(limit, offset)
ここで、pythonのformat関数を使っています。 SELECT * FROM テーブル名; なので イベントテーブルの全列で条件がlimitiとoffset
limitは表示件数、offsetは取得の開始位置を表します。
つまり、3ページ目の場合は select * from event limit 10 offset 20 となっています。
② strftime('%Y-%m-%d %H:%M:%S', cast( Timestamp as BIGINT), 'unixepoch') as datetime
テーブルのTimestampには時間が入っていましたが、
「UNIX時間 (unix epoch)」と呼ばれる形式の数値が文字列で入っていました。
以下の順で行う
- cast( Timestamp as BIGINT) で字列の数値を数値型に変換する
- UNIX時間をYYYY-mm-dd HH:MM:SSの表記に変更する
- rows = cur.fetchall() でこれでrowsに表示する情報を格納している。
1. まず、文字列の数値を数値型に変換します。
cast( Timestamp as BIGINT)
ここで型(cast)を変換します。BIGINTは整数型にしています。
"1524155674" -> 1524155674
その他の型 https://so-zou.jp/web-app/tech/database/sqlite/data/data-type.htm
2. UNIX時間をYYYY-mm-dd HH:MM:SSの表記に変更
例)2018年4月20日 01:34:34 <--> 1524155674
sqliteではstrftime()を使って日付の表記を変更できます。
例)select strftime('%m月%d日', '2009-08-24 23:10:15'); / 08月24日 /
参考:https://www.dbonline.jp/sqlite/function/index8.html
注意点!
UNIX時間から変換する場合は、
以下のように3つ目の引数に'unixepoch'を指定してあげて下さい。
strftime('%Y-%m-%d %H:%M:%S', 1524155674, 'unixepoch')
/ 2018年4月20日 01:34:34 /
【余談】
デフォルトでは昇順に取得します。
大きいもの順(降順)は 末尾に、ORDER BY column名 DESC
小さいもの順(昇準)は 末尾に、ORDER BY column名 ASC
※小文字でも大丈夫です
日付で降順(最新のデータが先頭)に取り出す場合は、
select * from event limit 10 offset 20 order by timestamp desc
としてください。
参考:http://sql-oracle.com/sqlserver/?p=431
3. rows = cur.fetchall() でこれでrowsに表示する情報を格納
③rows = cur.fetchall()
これでrowsに表示する情報を格納している。
【グラフ用のデータ取得方法】
これも同じようにデータを取得する。 テーブルとは違い、limitはない。全データの取得をしている。
# 指定の範囲を抽出 cur.execute("select *, strftime('%Y-%m-%d %H:%M:%S', cast( Timestamp as BIGINT), 'unixepoch') as datetime from event") all_rows = cur.fetchall()
以上、指定のページの範囲のデータを取得方法でした。
【まとめ】
① length = 10 #表示件数決める
②pages = math.floor(total / length) + 1 #ページ数決める
③offset = (page - 1) * length #テーブルに表示させる開始位置を決める
④limit = length #リミット決める
⑤cur.execute… #で情報取得
⑥rows = cur.fetchall() #でテーブル用の情報格納
⑦all_rows = cur.fetchall() #でグラフ用の情報格納
つまり