Python - Using a Database: Putting Python's DB-API To Use(SQLite)

Head First Python

開発環境

• macOS Sierra - Apple (OS)
• Emacs (Text Editor)
• Python 3.6 (プログラミング言語)

Head First Python (Paul Barry (著)、O'Reilly Media)のChapter 7.(Using a Database: Putting Python's DB-API To Use) の DATABASE MAGNETS(No. 5700) を取り組んでみる。

MySQL、MariaDBの代わりに SQLite を使用。

DATABASE MAGNETS(No. 5700)

コード(Emacs)

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# cursor.execute('''create table log (
# id int auto_increment primary key,
# ts timestamp default current_timestamp,
# phrase varchar(128) not null,
# letters varchar(32) not null,
# ip varchar(16) not null,
# browser_string varchar(256) not null,
# results varchar(64) not null )''')

from flask import Flask, render_template, request, escape

import vsearch

app = Flask(__name__)


def log_request(req: 'flask_request', res: str) -> None:
    import sqlite3
    dbconfig = {'host': '127.0.0.1',
                'user': 'vsearch',
                'password': 'vsearchpasswd',
                'database': 'vsearchlogDB'}
    conn = sqlite3.connect(dbconfig['database'] + '.sql')
    cursor = conn.cursor()
    _SQL = '''insert into log
             (phrase, letters, ip, browser_string, results)
             values
             (?, ?, ?, ?, ?)'''
    cursor.execute(_SQL, (req.form['phrase'],
                          req.form['letters'],
                          req.remote_addr,
                          req.user_agent.browser,
                          res))
    conn.commit()
    cursor.close()
    conn.close()


@app.route('/')
@app.route('/entry')
def entr_page() -> 'html':
    return render_template('entry.html',
                           the_title='Welcome to search4letters on the web!')


@app.route('/search4', methods=['POST'])
def do_search() -> 'html':
    phrase = request.form['phrase']
    letters = request.form['letters']
    title = 'Here are your result:'
    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('/viewlog')
def view_the_log() -> str:
    with open('vsearch.log') as f:
        contents = []
        for content in f:
            contents.append([])
            for x in content.split('|'):
                contents[-1].append(escape(x))

    return str(contents)

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

入出力結果(Terminal, IPython)

$ ipython
Python 3.6.1 (default, Mar 30 2017, 17:52:30) 
Type "copyright", "credits" or "license" for more information.

IPython 5.3.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: paste
    import sqlite3
    dbconfig = {'host': '127.0.0.1',
                'user': 'vsearch',
                'password': 'vsearchpasswd',
                'database': 'vsearchlogDB'}
    conn = sqlite3.connect(dbconfig['database'] + '.sql')
    cursor = conn.cursor()
## -- End pasted text --

In [2]: paste
cursor.execute('''create table log (
id int auto_increment primary key,
ts timestamp default current_timestamp,
phrase varchar(128) not null,
letters varchar(32) not null,
ip varchar(16) not null,
browser_string varchar(256) not null,
results varchar(64) not null )''')
## -- End pasted text --
Out[2]: <sqlite3.Cursor at 0x10574cb90>

In [3]: cursor.close()

In [4]: conn.close()

In [5]: quit()
$ ./vsearch4log.py
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)
 * Restarting with stat
 * Debugger is active!
 * Debugger PIN: 873-014-066
127.0.0.1 - - [30/Mar/2017 19:29:55] "GET / HTTP/1.1" 200 -
127.0.0.1 - - [30/Mar/2017 19:29:55] "GET /static/hf.css HTTP/1.1" 304 -
127.0.0.1 - - [30/Mar/2017 19:30:01] "POST /search4 HTTP/1.1" 200 -
127.0.0.1 - - [30/Mar/2017 19:30:24] "POST /search4 HTTP/1.1" 200 -
  C-c C-c$ ipython
Python 3.6.1 (default, Mar 30 2017, 17:52:30) 
Type "copyright", "credits" or "license" for more information.

IPython 5.3.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: paste
    import sqlite3
    dbconfig = {'host': '127.0.0.1',
                'user': 'vsearch',
                'password': 'vsearchpasswd',
                'database': 'vsearchlogDB'}
    conn = sqlite3.connect(dbconfig['database'] + '.sql')
    cursor = conn.cursor()
## -- End pasted text --

In [2]: cursor.fetchall()
Out[2]: []

In [3]: cursor.execute('select * from log')
Out[3]: <sqlite3.Cursor at 0x107eadb90>

In [4]: cursor.fetchall()
Out[4]: 
[(None,
  '2017-03-30 10:30:01',
  'Head First Python',
  'aeiou',
  '127.0.0.1',
  'safari',
  "{'o', 'e', 'a', 'i'}"),
 (None,
  '2017-03-30 10:30:24',
  'SQLite',
  'aeiou',
  '127.0.0.1',
  'safari',
  "{'e', 'i'}")]

In [5]: quit()
$