【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="検索" onclick="searchButtonClick()">
      </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>

<!DOCTYPE html>

<html>

<head>

<title>{{ title }}</title>

</head>

<body>

<span>

名前：

</span>

<span>

種別：

</span>

{% for item in items %}

{% end %}

</select>

</div>

</div>

</tr>

</table>

</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;
}

body {

font-family:"ＭＳＰゴシック","MSPGothic",sans-serif;

width:100%;

margin:0auto;

}

div {

margin:20px;

}

div span {

margin:15px;

}

div#container{

width:800px;

}

table {

width:100%;

border:1pxsolid#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:1pxsolid#ccc;

}

td {

padding-left:5px;

width:200px;

height:20px;

border:1pxsolid#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() {
      }
   });
}

functionsearchButtonClick(){

varparam={

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,"");

vardata=JSON.parse(jsonResponse);

// 最初にデータ行を削除

vartable=$("#catTable");

varrows=table.find("tr");

for(index inrows){

if(rows[index].id==""){

rows[index].remove();

}

// テーブルに追加

$.each(data,function(i,row){

vartr=document.createElement("tr");

$.each(row,function(i,cell){

vartd=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()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

# --- coding: utf-8 ---

u"""簡易WEBシステムのサンプルプログラム"""

importos

importsignal

importlogging

importsqlite3

importjson

importtornado.websocket

importtornado.web

importtornado.ioloop

fromtornado.options importoptions

fromcontextlibimportclosing

is_closing=False

defsignal_handler(signum,stack):

u""" サーバー停止信号を受信した際にフラグをオンにします."""

globalis_closing

logging.info("exiting...")

is_closing=True

deftry_exit():

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

globalis_closing

ifis_closing:

# clean up here

tornado.ioloop.IOLoop.instance().stop()

logging.info("exit success")

classSQLiteUtil:

u""" SQLite 操作用クラス """

@staticmethod

defcreate_db():

u""" データベース、及び必要なテーブルを作成します. """

logging.info("create database")

withclosing(sqlite3.connect("sample.db"))asconn:

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

definsert_initial_data():

u""" 各テーブルに初期データを登録します """

logging.info("insert initial data")

withclosing(sqlite3.connect("sample.db"))asconn:

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

defselect_kind_all():

u""" MST種別のデータを全て取得します. """

logging.info("select all kind")

withclosing(sqlite3.connect("sample.db"))asconn:

c=conn.cursor()

sql="SELECT * FROM MSTKIND ORDER BY KIND_CD"

c.execute(sql)

returnc.fetchall()

@staticmethod

defselect_cat(name,kind_cd):

logging.info("select cat")

withclosing(sqlite3.connect("sample.db"))asconn:

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)"

ifkind_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={}

forrinc.execute(sql):

result.append({

"no":r['no'],

"name":r['name'],

"sex":r['sex'],

"age":r['age'],

"kind_name":r['kind_name'],

"favorite":r['favorite'],

})

returnresult

classSearchCatHandler(tornado.websocket.WebSocketHandler):

u""" 検索処理 """

defget(self):

logging.info("SearchCatHandler[GET]")

defpost(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))

classMainHandler(tornado.web.RequestHandler):

u""" メイン処理 """

defget(self):

# テーブルの作成

su=SQLiteUtil()

ifnotos.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キーを押すと開発ツールが表示されます
  以上です。
  よろしくお願いします。
  返信