Python - データベースの利用 - PythonのDB-APIを使う(接続特性、サーバー、カーソル、SQL合う襟の実行、カーソルと接続を閉じる)

Head First Python 第2版
原著
楽天ブックス(Kobo) Yahoo!

開発環境

• macOS Mojave - Apple (OS)
• Emacs (Text Editor)
• Windows 10 Pro (OS)
• Visual Studio Code (Text Editor)
• Python 3.7 (プログラミング言語)
• Packages
  • Flask
  • psycopg2
• PostgreSQL (ORDBMS(object-relational database management system))

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 psycopg2 as sql

app = Flask(__name__)


def log_request(req: 'flask_request', res: str) -> None:
    dbconfig = {'host': '127.0.0.1',
                'database': 'vsearchlogdb'}
    conn = sql.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('/')
@app.route('/entry')
def entry_page() -> str:
    return render_template(
        'entry.html',
        the_title='Welcom to search4letters on the web!')


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


@app.route('/viewlog')
def view_the_log() -> str:
    with open('vsearch.log') as log:
        contents = [[escape(row) for row in line.split('|')]
                    for line in log]
    return str(contents)


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

入出力結果(Terminal, cmd(コマンドプロンプト), Jupyter(IPython))

$ python3 hello_flask.py
 * Serving Flask app "hello_flask" (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 - - [30/Nov/2018 12:19:23] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [30/Nov/2018 12:19:23] "GET /static/hf.css HTTP/1.1" 404 -
(1, datetime.datetime(2018, 10, 23, 18, 43, 6, 41800), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'a', 'i', 'o', 'e'}")
(2, datetime.datetime(2018, 10, 23, 19, 39, 17, 766149), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'o', 'e', 'a'}")
(3, datetime.datetime(2018, 10, 23, 19, 39, 49, 540211), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'chrome', "{'e', 'a', 'o', 'i'}")
(4, datetime.datetime(2018, 10, 23, 20, 1, 25, 960959), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'e', 'a', 'o'}")
(5, datetime.datetime(2018, 10, 27, 20, 6, 38, 230949), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'a', 'e', 'o', 'i'}")
(6, datetime.datetime(2018, 10, 28, 16, 25, 33, 685069), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'e', 'i', 'o', 'a'}")
(7, datetime.datetime(2018, 10, 28, 21, 19, 14, 740612), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'a', 'o', 'e'}")
(8, datetime.datetime(2018, 10, 28, 21, 20, 0, 17564), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'e', 'o', 'a', 'i'}")
(9, datetime.datetime(2018, 10, 28, 22, 49, 42, 736729), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'chrome', "{'i', 'a', 'o', 'e'}")
(10, datetime.datetime(2018, 10, 29, 7, 34, 43, 63718), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'e', 'i', 'a', 'o'}")
(11, datetime.datetime(2018, 10, 29, 7, 43, 58, 841554), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'a', 'e', 'i', 'o'}")
(12, datetime.datetime(2018, 11, 3, 22, 21, 12, 365625), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'chrome', "{'e', 'o', 'a', 'i'}")
(13, datetime.datetime(2018, 11, 3, 22, 27, 24, 55653), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'o', 'e', 'a'}")
(14, datetime.datetime(2018, 11, 3, 22, 27, 28, 838474), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'o', 'e', 'a'}")
(15, datetime.datetime(2018, 11, 3, 22, 27, 38, 437255), 'This is a test of the posting capability', 'aeiou', '127.0.0.1', 'safari', "{'i', 'o', 'e', 'a'}")
(16, datetime.datetime(2018, 11, 30, 12, 19, 0, 810079), 'Hello, world!', 'aeiou', '127.0.0.1', 'safari', "{'e', 'o'}")
(17, datetime.datetime(2018, 11, 30, 12, 19, 33, 281325), 'Hello, flask!', 'aeiou', '127.0.0.1', 'safari', "{'a', 'e', 'o'}")
127.0.0.1 - - [30/Nov/2018 12:19:33] "POST /search4 HTTP/1.1" 200 -
127.0.0.1 - - [30/Nov/2018 12:19:33] "GET /static/hf.css HTTP/1.1" 404 -
  C-c C-c$