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'