Raspberry PIでPython WEB#3（DB編）

#概要
今回はMySQLDBを利用して簡単なCRUDコードを作成したいと思います。
MySQLライブラリはSQLAlchemyというものを使用します。
※MySQLサーバーのバージョンが「5.5.54」ですが、下記の事前作業には最新MySQLをインストールするコマンドをいれました。

#事前作業
①次のコマンドでSQLAlchemyをインストールします。

# Python環境駆逐
sudo apt-get install python2.7-dev python3-dev

# MySQLサーバインストール
sudo apt-get install mariadb-server-10.0
sudo apt-get install python-mysqldb

# DBライブラリ
pip3 install sqlalchemy

# 次のエラーが発生し、インストールします。
# ModuleNotFoundError: No module named 'MySQLdb'
pip3 install mysqlclient

②データベース及びテーブル作成
※テーブル作成についてはboard.pyからも作成ができます。

# データベース作成
CREATE DATABASE testDB;

# テーブル作成SQL
CREATE TABLE `boards` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `body` varchar(200) DEFAULT NULL,
  `writer` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

サンプルソース作成

①以下3つのファイルを作成します。

# file name : setting.py
# pwd : /home/pi/work/setting.py
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

# mysqlのDBの設定
DATABASE = 'mysql://%s:%s@%s/%s?charset=utf8' % (
    "flaskweb", # username
    "flaskweb123", # password
    "192.168.1.25", # host
    "testDB", # database name
)
ENGINE = create_engine(
    DATABASE,
    encoding = "utf-8",
    echo=True # Trueだと実行のたびにSQLが出力される
)

# Sessionの作成
session = scoped_session(
# ORM実行時の設定。自動コミットするか、自動反映するなど。
    sessionmaker(
        autocommit = False,
        autoflush = False,
        bind = ENGINE
    )
)

Base = declarative_base()
Base.query = session.query_property()

# file name : board.py
# pwd : /home/pi/work/board.py
import sys
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float, DateTime
from setting import Base
from setting import ENGINE

class Board(Base):
    __tablename__ = 'boards'
    id = Column('id', Integer, primary_key = True)
    title = Column('title', String(100))
    body = Column('body', String(200))
    writer = Column('writer', String(50))
    email = Column('email', String(100))
    password = Column('password', String(50), nullable=False ) # NOT NULLの指定方法

def main(args):
    Base.metadata.create_all(bind=ENGINE)

if __name__ == "__main__":
    main(sys.argv)

# file name : main.py
# pwd : /home/pi/work/main.py
from setting import session
from board import *

board = Board()
board.title = 'title1'
board.body = 'test body1'
board.writer = 'tester'
board.email = 'test@localhost'
board.password = '1111'

session.add(board)
session.commit()

# query関連の条件指定は参考サイトを参照してください。
boards = session.query(Board).all()
for board in boards:
    print(board.title)
~

#テーブル作成
次のコマンドでテーブル作成ができます。
※テーブル作成後に該当DDLも表示ができました。

pi@raspberrypi:~/work $ python3 board.py
2021-06-15 09:04:06,377 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'sql_mode'
2021-06-15 09:04:06,378 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-06-15 09:04:06,380 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2021-06-15 09:04:06,380 INFO sqlalchemy.engine.Engine [generated in 0.00040s] ()
2021-06-15 09:04:06,383 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2021-06-15 09:04:06,384 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-06-15 09:04:06,386 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-06-15 09:04:06,388 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = %s AND table_name = %s
2021-06-15 09:04:06,388 INFO sqlalchemy.engine.Engine [generated in 0.00043s] ('testDB', 'boards')
2021-06-15 09:04:06,392 INFO sqlalchemy.engine.Engine
CREATE TABLE boards (
        id INTEGER NOT NULL AUTO_INCREMENT,
        title VARCHAR(100),
        body VARCHAR(200),
        writer VARCHAR(50),
        email VARCHAR(100),
        password VARCHAR(50) NOT NULL,
        PRIMARY KEY (id)
)


2021-06-15 09:04:06,392 INFO sqlalchemy.engine.Engine [no key 0.00038s] ()
2021-06-15 09:04:06,406 INFO sqlalchemy.engine.Engine COMMIT

#データ登録
次のコマンドで１つのレコードが登録できます。
※レコードの登録と最後に登録されたレコードのタイトルのみ表示しています。

pi@raspberrypi:~/work $ python3 main.py
2021-06-15 09:07:08,710 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'sql_mode'
2021-06-15 09:07:08,710 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-06-15 09:07:08,713 INFO sqlalchemy.engine.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2021-06-15 09:07:08,713 INFO sqlalchemy.engine.Engine [generated in 0.00038s] ()
2021-06-15 09:07:08,716 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2021-06-15 09:07:08,717 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-06-15 09:07:08,719 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-06-15 09:07:08,723 INFO sqlalchemy.engine.Engine INSERT INTO boards (title, body, writer, email, password) VALUES (%s, %s, %s, %s, %s)
2021-06-15 09:07:08,723 INFO sqlalchemy.engine.Engine [generated in 0.00045s] ('title1', 'test body1', 'tester', 'test@localhost', '1111')
2021-06-15 09:07:08,725 INFO sqlalchemy.engine.Engine COMMIT
2021-06-15 09:07:08,733 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-06-15 09:07:08,740 INFO sqlalchemy.engine.Engine SELECT boards.id AS boards_id, boards.title AS boards_title, boards.body AS boards_body, boards.writer AS boards_writer, boards.email AS boards_email, boards.password AS boards_password
FROM boards
2021-06-15 09:07:08,740 INFO sqlalchemy.engine.Engine [generated in 0.00045s] ()
title1

#DBを確認
次のコマンドで登録されたレコードを確認します。

pi@raspberrypi:~/work $ mysql -uflaskweb -pflaskweb123 testDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.54-0+deb8u1 (Raspbian)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from boards;
+----+--------+------------+--------+----------------+----------+
| id | title  | body       | writer | email          | password |
+----+--------+------------+--------+----------------+----------+
|  1 | title1 | test body1 | tester | test@localhost | 1111     |
+----+--------+------------+--------+----------------+----------+
1 row in set (0.00 sec)

mysql>

#参考
①【PythonのORM】SQLAlchemyで基本的なSQLクエリまとめ
https://qiita.com/tomo0/items/a762b1bc0f192a55eae8
・DB接続及びテーブルのモデル作成、データの登録及び条件指定方法など参照

②SQLAlchemy で Update するには？
https://qiita.com/nskydiving/items/745f985a42da89a16934
・全データ削除、一括登録、更新内容を参照

#終わりに
午前中まではPyMySQLを利用して調べていましたが、
SQLAlchemyの方が以下のメリットがありましたので、このライブラリに決めました。次回はWEBに連動してCRUDの掲示板を作成してみたいと思います。

■メリット
・SQLに該当するロジックを書くこと。
・MYSQL、SQL、PostgreSQLの特殊なSQL文を気にしなくてもよいこと。
・DBMSが変更されてもSQL内容の変更は不要。（接続文字列関連のロジックは修正が必要）

■デメリット
・SQLをコードで書いてあるため、デバッグするのがちょっと不便かもです。