データベース(postgreSQL)を利用した簡単なlinebotアプリを作る(データベース移行編・とりあえず完成!)

baku1101.hatenablog.com
これの続きです.
前回は,データベースのファイルをローカル(heroku上)で保管するような仕様にしていたら,heroku上にファイルの保存はできないことが判明してどうしようかというところでした.

データベースどうする?

とりあえずheroku flask データベースとかで調べると,SQLAlchemyってやつを使う記事が大量にでてきます.

SQLAlchemyとは?

これはこの記事を見たほうが早いですね.
端的に言うと,ORMというSQLをオブジェクトとして扱う概念があり,これのpythonのモジュールがSQLAlchemyってやつです(多分).sqlを生で書かないので記述が楽,sqlインジェクションなどのセキュリティを気にする必要がない,同一の記述で様々な種類のRDBMSに対応できるなどの利点がある一方,生成されるsqlコードを結局気にする必要がある,細かい指示ができないなどの欠点もあります.
個人的にはもうsql文で書いてしまったので,これを再利用したい気持ちがあるのでできればこれは使いたくないです..

本当にSQLiteは使えない?

heroku上のデータベースでなくとも,外部のデータベースを利用すればいい話なので,本当にsqlite3で書くことはできないのか?と思いました.が,ローカルのファイルにしか対応していないみたいです..まあしょうがない(参考: SQLite - PycharmのデータベースツールでリモートホストのSQLiteに接続出来ない|teratailなど)

herokuのpostgreSQLを使う

生のsql文を使いたい,sqliteは使えないとなったらこれが一番楽そうだなと思いました.postgreSQLならリモートのデータベースにも接続できます.
pythonpostgreSQLに接続するためにはpsycopg2というモジュールをインストールする必要がありますが,ここでちょっとエラーがでたのでメモ.

$ pip3 install psycopg2
Collecting psycopg2
Downloading https://files.pythonhosted.org/packages/23/7e/93c325482c328619870b6cd09370f6dbe1148283daca65115cd63642e60f/psycopg2-2.8.2.tar.gz (368kB)
    100% |████████████████████████████████| 368kB 484kB/s 
    Complete output from command python setup.py egg_info:
    running egg_info
    creating pip-egg-info/psycopg2.egg-info
    writing pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing dependency_links to pip-egg-info/psycopg2.egg-info/dependency_links.txt
    writing top-level names to pip-egg-info/psycopg2.egg-info/top_level.txt
    writing manifest file 'pip-egg-info/psycopg2.egg-info/SOURCES.txt'
    Error: b'You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.\n'

postgresql-server-devをインストールしろと言われてるのでそのとおりにします.

$ sudo apt install postgresql-server-dev-10

もう一度pipをやってインストールできればok.

postgreSQLをつかって書き直す

ということでsql文を書き直します.
psycopg2を使う上で参考になったサイト:
psycopg2 でよくやる操作まとめ - Qiita
PythonでPostgreSQLに接続してみた - 薮蛇なエンジニアの開発備忘録
Pythonのmodule「psycopg2」を使ってPostgreSQLへ接続する - ITの隊長のブログ
PostgreSQLをPythonからpsycopg2を使っていじる — そこはかとなく書くよん。

幸運なことにsqlite3とほとんど方言が変わりませんでした.変わったところをかいつまんで行きます.

import datetime
import time
import psycopg2
import os
import sys

url = os.getenv('DATABASE_URL', None)
if url is None:
    print('must set DATABASE_URL')
    sys.exit(1)

データベースが参照できるURLはherokuの環境変数のDATABASE_URLとして登録されています.ローカルでテストする時は自分でexportしていました.セットされていなかった場合は標準出力にprintする(これはherokuのlogに出てくる).
ローカルのpostgreSQLのデータベース作成の参考にしたサイト:CentOS で PostgreSQL を使ってみよう!(2) | Let's Postgres

続き

con = psycopg2.connect(url)
cur = con.cursor()

def CreateTable(usrname):
    cur.execute("CREATE TABLE IF NOT EXISTS {} (year int, month int, start timestamp, finish timestamp, id serial primary key);".format(usrname))
# primary keyの型はint でなく serial じゃないとauto incrementが適用されないので注意

def DropTable(usrname):
    cur.execute("DROP TABLE IF EXISTS {}".format(usrname))
    con.commit()

def Start(usrname):
    CreateTable(usrname)
    if isExistTable(usrname + '_tmp'):
        return False
    cur.execute("CREATE TABLE {}_tmp(start timestamp)".format(usrname))
    start = datetime.datetime.now()
    cur.execute("INSERT INTO {}_tmp(start) values (%s)".format(usrname), (start,))
    con.commit()
    return True

primary keyの型がinteger から serialになっていたり,valuesの中身が ? から %sになっていたりと,方言による細かい変更はありますがほとんどそのままでできました.良かった.

# 直近の1列削除
def DeleteRow(usrname):
    cur.execute("SELECT max(id) FROM {}".format(usrname))
    bottom = cur.fetchone()[0]
    cur.execute("DELETE FROM {} WHERE id = {}".format(usrname, bottom))
    con.commit()

ここの直近のものを削除するクエリですが,多分前の記事に書いてあるやつは間違いでした.(idの最大値 = rowの数としていた)

# nameテーブルがあるかどうかを返す(tmpテーブルがあるかの確かめ用)
def isExistTable(name):
    cur.execute("SELECT relname FROM pg_stat_user_tables")
    l = cur.fetchall()
    if ((name,) in l):
        return True
    else:
        return False

pg_stat_user_tablesというテーブルがpostgreSQL側で最初から用意されており,これのrelnameというカラムに現在のデータベースのすべてのtablenameが格納されています.参考:PostgreSQLにてテーブルやカラムの各種情報を取得するSQL (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得) - いろいろ備忘録日記

そういえばpostgreSQLpythonのdatetime型に対応していて,こちらでわざわざ変換してやる必要がないんですね.これ結構便利でした.

できたもの

  • ファイル構成
├── Procfile
├── __pycache__
├── example
├── main.py
├── mydatabase.py
├── requirements.txt
└── test.py

exampleはlinebotのサンプルを入れてるだけです.詳しい中身はGitHub - baku1101/linebot_katekyo: 家庭教師やる用のlinebotから

  • できること
    • start,finishボタンでdatetime型を使って時間の計測ができる
    • それをherokuのデータベースに保管する
    • 月ごとの計測した日付・時間(何時から何時まで)がわかる
    • 月ごとの計測した時間の合計がわかる
    • 直近に追加したデータの削除ができる
  • イメージ画像

f:id:baku1101:20190419110646j:plain
ボタンを上から押してみた画像

最後に

一応最低限の機能をもたせたものができたので完成ですが,せっかくpythonでデータ持ってきてるからグラフ出力とかできたら楽しそう.
SQL初めて触ったけど便利だった(今までpickleでデータ保存しかしたことなかったから,データの保存・取り出しがめんどくさかった)
あとこれの友だち追加できるQRコードです.お試ししたかったらどうぞ
f:id:baku1101:20190419111439p:plain