FlaskによるWebアプリ開発④～SQLAlchemyを使ってデータベースを操作する

Contents

flaskでのWebアプリ開発シリーズ記事一覧
Pythonとデータベースの連携
SQLAlchemyとは？
SQLAlchemy使い方
SQLAlchemyでテーブルを作成
.dbファイルの中身を見る方法

SQLAlchemyからテーブルにデータを追加（INSERT文）

flaskでのWebアプリ開発シリーズ記事一覧

Pythonとデータベースの連携

SQLAlchemy
sqlite

この2つがPythonでデータベースを操作する際に使う代表的なライブラリです。違いとしてはsqliteは用意された関数の中に引数として、そのままデータベースの構文を打ち込むのに対してSQLAlchemyはSQL構文をそのまま書かずに独特の書き方をする点です。

なのでSQLAlchemyだと、データベース側にどういうコードが送られているのかが、慣れないうちはブラックボックスになってしまいます。

個人的にはSQLAlchemyの方が便利といえば便利なので使っててしっくりくるので好きですが、この辺りは人によると思います。

SQLAlchemyとは？

pythonからデータベースを操作するためのORMモジュール。要はPythonからSQLでデータベースの操作ができるライブラリ。

<メリット>

・SQLインジェクション対策がサポートされる。
・クラスなので、SQLをオブジェクト指向で書ける。
・引数に変数を入れるため、文字列の結合などが必要ないので短く書ける。

<注意点>
・ちょっとしたSQLを実行する場合は、ORMを使わない方がコード量が短い。
・英語の文献が多く日本語記事があまりない。
・慣れないうちは内部でどんなクエリを発行しているのか分かりにくいブラックボックスになりがち。

同じくPythonでデータベース操作ができるsqliteとの違いはsqliteよりも少しセキュリティ面などが高い分内部処理が複雑なのと、直接SQL文を書かないので、内部で気づかないうちに超重いSQLクエリを叩かれてたりする点です。

個人的なイメージとしては以下のような感じです。

sqlite－初心者向け
Sqlalchemy－中級者以降向け

#sqlalchemyのインストール
pip install flask-sqlalchemy

SQLAlchemy使い方

db.を紐づけする
db.Modelを継承してモデルをつくる
db.Columnでcolumnを定義する

SQLAlchemyでテーブルを作成

ファイルを実際に作成して説明します。

from flask import Flask, request, render_template
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
SQLALCHEMY_TRACK_MODIFICATIONS = False
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False


# SQLAlchemyでデータベースに接続する ※test.dbがない場合は自動的に作成されます
db_uri = 'sqlite:///test.db'
app.config['SQLALCHEMY_DATABASE_URI'] = db_uri
db = SQLAlchemy(app)


# データベースの仕様をクラスで定義する
class order_table(db.Model):
    orderid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    pub_date = db.Column(db.DateTime, nullable=False,default=datetime.utcnow)
    goods_number = db.Column(db.Integer)
    payment = db.Column(db.Text())

    # ※order_idはプライマリキー(自動で値が挿入される)ので、要らない
    def __init__(self, pub_date, goods_number, payment):
        self.pub_date = pub_date
        self.goods_number = goods_number
        self.payment = payment

# テーブルを作成する
db.create_all()
print('ok')

ここではorder_tableというテーブルをクラスとして定義してSqlalchemyで実際にdbファイル内に作成します。今回作るテーブルの要素は以下の４つです。

order_id
pub_date
goods_number
payment

SQLAlchemyの構文は特殊でコントクラスタ(__init__)の前にデータテーブルの要素の設定（プライマリキーやデータ型）を指定します。

そのあとに初期化メソッド(__init__)でインスタンスを作成した場合にデータを格納するクラス変数を指定するという流れになっています。

sqliteだとそのままcreate文でテーブルを作るので、そのあたりが少し手間というか特殊です、よくわからんという人はコンソールでデータベースを開いてcreate文で作ってもいいです。

そして、このコードを実行すると、実行したディレクトリ上にtest.dbというファイルが作成されています。

