【Python】SQLiteからデータを取得して表示する（その２）

2018年3月8日2020年10月11日Python,開発

おはようございます。

先日に引き続き、Python で SQLiteからデータを取得、データをテーブル形式で表示したいと思います。

今までクライアントアプリで作ってきたものを再現する感じになります。

本来 Tornado はこういった使い方をしなさそうですが、まあとりあえず勉強として。

そのうち非同期やノンブロッキングな仕組みを利用したサンプルもやっていきたいと思います。

プログラムは前回のものを流用します。

【Python】SQLiteからデータを取得して表示（その１）

1. 画面の修正
2. プログラムの修正
- 2.1. javascript の追加
- 2.2. 検索処理の追加
3. 起動してみる
4. まとめ

画面の修正

index.html

<!DOCTYPE html>
<html>
  <head>
    <title>{{ title }}</title>
    <link rel="stylesheet" href="{{ static_url('css/style.css') }}"/>
    <script type="text/javascript" src="{{ static_url('js/script.js') }}"></script>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
  </head>
  <body>
    <div id="container">
      <div style="float:left">
        <span>
          名前：
        </span>
        <input type="text" id="searchName" value="">
        <span>
          種別：
        </span>
        <select id="searchKind">
            <option value="">指定なし</option>
          {% for item in items %}
            <option value="{{item[0]}}">{{ item[1] }}</option>
          {% end %}
        </select>
      </div>
      <div style="float:right;">
        <input type="button" value="検索">
      </div>
      <div style="clear:both; padding-top:10px;">
        <table id="catTable">
          <tr id="header">
            <th style="width:10%">No</th>
            <th style="width:20%">名前</th>
            <th style="width:10%">性別</th>
            <th style="width:10%">年齢</th>
            <th style="width:20%">種別</th>
            <th style="width:30%">好物</th>
          </tr>
        </table>
      </div>
    </div>
  </body>
</html>

style.css

body {
  font-family:"ＭＳ Ｐゴシック","MS PGothic",sans-serif;
  width: 100%;
  margin: 0 auto;
}
div {
  margin:20px;
}
div span {
  margin:15px;
}
div#container{
    width: 800px;
}
table {
    width:100%;
   border: 1px solid #ccc;
   border-collapse:collapse;
}
div.editField {
   position: absolute;
   width:0px;
   height:0px;
}
th {
   text-align:center;
   background-color:#404040;
   color:#ffffff;
   width: 100px;
   height: 20px;
   border: 1px solid #ccc;
}
td {
   padding-left:5px;
   width: 200px;
   height: 20px;
   border: 1px solid #ccc;
}
input[type="button"] {
    width: 100px;
}

プログラムの修正

javascript の追加

static/js/script.js

function searchButtonClick () {

   var param = {
      name : $("#searchName").val()
      , kind_cd : $("#searchKind").val()
   }

   $.ajax({
      url: 'http://localhost:8888/search',
      type: 'POST',
      data: JSON.stringify(param),

      success: function(jsonResponse) {
         // 不要な\マークを除去
         // なぜなのか
         jsonResponse = jsonResponse.replace( /\\/g , "" );
         var data = JSON.parse(jsonResponse);

         // 最初にデータ行を削除
         var table = $("#catTable");
         var rows = table.find("tr");
         for (index in rows) {
            if (rows[index].id == ""){
               rows[index].remove();
            }
         }

         // テーブルに追加
         $.each(data, function(i, row){
            var tr = document.createElement("tr");
            $.each(row, function(i, cell){
               var td = document.createElement("td");
               td.innerHTML = cell;
               tr.appendChild(td);
            });

            table.append(tr);
         });

      },
      error: function() {
      }
   });
}

検索処理の追加

SQLiteUtilにメソッドを追加、
更にURLと対応するハンドラ―を追加して検索処理を実施できるようにする。

server.py

# --- coding: utf-8 ---
u"""簡易WEBシステムのサンプルプログラム"""

import os
import signal
import logging
import sqlite3
import json
import tornado.websocket
import tornado.web
import tornado.ioloop
from tornado.options import options
from contextlib import closing

is_closing = False


def signal_handler(signum, stack):
    u""" サーバー停止信号を受信した際にフラグをオンにします."""

    global is_closing
    logging.info("exiting...")
    is_closing = True


def try_exit():
    u""" サーバー停止フラグが場合にインスタンスを停止します. """

    global is_closing
    if is_closing:
        # clean up here
        tornado.ioloop.IOLoop.instance().stop()
        logging.info("exit success")


