Python - Flask - MySQL - データベースの利用 - Python の DB-API を使う(接続特性、サーバとの接続、カーソル、クエリの実行、close)

Head First Python 第2版
原著

開発環境

• macOS Mojave - Apple
• Emacs (Text Editor)
• Python 3.7 (プログラミング言語)

Head First Python 第2版 ―頭とからだで覚えるPythonの基本 (Paul Barry (著)、嶋田 健志 (監修)、木下 哲也 (翻訳)、オライリージャパン)の7章(データベースの利用 - Python の DB-API を使う)、データベースマグネット(p. 297)を取り組んでみる。

コード(Emacs)

Python 3

from flask import Flask, render_template, request, escape
import vsearch
import mysql.connector

app = Flask(__name__)


def log_request(req: 'flask_request', res: str) -> None:
    dbconfig = {'host': '127.0.0.1',
                'user': 'vsearch',
                'password': 'vsearchpasswd',
                'database': 'vsearchlogDB', }
    conn = mysql.connector.connect(**dbconfig)
    cursor = conn.cursor()
    _SQL = '''
insert into log
(phrase, letters, ip, browser_string, results)
values
(%s, %s, %s, %s, %s)
'''
    cursor.execute(
        _SQL,
        (req.form['phrase'], req.form['letters'], req.remote_addr,
         req.user_agent.browser, res))
    conn.commit()
    _SQL = '''select * from log'''
    cursor.execute(_SQL)
    for row in cursor.fetchall():
        print(row)
    cursor.close()
    conn.close()


@app.route('/search4', methods=['POST'])
def do_search() -> 'html':
    phrase = request.form['phrase']
    letters = request.form['letters']
    title = '検索結果'
    results = str(vsearch.search4letters(phrase, letters))
    log_request(request, results)
    return render_template('results.html',
                           the_title=title,
                           the_phrase=phrase,
                           the_letters=letters,
                           the_results=results)


@app.route('/')
@app.route('/entry')
def entry_page() -> 'html':
    return render_template(
        'entry.html', the_title='Web版のsearch4lettersにようこそ！')


@app.route('/viewlog')
def view_the_log() -> 'html':
    with open('vsearch.log') as log:
        contents = [[escape(col) for col in row.split('|')]
                    for row in log]
    titles = ('フォームデータ', 'リモートアドレス', 'ユーザーエージェント', '結果')
    return render_template('viewlog.html',
                           the_title='ログの閲覧',
                           the_row_titles=titles,
                           the_data=contents,)


if __name__ == '__main__':
    app.run(debug=True)

入出力結果(Terminal, Jupyter(IPython))

$ python3 vsearch4web.py
 * Serving Flask app "vsearch4web" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: on
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 178-619-558
127.0.0.1 - - [08/Oct/2018 18:54:56] "GET / HTTP/1.1" 200 -
(1, datetime.datetime(2018, 10, 8, 18, 39, 47), 'hitch-hiker', 'xyz', '127.0.0.1', 'Safari', 'set()')
(2, datetime.datetime(2018, 10, 8, 18, 40, 8), 'galaxy', 'xyz', '127.0.0.1', 'Chrome', 'set()')
(3, datetime.datetime(2018, 10, 8, 18, 40, 38), 'galaxy', 'xyz', '127.0.0.1', 'Chrome', "{'x', 'y'}")
(4, datetime.datetime(2018, 10, 8, 18, 51, 1), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'e', 'o', 'a', 'i'}")
(5, datetime.datetime(2018, 10, 8, 18, 54, 59), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'e', 'a', 'i', 'o'}")
127.0.0.1 - - [08/Oct/2018 18:54:59] "POST /search4 HTTP/1.1" 200 -
127.0.0.1 - - [08/Oct/2018 18:55:03] "GET /search4 HTTP/1.1" 405 -
127.0.0.1 - - [08/Oct/2018 18:55:03] "GET /viewlog.html HTTP/1.1" 404 -
127.0.0.1 - - [08/Oct/2018 18:55:04] "GET /viewlog HTTP/1.1" 200 -
127.0.0.1 - - [08/Oct/2018 18:55:05] "GET /viewlog HTTP/1.1" 200 -
  C-c C-c$