.dbファイルの中身を見る方法

.dbファイルはtxtやhtmlとは違って、PCにデフォルトで搭載されているソフトでは基本的にみることはできません。

なので、扱いにくいファイル形式です。そのため.dbファイルに苦手意識を持つ人が多いです。ですが、データベースはエンジニアであれば避けては通れないものなので、使っていって慣れていくしかありません。

.dbファイルを見る方法は大きく2つあります。

コマンドラインからsqlite3コマンドを使って開く
「DB Browser for SQLite」などの可視化ソフトを使用する

現場によってはGUIがない可能性があるので、理想はコマンドラインから開けるようになることですが、コマンドラインからみると味気ないので、可視化ツールが使える環境であれば可視化ツールを使う方が見やすいです。今回はとりあえずコマンドから開いてみます。

cdコマンドでFlaskのプロジェクトのディレクトリに移動してsqlite3コマンドでdbファイルを開いてみます。sqlite>となれば成功です。

ファイルのパスは初心者が詰まりやすいポイントですが、Windowsなら「shift」を押しながら、右クリックすると、「パスをコピー」という選択肢が出てくるので、それでパスをコピーして直接アクセスするのもアリです。

# コマンドラインからdbファイルを開く
$sqlite3 test.db

ファイルが開けたら次はテーブルを確認します。

# dbファイルにあるテーブルの一覧を表示する
$.tables

ここにorder_tableとあれば成功です。

SQLAlchemyからテーブルにデータを追加（INSERT文）

次はSQLAlchemyからテーブルにデータを追加します、

<app.py>

from flask import Flask, request, render_template
from datetime import datetime
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
app = Flask(__name__)
SQLALCHEMY_TRACK_MODIFICATIONS = False
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False


# SQLAlchemyでデータベースに接続する
db_uri = 'sqlite:///test.db'
app.config['SQLALCHEMY_DATABASE_URI'] = db_uri
db = SQLAlchemy(app)

# テーブルの中身をクラスとして定義する
class order_table(db.Model):
    orderid = db.Column(db.Integer, primary_key=True, autoincrement=True)
    pub_date = db.Column(db.DateTime, nullable=False,default=datetime.utcnow)
    goods_number = db.Column(db.Integer)
    payment = db.Column(db.Text())

    def __init__(self, pub_date, goods_number, payment):
        self.pub_date = pub_date
        self.goods_number = goods_number
        self.payment = payment


# テーブルに追加するデータ
date = datetime.now()
goods_number = 810
payment = 'cash'

# テーブルにデータを追加する
add_data = order_table(date,goods_number,payment)
db.session.add(add_data)

# テーブルへの変更を保存する
db.session.commit()

実行したらデータが追加できているかコマンドラインからdbファイルを開いて確認します。

# dbファイルを開く
$sqlite3 test.db

# テーブルにデータが追加されているか確認する
$sqlite> .tables
※実行結果　order_table

$sqlite> select * from order_table;
※実行結果　1|2019-05-22 09:51:02.112116|810|cash

それっぽいデータベースにするために、あと2つほど適当にデータを追加します。

# テーブルに追加するデータ
date = datetime.now()
goods_number = 811
payment = 'credit'
date2 = datetime.now()
goods_number2 = 812
payment2 = 'credit'

# テーブルにデータを追加する
add_data = order_table(date,goods_number,payment)
add_data2 = order_table(date2,goods_number2,payment2)
db.session.add(add_data)
db.session.add(add_data2)

# テーブルへの変更を保存する
db.session.commit()

# データがINSERT出来ているか確認する
sqlite> select * from order_table;

※実行結果
1|2019-05-22 09:51:02.112116|810|cash
2|2019-05-22 10:24:00.764144|811|credit
3|2019-05-22 10:24:00.764144|812|credit

テーブルのデータを読み込む（SELECT文）

テーブルにデータが追加できたら次はSELECT文で、SQLAlchemyからテーブルに格納されているデータを読み込んでみます。

SQLAlchemyではテーブルに格納されているデータは.query.all()で呼び出すことができます。試しにさきほど作ったorder_tableの中身を呼び出してみます。