class SQLiteUtil:
    u""" SQLite 操作用クラス """

    @staticmethod
    def create_db():
        u""" データベース、及び必要なテーブルを作成します. """

        logging.info("create database")
        with closing(sqlite3.connect("sample.db")) as conn:

            c = conn.cursor()

            # 種別テーブル
            sql = "CREATE TABLE IF NOT EXISTS MSTKIND ("
            sql += "  KIND_CD NCHAR NOT NULL"
            sql += "  , KIND_NAME NVARCHAR"
            sql += "  , primary key (KIND_CD)"
            sql += ")"
            c.execute(sql)

            # 猫テーブル
            sql = "CREATE TABLE IF NOT EXISTS TBLCAT ("
            sql += "  NO INT NOT NULL"
            sql += "  , NAME NVARCHAR NOT NULL"
            sql += "  , SEX NVARCHAR NOT NULL"
            sql += "  , AGE INT DEFAULT 0 NOT NULL"
            sql += "  , KIND_CD NCHAR DEFAULT 0 NOT NULL"
            sql += "  , FAVORITE NVARCHAR"
            sql += "  , primary key (NO)"
            sql += ")"

            c.execute(sql)

            c.close()

    @staticmethod
    def insert_initial_data():
        u""" 各テーブルに初期データを登録します """

        logging.info("insert initial data")
        with closing(sqlite3.connect("sample.db")) as conn:

            c = conn.cursor()

            # 種別マスタ
            sql = "INSERT INTO MSTKIND (KIND_CD, KIND_NAME) VALUES (?,?)"
            kinds = [
                ('01', 'キジトラ'),
                ('02', '長毛種（不明）'),
                ('03', 'ミケ（っぽい）'),
                ('04', 'サビ'),
                ('09', 'その他'),
            ]
            c.executemany(sql, kinds)

            # 猫データ
            sql = "INSERT INTO TBLCAT VALUES (?,?,?,?,?,?)"
            cats = [
                ("1","そら","♂","6","01","犬の人形"),
            ("2","りく","♂","5","02","人間"),
            ("3","うみ","♀","4","03","高級ウェットフード"),
            ("4","こうめ","♀","2","04","横取りフード"),
        ]
        c.executemany(sql, cats)

        c.close()
        conn.commit()

    @staticmethod
    def select_kind_all():
        u""" MST種別のデータを全て取得します. """

        logging.info("select all kind")
        with closing(sqlite3.connect("sample.db")) as conn:

            c = conn.cursor()

            sql = "SELECT * FROM MSTKIND ORDER BY KIND_CD"

            c.execute(sql)
            return c.fetchall()

    @staticmethod
    def select_cat(name, kind_cd):

        logging.info("select cat")
        with closing(sqlite3.connect("sample.db")) as conn:

            conn.row_factory = sqlite3.Row
            c = conn.cursor()

            # SQL組み立て
            sql = "SELECT C.NO, C.NAME, C.SEX, C.AGE, C.KIND_CD, K.KIND_NAME, C.FAVORITE FROM TBLCAT C"
            sql += " LEFT OUTER JOIN MSTKIND K ON ( C.KIND_CD = K.KIND_CD)"
            if kind_cd == "":
                sql += " WHERE C.NAME LIKE '" + name + "%'"
            else:
                sql += " WHERE C.NAME LIKE '" + name + "%' AND C.KIND_CD = '" + kind_cd + "'"

            sql += " ORDER BY C.KIND_CD"

            result = []
            row = {}
            for r in c.execute(sql):
                result.append({
                    "no":r['no'],
                    "name":r['name'],
                    "sex":r['sex'],
                    "age": r['age'],
                    "kind_name": r['kind_name'],
                    "favorite": r['favorite'],
                })

            return result


class SearchCatHandler(tornado.websocket.WebSocketHandler):
    u""" 検索処理 """

    def get(self):

        logging.info("SearchCatHandler[GET]")

    def post(self):

        logging.info("SearchCatHandler[POST]")
        param = json.loads(self.request.body)

        su = SQLiteUtil()
        result = su.select_cat(param['name'], param['kind_cd'])

        self.write(json.dumps(result, ensure_ascii=False))


class MainHandler(tornado.web.RequestHandler):
    u""" メイン処理 """

    def get(self):

        # テーブルの作成
        su = SQLiteUtil()
        if not os.path.isfile("sample.db"):
            su.create_db()
            su.insert_initial_data()

        items = su.select_kind_all()
        self.render("index.html", title="Pythonサンプル", word="Python!!", items=items)


application = tornado.web.Application([
    (r"/", MainHandler),
    (r"/search", SearchCatHandler)
    ],
    template_path=os.path.join(os.getcwd(),  "templates"),
    static_path=os.path.join(os.getcwd(),  "static"),
    js_path=os.path.join(os.getcwd(), "js"),
)

if __name__ == "__main__":
    tornado.options.parse_command_line()
    signal.signal(signal.SIGINT, signal_handler)
    application.listen(8888)
    logging.info("server started")
    tornado.ioloop.PeriodicCallback(try_exit, 100).start()
    tornado.ioloop.IOLoop.instance().start()

起動してみる

初期画面

検索ボタンをクリックします。

検索結果

無事にデータが表示されました。

まとめ

最初は WebSocket を使って実装しようとしたのですが、まだちょっと理解する時間が足りなかったので今まで経験したことのある JQuery の非同期通信（Ajax）で処理しました。

想定外に嵌ったのが、Json形式でサーバーから受け取ったデータがうまくパースされてくれなかったところです。

何故かサーバーから受け取った Json形式の文字列がダブルコーテーションで囲まれているのが原因で想定通りの動きをしていなかった模様。

何故なのか。

あまり深追いしてもしょうがないのでとりあえずクライアント側で処理しました。

次回は、データの追加、更新、削除ですかね。

ではでは。

2018年3月8日2020年10月11日Python,開発JQuery,Python,SQLite,Tornado,プログラミング

Posted by doradora

【Python】SQLite でデータ追加、更新、削除をする

【Python】SQLiteからデータを取得して表示する（その１）

コメント一覧

アニメ好きな人より:
2020年1月7日 11:22 AM
こんにちは。初めまして。
私はこの記事を読ませていただき、素晴らしいものだと思いました。
ですが、私が今この作業を行いましたが、
Webにテーブルは表示されるものの、検索ボタンを押してもデータは表示されませんでした。
どうしてでしょうか？？教えてください。
返信
- doradoraより:
  2020年1月9日 9:46 AM
  アニメ好きな人様
  いつもブログを見ていただきありがとうございます。
  ご質問の件、
  データが表示されない原因は分かりませんが、次のことを確認していただけますか。
  ・SQLiteにテーブル、データが登録されているかどうか
  ・Pythonでエラーが発生しているかどうか
  ・Javascriptでエラーが発生しているかどうか
  　→最近のブラウザであれば、F12キーを押すと開発ツールが表示されます
  以上です。
  よろしくお願いします。
  返信

コメントする