More than 3 years have passed since last update.

MariaDB のテーブルを web で表示する方法

Last updated at 2019-11-19Posted at 2019-11-19

Web で MariaDB のテーブルを次のように表示する方法です。

<!DOCTYPE html>
<html lang="ja">
<head>
<meta http-equiv="Pragma" content="no-cache" />
<meta http-equiv="Cache-Control" content="no-cache" />
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8" />
<script src="/js/jquery-3.4.1.min.js"></script>
<script src="display.js"></script>
<link rel="stylesheet" href="display.css">
<title>city</title>
</head>
<body>
<h2>city</h2><p />
<div class="contents">contents</div>
<hr />
Nov/19/2019 AM 08:25<p />
</body>
</html>

display.js

// -----------------------------------------------------------------------
//      display.js
//
//                                      Nov/16/2019
//
// -----------------------------------------------------------------------
jQuery (function ()
{
        var data_text = ""

        const file_in = "./display.py"

        jQuery.getJSON (file_in,function (data_aa)
                {
                var str_out = ""
                str_out += "<table>"
                str_out += "<tr>"
                str_out += "<th>id</th>"
                str_out += "<th>name</th>"
                str_out += "<th>population</th>"
                str_out += "<th>date_mod</th>"
                str_out += "</tr>"
                for (var it in data_aa)
                        {
                        var unit = data_aa[it]
                        str_out += "<tr>"
                        str_out += "<td>" + unit.id + "</td>"
                        str_out += "<td>" + unit.name + "</td>"
                        str_out += "<td>" + unit.population + "</td>"
                        str_out += "<td>" + unit.date_mod + "</td>"
                        str_out += "</tr>"
                        }
                str_out += "</table>"

               jQuery(".contents").html (str_out)
                })
                .fail(function(jqXHR, textStatus, errorThrown)
                        {
                        var str_out = "<h2>Not Exist</h2>"
                        str_out += "<blockquote>"
                                str_out += file_in + "<br />"
                        str_out += "</blockquote>"
                        jQuery(".contents").html(str_out)
                        })
})

// -----------------------------------------------------------------------

display.css

/* -------------------------------------------------------------- */
/*

        display.css

                                                Nov/19/2019

*/
/* -------------------------------------------------------------- */
table.main,td,th {
table-layout:fixed;
border:1.5px #7e7e7e solid;
border-collapse: collapse;
height: 16px;
}

th {
        background: #c6c6c6;
}

table.tag {
border:0.5px green solid;
}

tr.cyan {
        background-color: #c7d7c7;
}

.red {color:#ff0000;}

/* -------------------------------------------------------------- */

display.py

#! /usr/bin/python3
# -*- coding: utf-8 -*-
#
#       display.py
#                                       Nov/19/2019
#
# ----------------------------------------------------------------
import sys
import json
from datetime import date, datetime
#
import mysql.connector
#
#
from dotenv import load_dotenv
# ----------------------------------------------------------------
#
from define_conn_cursor import define_conn_cursor
#
#
# ----------------------------------------------------------------
def json_serial(obj):
    # 日付型の場合には、文字列に変換します
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    # 上記以外はサポート対象外.
    raise TypeError ("Type %s not serializable" % type(obj))
# ----------------------------------------------------------------
def     sql_to_json_proc        (cursor):
    sql_str="select id, name,population,date_mod from cities"
    cursor.execute (sql_str)
    rows = cursor.fetchall ()
#
    str_json = json.dumps(rows, default=json_serial)
#
    return str_json
# ----------------------------------------------------------------
sys.stderr.write("*** 開始 ***\n")
#
dotenv_path = '.env'
#
conn,cursor = define_conn_cursor(dotenv_path)
#
#
str_json = sql_to_json_proc(cursor)
#
print ("Content-Type: application/json")
print ()
#
print(str_json)
#
cursor.close()
conn.close()
#
#
sys.stderr.write("*** 終了 ***\n")
#
# ----------------------------------------------------------------

define_conn_cursor.py

#
#       define_conn_cursor.py
#                                       Nov/17/2019
#
import sys
import os
#
import mysql.connector
#
from dotenv import load_dotenv
#
#
# ----------------------------------------------------------------
def define_conn_cursor(dotenv_path):
    load_dotenv(dotenv_path)
    HOST=os.environ.get("host")
    USER=os.environ.get("user")
    PASSWORD=os.environ.get("password")
    DATA_BASE=os.environ.get("data_base")
#
    conn = mysql.connector.connect(user=USER,password=PASSWORD,host=HOST,database=DATA_BASE)
#
    cursor = conn.cursor(dictionary=True)
#
    return conn,cursor
# ----------------------------------------------------------------

.env

host='localhost'
user='john'
password='secret'
data_base='city'

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up