# テーブルのデータを読み込む 
order = order_table.query.all()

# 読み込んだテーブルのデータをPythonで表示する方法のあれこれ
print(order, type(order))
print(dir(order[0]))
print(order[0].pub_date, order[0].goods_number, order[0].payment)

# for文でテーブルの要素を取得してリストに格納する
goodsdata = []
    for i in order:
        goodsdata.append(i.goods_number)

# リストの中身を表示する
print(goodsdata)

処理をざっと解説すると、まずorder = order_table.query.all()の部分でorderという変数の中にデータテーブルに格納されているデータが渡しています。

orderの中身はリストになっており、リストの要素一つあたりに一列ごとにデータがオブジェクト化されて格納されています。↓

[<order_table 1>, <order_table 2>, <order_table 3>] <class ‘list’>

データテーブルの中身を見るためにはリストから1つずつ要素を取り出して.クラス変数名で呼び出してあげるといいです。そのオブジェクトが持つ変数・関数はdir()で確認できます。

[‘__class__’, ‘__delattr__’, ‘__dict__’, ‘__dir__’, ‘__doc__’, ‘__eq__’, ‘__format__’, ‘__ge__’, ‘__getattribute__’, ‘__gt__’, ‘__hash__’, ‘__init__’, ‘__init_subclass__’, ‘__le__’, ‘__lt__’, ‘__mapper__’, ‘__module__’, ‘__ne__’, ‘__new__’, ‘__reduce__’, ‘__reduce_ex__’, ‘__repr__’, ‘__setattr__’, ‘__sizeof__’, ‘__str__’, ‘__subclasshook__’, ‘__table__’, ‘__tablename__’, ‘__weakref__’, ‘_decl_class_registry’, ‘_sa_class_manager’, ‘_sa_instance_state’, ‘goods_number’, ‘metadata’, ‘orderid’, ‘payment’, ‘pub_date’, ‘query’, ‘query_class’]

その中でテーブルに格納している値を持っているのはクラス定義の部分で指定した「pub_date」と「goods_number」と「payment」なので、これをそれぞれorder[0]の後ろに着けて呼び出すとテーブル1列目の各要素が呼び出せます。

print(order[0].pub_date, order[0].goods_number, order[0].payment)

↓

2019-05-22 09:51:02.112116 810 cash

これがorder[1]order[2]にも同じように格納されているのでfor文で呼び出すことができます。

for i in order:
    goodsdata.append(i.goods_number)
print(goodsdata)

※ 実行結果
[810, 811, 812]

テーブルから特定の要素を抽出する(WHERE文)

次はSELECT文から条件に当てはまる要素だけを抽出するWHERE文をやってみます。SqlalchemyではWHERE文は以下のように記述します。テーブルの列がそのまま呼び出されるので.を付けて中身を呼び出します。

order = order_table.query.filter_by(goods_number=811).first() 
print(order) 
print(order.orderid, order.goods_number, order.pub_date)

※ 実行結果 <order_table 2> 2 811 2019-05-22 10:24:00.764144

テーブルの中身を更新する（UPDATE文）

SQLでいうところのUPDATE文にあたるテーブルのデータ更新は、呼び出したテーブルの要素の中身を変更して保存するだけです。

# テーブルからgoods_numberが811のものを抽出する
order = order_table.query.filter_by(goods_number=811).first()

# goods_numberを850に書き換える
order.goods_number = 850

# 変更内容を保存
db.session.commit()

# 変更結果を確認する
print(order.orderid, order.goods_number, order.pub_date)

<実行結果>

2 850 2019-05-22 10:24:00.764144

このように811が850に書き換えられています。

テーブルから特定のデータを削除する(DROP文)

SQLでテーブルの要素の削除にあたるDelete文はdb.session.dalete()で行えます。

# goods_numberが810のものをテーブルから削除する
order = order_table.query.filter_by(goods_number=810).first()
db.session.delete(order)

# 変更内容を保存する
db.session.commit()

<データベースにアクセスして確認>