被施工的Tom的記錄

記錄Flask多資料庫的用法

發表於 2019-08-15 更新於 2024-03-03 分類於記錄， Flask

前言

目前有需求要存取多個獨立的database，但是裏面的資料表是完全一樣的，資料是不一樣的例如有三家公司，可以來存取它的資料庫。

先來建立單一網頁CRUD

先建立資料夾TestFlaskMultDB

一樣在虛擬環境，先安裝flask-sqlalchemy

1 2	pip install flask pip install flask-sqlalchemy

建立靜態網頁

先來建立bookmanager.py我們的檔案結構像是這樣

1 2	TestFlaskMultDB\ bookmanager.py

在bookmanager.py中加入

from flask import Flask

app = Flask(__name__)


@app.route("/")
def home():
    return "My Flask app"


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

這時在vscode按下debug時，可以在http://127.0.0.1:3000/ 的網頁中看到My Flask app

可以處理使用者的輸入

如果是上面的網頁的話，使用者不能作任何的互動，所以Flask提供了一個叫templates的資料夾，將可以顯示在網頁上的html檔放在此，flask 有使用Jinja template engine 來處理template網頁的問題

建立一個輸入的form

我們在下面在建立一個資料夾叫templates在下面在建立home.html,所以專案結構看起來會是像

TestFlaskMultDB\
    bookmanager.py
    templates/
        home.html

在home.html中加入

<html>
<body>
    <form method="POST" action="/">
        <input type="text" name="title">
        <input type="submit" value="增加">
    </form>
</body>
</html>

在bookmanager.py中要加入模組才可使用和修改home()回傳html

from flask import Flask, render_template


app = Flask(__name__)


@app.route("/")
def home():
    # return "My Flask app"
    return render_template("home.html")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

在度瀏覽http://127.0.0.1:3000/會發現網頁如下

處理拿到使用者輸入的資料

要加入下面的模組

1	from flask import request

又網頁有GET和POST的請求，所以要加入到home

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        print(request.form)
    return render_template("home.html")

在網頁上，輸入資料可以在終端機上看到資訊

'-m' 'flask' 'run' '--port=3000' '--no-debugger' '--no-reload'
 * Serving Flask app "bookmanager.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:3000/ (Press CTRL+C to quit)
ImmutableMultiDict([('title', 'Peter Pen')])
127.0.0.1 - - [15/Aug/2019 12:26:44] "POST / HTTP/1.1" 200 -

有看到ImmutableMultiDict([('title', 'Peter Pen')])可以看到資料是你輸入的資料，有title是因為在home.html裏有<input type="text" name="title">

加入資料到資料庫

先在HeidiSQL的工具加入資料庫bookdatabase,接下來設定資料庫

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/bookdatabase'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        print(request.form)
    return render_template("home.html")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)
# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/bookdatabase'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)

加入ORM模型

加入class和建立資料到資料庫

class Book(db.Model):
    title = db.Column(db.String(80), unique=True,
                      nullable=False, primary_key=True)

    def __repr__(self):
        return "<Title:{}".format(self.title)


@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        book = Book(title=request.form.get("title"))
        db.session.add(book)
        db.session.commit()
    return render_template("home.html")

因為資料庫沒有此物件的資料表，因此先來建立資料表，在python的終端模式下下列的指令

1
2
3

>>> from bookmanager import db
>>> db.create_all()
>>> exit()

讀取資料庫的資料

1 2	books = Book.query.all() return render_template("home.html", books=books)

為了顯示book的資訊，所以要修改home.html加入下列的碼

{% for book in books %}
  <p>{{book.title}}</p>
  <form method="POST" action="./update">
    <input type="hidden" value="{{book.title}}" name="oldtitle">
    <input type="text" value="{{book.title}}" name="newtitle">
    <input type="submit" value="Update">
  </form>
{% endfor %}

因為有跳轉的問題，所以加入下列的模組

1	from flask import redirect

最後的bookmanager.py

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/bookdatabase'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


class Book(db.Model):
    title = db.Column(db.String(80), unique=True,
                      nullable=False, primary_key=True)

    def __repr__(self):
        return "<Title:{}".format(self.title)


@app.route("/", methods=["GET", "POST"])
def home():
    books = None
    if request.form:
        try:
            book = Book(title=request.form.get("title"))
            db.session.add(book)
            db.session.commit()
        except Exception as e:
            print("Failed to add book")
            print(e)
    books = Book.query.all()
    return render_template("home.html", books=books)


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

和home.html

<html>
<body>
    <h1>加入一本書</h1>
    <form method="POST" action="/">
        <input type="text" name="title">
        <input type="submit" value="增加">
    </form>
    <h1>書本列表</h1>
    {% for book in books %}
    <p>{{book.title}}</p>
    <form method="POST" action="./update">
        <input type="hidden" value="{{book.title}}" name="oldtitle">
        <input type="text" value="{{book.title}}" name="newtitle">
        <input type="submit" value="Update">
    </form>
    {% endfor %}
</body>
</html>

Update更新資料

加入更新的資料程式碼

@app.route("/update", methods=["POST"])
def update():
    try:
        newtitle = request.form.get("newtitle")
        oldtitle = request.form.get("oldtitle")
        book = Book.query.filter_by(title=oldtitle).first()
        book.title = newtitle
        db.session.commit()
    except Exception as e:
        print("Failed to update book")
        print(e)
    return redirect("/")

Delete刪除資料

在home.html 加入刪除的按鈕

<form method="POST" action="./delete">
        <input type="hidden" value="{{book.title}}" name="title">
        <input type="submit" value="刪除">
</form>

加入刪除的程式碼

@app.route("/delete", methods=["POST"])
def delete():
    try:
        title = request.form.get("title")
        book = Book.query.filter_by(title=title).first()
        db.session.delete(book)
        db.session.commit()
    except Exception as e:
        print("Failed to delete book")
        print(e)
    return redirect("/")

最後的碼和html

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/bookdatabase'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True

db = SQLAlchemy(app)


class Book(db.Model):
    title = db.Column(db.String(80), unique=True,
                      nullable=False, primary_key=True)

    def __repr__(self):
        return "<Title:{}".format(self.title)


@app.route("/", methods=["GET", "POST"])
def home():
    books = None
    if request.form:
        try:
            book = Book(title=request.form.get("title"))
            db.session.add(book)
            db.session.commit()
        except Exception as e:
            print("Failed to add book")
            print(e)
    books = Book.query.all()
    return render_template("home.html", books=books)


@app.route("/update", methods=["POST"])
def update():
    try:
        newtitle = request.form.get("newtitle")
        oldtitle = request.form.get("oldtitle")
        book = Book.query.filter_by(title=oldtitle).first()
        book.title = newtitle
        db.session.commit()
    except Exception as e:
        print("Failed to update book")
        print(e)
    return redirect("/")


@app.route("/delete", methods=["POST"])
def delete():
    try:
        title = request.form.get("title")
        book = Book.query.filter_by(title=title).first()
        db.session.delete(book)
        db.session.commit()
    except Exception as e:
        print("Failed to delete book")
        print(e)
    return redirect("/")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

html檔

<html>

<body>
    <h1>加入一本書</h1>
    <form method="POST" action="/">
        <input type="text" name="title">
        <input type="submit" value="增加">
    </form>

    <h1>書本列表</h1>
    {% for book in books %}
    <p>{{book.title}}</p>
    <form method="POST" action="./update">
        <input type="hidden" value="{{book.title}}" name="oldtitle">
        <input type="text" value="{{book.title}}" name="newtitle">
        <input type="submit" value="Update">
    </form>
    <form method="POST" action="./delete">
        <input type="hidden" value="{{book.title}}" name="title">
        <input type="submit" value="刪除">
    </form>
    {% endfor %}
</body>

</html>

建立使用多資料庫的方式來建立(有兩種)

在ORM的模型上有兩種方法declarative_base和db.Model專案是使用db.Model

declarative_base方法(不使用)

以HeidiSQL在mariadb來建立多個資料庫分別是dbhisharp、dbasa、dbups，建立好之後來設定先建立run.py

所以專案結構看起來會是像

1 2	TestFlaskMultDB\ run.py

在run.py中加入相關的設定

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
SQLALCHEMY_BINDS = {
    'users':        'mysql://root:12345678@localhost:33306/dbasa',
    'appmeta':      'mysql://root:12345678@localhost:33307/dbups'
}
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True


@app.route("/", methods=["GET", "POST"])
def home():
    return "OK"


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

建立資料表

因要連線不同的資料庫，所以要有一個機制來管理依不同的名稱來提供不同的連線，所以現在的專案結構是

TestFlaskMultDB\
	database\
	    __init__.py
	    dbprovide.py
	    
    run.py

在dbprovide.py的內容如下

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 依名稱來提供不同的資料庫的連線目前有預設，asa，ups


class DbProvide():
    db = None
    connection = None
    session = None

    def __init__(self, center=None):
        if center == None:
            self.db = create_engine(
                'mysql://root:12345678@localhost:3306/dbhisharp', echo=True, pool_threadlocal=True)
            self.connection = self.db.connect()
        if center == "asa":
            self.db = create_engine(
                'mysql://root:12345678@localhost:33306/dbasa', echo=True, pool_threadlocal=True)
            self.connection = self.db.connect()
        if center == "ups":
            self.db = create_engine(
                'mysql://root:12345678@localhost:33307/dbups', echo=True, pool_threadlocal=True)
            self.connection = self.db.connect()
        Session = sessionmaker(bind=self.db)
        self.session = Session()

也要提供ORM的模型,所以現在的專案結構是

TestFlaskMultDB\
	database\
	    __init__.py
	    dbprovide.py
	    models.py
    run.py

在models.py中

import hashlib
from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref
from dbprovide import DbProvide

Base = declarative_base()


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    username = Column(String(80))
    password = Column(String(80))

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)


if __name__ == '__main__':
    # ''' 此時只有建立 SQLAlchemy Engine 實例，還沒在記憶體內建立資料，
    #     只有第一個 SQL 指令被下達時，才會真正連接到資料庫內執行 '''
    # engine = create_engine('sqlite:///:memory:', echo=True)
    # ''' 真正建立表格是使用 Base.metadata.create_all(engine) '''
    # Base.metadata.create_all(engine)
    # auser = User('user1', 'username', 'password'.encode('utf-8'))
    # print('Mapper:', auser.__mapper__)

    # 建立和測試資料表測試dbhisharp

    dbprv = DbProvide()
    Base.metadata.create_all(dbprv.db)
    user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    session = dbprv.session
    session.add(user_1)
    row = session.query(User).filter_by(name='user1').first()
    if row:
        print('Found user1')
        print(row)
    else:
        print('Can not find user1')
    session.rollback()  # 資料庫回到新增 user1 之前的狀態

    print("測試dbhisharp完成-------------")

    # 建立和測試資料表測試dbasa

    dbprv = DbProvide("asa")
    Base.metadata.create_all(dbprv.db)
    user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    session = dbprv.session
    session.add(user_1)
    row = session.query(User).filter_by(name='user1').first()
    if row:
        print('Found user1')
        print(row)
    else:
        print('Can not find user1')
    session.rollback()  # 資料庫回到新增 user1 之前的狀態

    print("測試dbasa完成-------------")

    # 建立和測試資料表測試dbasa

    dbprv = DbProvide("ups")
    Base.metadata.create_all(dbprv.db)
    user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
    session = dbprv.session
    session.add(user_1)
    row = session.query(User).filter_by(name='user1').first()
    if row:
        print('Found user1')
        print(row)
    else:
        print('Can not find user1')
    session.rollback()  # 資料庫回到新增 user1 之前的狀態

    print("測試dbups完成-------------")

db.Model的方法

先在一個資料庫上建立資料表，再將此資料表的結構，移到剩下的兩個資料庫

建立資料表

在專案的結構如下

1
2
3

database
	models.py
db_create.py

在database\models.py以下是放ORM的物件如下

import hashlib
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

在db_create.py中

# 建立基本資料表，通常沒有資料表的時候才會建立

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import db, User

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
# SQLALCHEMY_BINDS = {
#     'asa':        'mysql://root:12345678@localhost:33306/dbasa',
#     'ups':      'mysql://root:12345678@localhost:33307/dbups'
# }

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)

if __name__ == "__main__":
    try:
        with app.app_context():
            db.create_all()

            user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
            session = db.session
            session.add(user_1)
            row = session.query(User).filter_by(name='user1').first()
            if row:
                print('Found user1')
                print(row)
            else:
                print('Can not find user1')
            session.rollback()  # 資料庫回到新增 user1 之前的狀態
    except Exception as e:
        print(e)
    print('User資料表建立完成')

執行db_create.py後在dbhisharp的資料庫上會建立資料表，在將它的scheme匯出，在匯入到其它兩個的資料庫先到

D:\Project\mariadb\mariadb1\bin來執行匯出資料庫結構-R 是有其它的表(如sp)

1	mysqldump -u root -p -d -R dbhisharp > userschema.sql

在以HeidiSQL來載入此sqluserschema.sql檔來建立資料表

建立測試三個資料庫的資料-靜態

這個方法是資料庫在Flask-SQLAlchemy裏已經建立好了連接來處理

在原來的models.py有修改，主要是加入了__bind_key__為了是使用那一個資料庫

import hashlib
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password, bind=None):
        self.__bind_key__ = bind
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

先來測試CRUD裏的C,來各別的建立各別的資料來加入三個資料庫，可以來測試之後剩下的動作。在在專案的結構下加入Testdbdata.py

database
	models.py
db_create.py
Testdbdata.py

在Testdbdata.py裏加入程式碼

加入資料

# 增加
def TestAddDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'), bindkey)
        print(myuser)
        session.add(myuser)
        session.commit()

刪除資料

# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()

查詢資料

# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)

修改資料

# 修改
def TestModDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()

最後完整的Testdbdata.py

# 來建立測試用的資料
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import db, User
from multidbmanager import MultiDBMgr
app = Flask(__name__)

# 資料庫設定
# SQLALCHEMY_DATABASE_URI = 'mysql://root:12345678@localhost:3306/dbhisharp'
# SQLALCHEMY_BINDS = {
#     'asa':        'mysql://root:12345678@localhost:33306/dbasa',
#     'ups':      'mysql://root:12345678@localhost:33307/dbups'
# }
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {
    'asa': 'mysql://root:12345678@localhost:33306/dbasa',
    'ups': 'mysql://root:12345678@localhost:33307/dbups'
}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False
db.init_app(app)


# 增加
def TestAddDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'), bindkey)
        print(myuser)
        session.add(myuser)
        session.commit()


# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()


# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)


# 修改
def TestModDBdata(dbkey=""):
    bindkey = dbkey
    session = MultiDBMgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()


if __name__ == "__main__":
    try:
        # TestAddDBdata("asa")
        with app.app_context():
            # 增
            print("增加資料----------------")
            TestAddDBdata()
            TestAddDBdata("asa")
            TestAddDBdata("ups")
            # 修改
            print("修改資料----------------")
            TestModDBdata()
            TestModDBdata("asa")
            TestModDBdata("ups")
            # 查
            print("查詢資料----------------")
            TestFindDBdata()
            TestFindDBdata("asa")
            TestFindDBdata("ups")
            # 刪
            print("刪除資料----------------")
            TestDelDBdata()
            TestDelDBdata("asa")
            TestDelDBdata("ups")

    except Exception as e:
        print(e)
    print('資料表增刪查改完成')

先來建立單一資料庫的網頁

在目前的專案結構加入run.py如下

database\
    __init__.py
	models.py
multidbmanager.py
run.py

在run.py中加入以下的碼,執行後可以以網頁來瀏覽可以看到顯示single db OK

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import db, User
from multidbmanager import MultiDBMgr
app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {
    'asa': 'mysql://root:12345678@localhost:33306/dbasa',
    'ups': 'mysql://root:12345678@localhost:33307/dbups'
}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False
db.init_app(app)


@app.route("/", methods=["GET", "POST"])
def home():
    return "single db OK"


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

建立靜態網頁

我們在下面在建立一個資料夾叫templates在下面在建立home.html,所以專案結構看起來會是像

templates\
    home.html
database\
    __init__.py
	models.py
multidbmanager.py
run.py

在home.html中加入

<html>

<body>
    <h2>預設的資料庫 </h2>
    <form method="POST" action="/">
        <input type="hidden" value="" name="dbkey">
        Name:<br><input type="text" name="name" value=""><br>
        UserName:<br><input type="text" name="username" value=""><br>
        Password:<br><input type="password" name="password" value=""><br>
        <input type="submit" value="增加">
    </form>
</body>

</html>

在run.py中要加

1
2
3

@app.route("/", methods=["GET", "POST"])
def home():
    return render_template("home.html")

處理拿到使用者輸入的資料

要加入下面的模組

1	from flask import request

又網頁有GET和POST的請求，所以要加入到home

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        print(request.form)
    return render_template("home.html")

在網頁上，輸入資料可以在終端機上看到資訊，注意dbkey是代表使用那一個資料庫

 * Serving Flask app "run.py"
 * Environment: production
   WARNING: This is a development server. Do not use it in a production deployment.
   Use a production WSGI server instead.
 * Debug mode: off
 * Running on http://127.0.0.1:3000/ (Press CTRL+C to quit)
ImmutableMultiDict([('dbkey', ''), ('name', '111'), ('username', '222'), ('password', '333')])
127.0.0.1 - - [20/Aug/2019 15:17:47] "POST / HTTP/1.1" 200 -

Create資料

在run.py加入以下的碼

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = MultiDBMgr.getSession(bindkey)
        try:
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")
            # 檢查是否有重覆的資料
            row = session.query(User).filter_by(name=name).first()
            if not row:
                myuser = User(name, username, password.encode('utf-8'), bindkey)
                print(myuser)
                session.add(myuser)
                session.commit()
        except Exception as e:
            print("加入資料失敗")
            print(e)
    return render_template("home.html")

因為要那一個資料庫的資料run.py所以修改如下

@app.route("/", methods=["GET", "POST"])
def home():
    if request.form:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = MultiDBMgr.getSession(bindkey)
        try:
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")
            # 檢查是否有重覆的資料
            row = session.query(User).filter_by(name=name).first()
            if not row:
                myuser = User(name, username, password.encode('utf-8'), bindkey)
                print(myuser)
                session.add(myuser)
                session.commit()
        except Exception as e:
            print("加入資料失敗")
            print(e)
    # 取得各個列表
    # 預設列表
    users = None
    session = MultiDBMgr.getSession()
    users = session.query(User).all()
    # asa列表
    asausers = None
    session = MultiDBMgr.getSession("asa")
    asausers = session.query(User).all()
    return render_template("home.html", users=users, asausers=asausers)

在home.html修改如下

<html>

<body>
    <h2>預設的資料庫 </h2>
    <form method="POST" action="/">
        <input type="hidden" value="" name="dbkey">
        Name:<br><input type="text" name="name" value=""><br>
        UserName:<br><input type="text" name="username" value=""><br>
        Password:<br><input type="password" name="password" value=""><br>
        <input type="submit" value="增加">
    </form>
    <h3>資料列表</h3>
    {% for user in users %}
    <p>Name:{{user.name}}</p>
    <form method="POST" action="./update">
        <input type="hidden" value="" name="dbkey">
        <input type="hidden" value="{{user.id}}" name="id">
        UserName:
        <input type="text" value="{{user.username}}" name="newusername">
        <input type="submit" value="Update">
    </form>
    <form method="POST" action="./delete">
        <input type="hidden" value="" name="dbkey">
        <input type="hidden" value="{{user.id}}" name="id">
        <input type="submit" value="刪除">
    </form>
    {% endfor %}
    <hr>
    <h2>dbasa的資料庫 </h2>
    <form method="POST" action="/">
        <input type="hidden" value="asa" name="dbkey">
        Name:<br><input type="text" name="name" value=""><br>
        UserName:<br><input type="text" name="username" value=""><br>
        Password:<br><input type="password" name="password" value=""><br>
        <input type="submit" value="增加">
    </form>
    <h3>資料列表</h3>
    {% for user in asausers %}
    <p>Name:{{user.name}}</p>
    <span>
        <form method="POST" action="./update">
            <input type="hidden" value="asa" name="dbkey">
            <input type="hidden" value="{{user.id}}" name="id">
            UserName:
            <input type="text" value="{{user.username}}" name="newusername">
            <input type="submit" value="Update">
        </form>
        <form method="POST" action="./delete">
            <input type="hidden" value="asa" name="dbkey">
            <input type="hidden" value="{{user.id}}" name="id">
            <input type="submit" value="刪除">
        </form>
    </span>

    {% endfor %}
</body>

</html>

Update更新資料

在run.py將加入更新的程式碼如下，這裏有要注意的地方是，在form會傳入使用那一個資料庫dbkey

@app.route("/update", methods=["POST"])
def update():

    try:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = MultiDBMgr.getSession(bindkey)
        id = request.form.get("id")
        newusername = request.form.get("newusername")
        row = session.query(User).filter_by(id=id).first()
        if row:
            row.username = newusername
            session.commit()
    except Exception as e:
        print("更新資料失敗")
        print(e)
    return redirect("/")

以下的方法是codereview的做法

目前因專案是以db.Model來作為ORM的主體，所以是以db.Model為主,先來建立資料夾TestFlaskMultDB2

先來建立資料表

先在專案結構的目錄下，來建立database的資料夾，下面來建立__init__.py,models.py,multidbmanager.py,再來建立db_create.py,此檔案的目的是為了建立資料庫的資料表，目前的結構如下

TestFlaskMultDB2\
	database\
		__init__.py
		models.py
		multidbmanager.py
	db_create.py

在models.py主要是ORM模型，multidbmanager.py主要是來管理多個資料庫的切換，在db_create.py主要是建立資料庫中的資料表

在multidbmanage.py的程式碼如下

from flask import current_app
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class DBManager():
    def __init__(self):
        self.app = current_app
        self.db = db

    def check_bind(self, dbname=None):
        bindstat = None
        if dbname in current_app.config['SQLALCHEMY_BINDS']:
            bindstat = dbname
        return bindstat

    def getSession(self, dbname=None):
            # print(db.get_engine(self.app))
            # print(db.get_engine(self.app, 'asa'))
            # print(db.get_engine(self.app, 'ups'))
        engine = None
        bindstate = self.check_bind(dbname)
        if(bindstate == None):
            engine = db.get_engine(self.app)
        else:
            engine = db.get_engine(self.app, dbname)
        db.session.bind = engine
        session_maker = db.sessionmaker()
        session_maker.configure(bind=engine)
        session = session_maker()
        return session


dbmgr = DBManager()

在models.py的程式碼如下

import hashlib
from .multidbmanager import db, dbmgr


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password, bind=None):
        self.__bind_key__ = bind
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

    def getAll(dbname=None):
        session = dbmgr.getSession(dbname)
        return session.query(User).all()

在db_create.py的程式碼如下

# 建立基本資料表，通常沒有資料表的時候才會建立

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.multidbmanager import db

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
# app.config["SQLALCHEMY_BINDS"] = {
#     'asa': 'mysql://root:12345678@localhost:33306/dbasa',
#     'ups': 'mysql://root:12345678@localhost:33307/dbups'
# }

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)

if __name__ == "__main__":
    try:
        with app.app_context():
            db.create_all()

            user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
            session = db.session
            session.add(user_1)
            row = session.query(User).filter_by(name='user1').first()
            if row:
                print('Found user1')
                print(row)
            else:
                print('Can not find user1')
            session.rollback()  # 資料庫回到新增 user1 之前的狀態
    except Exception as e:
        print(e)
    print('User資料表建立完成')

執行db_create.py後在dbhisharp的資料庫上會建立資料表，在將它的scheme匯出，在匯入到其它兩個的資料庫先到

D:\Project\mariadb\mariadb1\bin來執行匯出資料庫結構-R 是有其它的表(如sp)

1	mysqldump -u root -p -d -R dbhisharp > userschema.sql

在以HeidiSQL來載入此sqluserschema.sql檔來建立資料表

來測試單元測試三個資料庫的CRUD的功能

再來建立Testdbdata.py,此檔案的目的是為了單元測試資料庫的資料表，目前的結構如下

TestFlaskMultDB2\
	database\
		__init__.py
		models.py
		multidbmanager.py
	db_create.py
	Testdbdata.py

在Testdbdata.py中的程式碼如下

# 來建立測試用的資料
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.multidbmanager import db, dbmgr

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {
    'asa': 'mysql://root:12345678@localhost:33306/dbasa',
    'ups': 'mysql://root:12345678@localhost:33307/dbups'
}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False
db.init_app(app)


# 增加
def TestAddDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'), bindkey)
        print(myuser)
        session.add(myuser)
        session.commit()


# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()


# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)


# 修改
def TestModDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()


if __name__ == "__main__":
    try:
        # TestAddDBdata("asa")
        with app.app_context():
            # 增
            print("增加資料----------------")
            TestAddDBdata()
            TestAddDBdata("asa")
            TestAddDBdata("ups")
            # 修改
            print("修改資料----------------")
            TestModDBdata()
            TestModDBdata("asa")
            TestModDBdata("ups")
            # 查
            print("查詢資料----------------")
            TestFindDBdata()
            TestFindDBdata("asa")
            TestFindDBdata("ups")
            # 刪
            print("刪除資料----------------")
            TestDelDBdata()
            TestDelDBdata("asa")
            TestDelDBdata("ups")

    except Exception as e:
        print(e)
    print('資料表增刪查改完成')

建立網頁來測試存取多個資料庫

先來建立templates的資料夾，下面在建立home.html和在建立run.py目前的結構如下

TestFlaskMultDB2\
	database\
		__init__.py
		models.py
		multidbmanager.py
	templstes\
         home.html
    run.py

在run.py的程式如下

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.multidbmanager import db, dbmgr
app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {
    'asa': 'mysql://root:12345678@localhost:33306/dbasa',
    'ups': 'mysql://root:12345678@localhost:33307/dbups'
}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False
db.init_app(app)


@app.route("/", methods=["GET", "POST"])
def home():
    # 取得使用那一個資料庫
    bindkey = request.form.get("dbkey")
    if request.form:

        session = dbmgr.getSession(bindkey)
        try:
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")
            # 檢查是否有重覆的資料
            row = User.findByName(name, bindkey)
            if not row:
                myuser = User(name, username, password.encode('utf-8'), bindkey)
                print(myuser)
                session.add(myuser)
                session.commit()
        except Exception as e:
            print("加入資料失敗")
            print(e)
    # 取得各個列表
    # 預設列表
    users = None
    users = User.getAll()
    # asa列表
    asausers = None
    asausers = User.getAll("asa")
    # ups 列表
    upsusers = None
    upsusers = User.getAll("ups")
    return render_template("home.html", users=users, asausers=asausers, upsusers=upsusers)


@app.route("/update", methods=["POST"])
def update():

    try:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = dbmgr.getSession(bindkey)
        id = request.form.get("id")
        newusername = request.form.get("newusername")
        row = session.query(User).filter_by(id=id).first()
        if row:
            row.username = newusername
            session.commit()
    except Exception as e:
        print("更新資料失敗")
        print(e)
    return redirect("/")

# 刪除
@app.route("/delete", methods=["POST"])
def delete():
    try:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = dbmgr.getSession(bindkey)
        print("{} (id={})".format(session, id(session)))
        uid = request.form.get("id")
        row = session.query(User).filter_by(id=uid).first()
        if row:
            User.delete(row, session)
            # session.delete(row)
            # session.commit()

    except Exception as e:
        print("刪除資料失敗")
        print(e)
    return redirect("/")


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

建立測試三個資料庫的資料-動態

目前的資料表和資料庫是使用上面所建立的基礎來擴充

1. 以sqlalchemy的功能來建立

先來建立資料夾來作為專案的主要資料夾TestFlaskMultDB3

單元測試三個資料庫的CRUD的功能

要測試三個資料庫，先來建立和使用一個資料庫，如果可以執行的話，那三個也是可以的，先來建立資料庫的管理和連結目前的專案結構是如下

TestFlaskMultDB3\
    Testdbdata.py
	database\
	    __init__.py
	    dbmanager.py
	    models.py

在database\models.py是放對映的ORM的物件程式碼如下

import hashlib
from .dbmanager import db


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password, bind=None):
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

在database\dbmanager.py是主要的切換資料庫的地方

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from flask import current_app
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class SessionManager(object):
    DBBinds = {}

    def __init__(self):
        print("SessionManager.__init__")
        self.dbname = None
        self.session = None

    # 取得資料庫的session
    def getSession(self, dbname=None):
        # 檢查是那一個資料庫
        db_name = None
        if dbname in SessionManager.DBBinds:
            db_name = dbname
        else:
            db_name = "default"
        # 檢查是否是同一個session
        if self.session:
            if self.session.name == db_name:
                return self.session
            else:
                self.session.remove()
                self.session = None

        if not self.session:
            engine = create_engine(SessionManager.DBBinds[db_name])
            db_session = scoped_session(sessionmaker(bind=engine))
            db_session.name = db_name
            self.session = db_session
        return db_session
    # 加入資料的連結

    def AddDBUrl(self, key, dburl):
        if key not in SessionManager.DBBinds:
            SessionManager.DBBinds[key] = dburl
        else:
            SessionManager.DBBinds[key] = dburl
        # print(SessionManager.DBBinds)


dbmgr = SessionManager()

單元測試的主程式Testdbdata.py的程式碼如下

# 來建立測試用的資料
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dbmanager import db, dbmgr

app = Flask(__name__)


# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False

# 動態加入資料庫
dbmgr.AddDBUrl("default", 'mysql://root:12345678@localhost:3306/dbhisharp')
dbmgr.AddDBUrl("asa", 'mysql://root:12345678@localhost:33306/dbasa')
dbmgr.AddDBUrl("ups", 'mysql://root:12345678@localhost:33307/dbups')

# 增加


def TestAddDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'))
        print(myuser)
        session.add(myuser)
    session.commit()


# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()


# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)


# 修改
def TestModDBdata(dbkey=""):
    bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()


if __name__ == "__main__":
    try:
        # TestAddDBdata("asa")
        with app.app_context():
            # 增加
            print("增加資料----------------")
            TestAddDBdata()
            TestAddDBdata("asa")
            TestAddDBdata("ups")
            # 修改
            print("修改資料----------------")
            TestModDBdata()
            TestModDBdata("asa")
            TestModDBdata("ups")
            # # 查
            print("查詢資料----------------")
            TestFindDBdata()
            TestFindDBdata("asa")
            TestFindDBdata("ups")
            # 刪
            print("刪除資料----------------")
            TestDelDBdata()
            TestDelDBdata("asa")
            TestDelDBdata("ups")

    except Exception as e:
        print(e)
    print('資料表增刪查改完成')

建立網頁來存取多個資料庫

先來建立templates的資料夾，下面在建立home.html和在建立run.py目前的結構如下

TestFlaskMultDB3\
    Testdbdata.py
	database\
	    __init__.py
	    dbmanager.py
	    models.py
	templates\
        home.html
     run.py

先來建立和取得所有的列表

在run.py中的程式碼如下

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dbmanager import dbmgr

app = Flask(__name__)


# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False


# 動態加入資料庫
dbmgr.AddDBUrl("default", 'mysql://root:12345678@localhost:3306/dbhisharp')
dbmgr.AddDBUrl("asa", 'mysql://root:12345678@localhost:33306/dbasa')
dbmgr.AddDBUrl("ups", 'mysql://root:12345678@localhost:33307/dbups')


@app.route("/", methods=["GET", "POST"])
def home():
    # 取得使用那一個資料庫
    bindkey = request.form.get("dbkey")
    if request.form:
        try:
            session = dbmgr.getSession(bindkey)
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")
            # 檢查是否有重覆的資料
            row = User.findByName(name, session)
            if not row:
                myuser = User(name, username, password.encode('utf-8'))
                print(myuser)
                session.add(myuser)
                session.commit()
        except Exception as e:
            print("加入資料失敗")
            print(e)
    # 取得各個列表
    # 預設列表
    users = None
    users = User.getAll()
    # asa列表
    asausers = None
    asausers = User.getAll("asa")
    # ups 列表
    upsusers = None
    upsusers = User.getAll("ups")
    return render_template("home.html", users=users, asausers=asausers, upsusers=upsusers)


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

加入更新資料

在原來的run.py中加入更新的功能

# 更新資料
@app.route("/update", methods=["POST"])
def update():

    try:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = dbmgr.getSession(bindkey)
        print("修改:{} (id={})".format(session, id(session)))
        uid = request.form.get("id")
        newusername = request.form.get("newusername")
        row = session.query(User).filter_by(id=uid).first()
        if row:
            row.username = newusername
            session.commit()
    except Exception as e:
        print("更新資料失敗")
        print(e)
    return redirect("/")

加入刪除資料

在原來的run.py中加入刪除的功能

# 刪除
@app.route("/delete", methods=["POST"])
def delete():
    try:
        # 取得使用那一個資料庫
        bindkey = request.form.get("dbkey")
        session = dbmgr.getSession(bindkey)
        print("刪除:{} (id={})".format(session, id(session)))
        uid = request.form.get("id")
        row = session.query(User).filter_by(id=uid).first()
        if row:
            User.delete(row, bindkey)
            # session.delete(row)
            # session.commit()

    except Exception as e:
        print("刪除資料失敗")
        print(e)
    return redirect("/")

2 .以flask_sqlalchemy的功能來建立

先來建立資料夾來作為專案的主要資料夾`TestFlaskMultDB4

單元測試三個資料庫的CRUD的功能

要測試三個資料庫，先來建立和使用一個資料庫，如果可以執行的話，那三個也是可以的，先來建立資料庫的管理和連結目前的專案結構是如下

TestFlaskMultDB4\
    Testdbdata.py
	database\
	    __init__.py
	    dyndbmanager.py
	    models.py

在database\models.py是放對映的ORM的物件程式碼如下

import hashlib
from .dyndbmanager import db, dbmgr


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password, bind=None):
        self.__bind_key__ = bind
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

在database\dyndbmanager.py是主要的切換資料庫的地方

from flask import current_app
from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()


class DynDBManager():
    def __init__(self):
        print("DynDBManager.__init__")
        self.app = current_app
        self.db = db
        self.dburl = None
        self.session = None

    # 加入資料的連結
    def AddDBUrl(self, key, dburl):
        if key not in current_app.config['SQLALCHEMY_BINDS']:
            current_app.config['SQLALCHEMY_BINDS'][key] = dburl
        else:
            current_app.config['SQLALCHEMY_BINDS'][key] = dburl
        # 重新整理連結的資料庫
        self.db.get_binds(self.app)
        print(current_app.config['SQLALCHEMY_BINDS'])

    def getSession(self, dbname=None):
        # 檢查那一個資料庫
        db_name = None
        if dbname in current_app.config['SQLALCHEMY_BINDS']:
            db_name = dbname

        # 檢查是否是同一個session
        if self.session:
            if self.session.name == db_name:
                return self.session
            else:
                self.session = None

        if not self.session:
            engine = db.get_engine(self.app, dbname)
            db.session.bind = engine
            session_maker = db.sessionmaker()
            session_maker.configure(bind=engine)
            session = session_maker()
            session.name = db_name
            self.session = session
            return session


dbmgr = DynDBManager()

單元測試的主程式Testdbdata.py的程式碼如下

# 來建立測試用的資料
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dyndbmanager import db, dbmgr

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)

# 動態加入資料庫
with app.app_context():
    dbmgr.AddDBUrl("asa", 'mysql://root:12345678@localhost:33306/dbasa')
    dbmgr.AddDBUrl("ups", 'mysql://root:12345678@localhost:33307/dbups')

# 增加


def TestAddDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'), bindkey)
        # 檢查是否有重覆的資料
        row = User.findByName(name, bindkey)
        if not row:
            print(myuser)
            session.add(myuser)
    session.commit()


# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()


# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)


# 修改
def TestModDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()


if __name__ == "__main__":
    try:
        with app.app_context():
            # 增加
            print("增加資料----------------")
            TestAddDBdata()
            TestAddDBdata("asa")
            TestAddDBdata("ups")
            # 修改
            print("修改資料----------------")
            TestModDBdata()
            TestModDBdata("asa")
            TestModDBdata("ups")
            # # 查
            # print("查詢資料----------------")
            TestFindDBdata()
            TestFindDBdata("asa")
            TestFindDBdata("ups")
            # # 刪
            # print("刪除資料----------------")
            TestDelDBdata()
            TestDelDBdata("asa")
            TestDelDBdata("ups")

    except Exception as e:
        print(e)
    print('資料表增刪查改完成')

建立網頁來存取多個資料庫

先來建立templates的資料夾，下面在建立home.html和在建立run.py目前的結構如下

TestFlaskMultDB3\
    Testdbdata.py
	database\
	    __init__.py
	    dyndbmanager.py
	    models.py
	templates\
        home.html
     run.py

先來建立和取得所有的列表

在run.py中的程式碼如下

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dyndbmanager import db, dbmgr

app = Flask(__name__)

# 資料庫設定
# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False

db.init_app(app)

# 動態加入資料庫
with app.app_context():
    dbmgr.AddDBUrl("asa", 'mysql://root:12345678@localhost:33306/dbasa')
    dbmgr.AddDBUrl("ups", 'mysql://root:12345678@localhost:33307/dbups')
    # db.get_binds()


def checkdbname(dbname=None):
    if dbname == "":
        return None
    else:
        return dbname


@app.route("/", methods=["GET", "POST"])
def home():
    # 取得使用那一個資料庫
    bindkey = checkdbname(request.form.get("dbkey"))
    if request.form:
        try:
            session = dbmgr.getSession(bindkey)
            print("新增:{} (id={})".format(session, id(session)))
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")
            # 檢查是否有重覆的資料
            row = User.findByName(name, bindkey)
            if not row:
                myuser = User(name, username, password.encode('utf-8'))
                print(myuser)
                session.add(myuser)
                session.commit()
        except Exception as e:
            print("加入資料失敗")
            print(e)
    # 取得各個列表
    # 預設列表
    users = None
    users = User.getAll()
    # asa列表
    asausers = None
    asausers = User.getAll("asa")
    # ups 列表
    upsusers = None
    upsusers = User.getAll("ups")
    return render_template("home.html", users=users, asausers=asausers, upsusers=upsusers)





if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

加入更新資料

在原來的run.py中加入更新的功能

# 更新資料
@app.route("/update", methods=["POST"])
def update():

    try:
        # 取得使用那一個資料庫
        bindkey = checkdbname(request.form.get("dbkey"))
        session = dbmgr.getSession(bindkey)
        print("修改:{} (id={})".format(session, id(session)))
        uid = request.form.get("id")
        newusername = request.form.get("newusername")
        row = session.query(User).filter_by(id=uid).first()
        if row:
            row.username = newusername
            session.commit()
    except Exception as e:
        print("更新資料失敗")
        print(e)
    return redirect("/")

加入刪除資料

在原來的run.py中加入刪除的功能

# 刪除
@app.route("/delete", methods=["POST"])
def delete():
    try:
        # 取得使用那一個資料庫
        bindkey = checkdbname(request.form.get("dbkey"))
        session = dbmgr.getSession(bindkey)
        print("刪除:{} (id={})".format(session, id(session)))
        uid = request.form.get("id")
        row = session.query(User).filter_by(id=uid).first()
        if row:
            User.delete(row, bindkey)
            # session.delete(row)
            # session.commit()

    except Exception as e:
        print("刪除資料失敗")
        print(e)
    return redirect("/")

以Angular來測試一下多個資料庫

之前的資料表和資料庫是不變，目前以angular來當前端主要的功能有

可以在網頁上動態的加入資料𢉦的連結
可以網頁上來切換選擇使用那一個資料來作操作

後台的建立基本上和上面的差不多只是要安裝python需要的程式庫,記得要在虛擬環境下安裝

1 2	pip install flask-marshmallow pip install marshmallow-sqlalchemy

先來建立資料夾TestFlaskMultDB5在下面在建立兩個資料夾client和server,client是給前端使用而server是給python後端來使用，檔案結構如下

1
2
3

TestFlaskMultDB5\
    client\
    server\

建立基本後台

後台的專案結構如下下以server的資料夾為開始的路徑

database\
    __init__.py
    dyndbmanager.py
    models.py
lib\
    retrespon.oy
db_create.py
run.py
Testdbdata.py

db_create.py主要是建立資料表

# 建立基本資料表，通常沒有資料表的時候才會建立

from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dyndbmanager import db

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
# app.config["SQLALCHEMY_BINDS"] = {
#     'asa': 'mysql://root:12345678@localhost:33306/dbasa',
#     'ups': 'mysql://root:12345678@localhost:33307/dbups'
# }

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)

if __name__ == "__main__":
    try:
        with app.app_context():
            db.create_all()

            user_1 = User('user1', 'username1', 'password_1'.encode('utf-8'))
            session = db.session
            session.add(user_1)
            row = session.query(User).filter_by(name='user1').first()
            if row:
                print('Found user1')
                print(row)
            else:
                print('Can not find user1')
            session.rollback()  # 資料庫回到新增 user1 之前的狀態
    except Exception as e:
        print(e)
    print('User資料表建立完成')

Testdbdata.py主要是單元測試，和資料庫的CRUD的動作是否正常

# 來建立測試用的資料
from flask import Flask, render_template, request
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User
from database.dyndbmanager import db, dbmgr

app = Flask(__name__)

# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = True
db.init_app(app)

# 動態加入資料庫
with app.app_context():
    dbmgr.AddDBUrl("asa", 'mysql://root:12345678@localhost:33306/dbasa')
    dbmgr.AddDBUrl("ups", 'mysql://root:12345678@localhost:33307/dbups')

# 增加


def TestAddDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        username = dbkey+"username"+str(idx)
        password = "password_"+str(idx)
        myuser = User(name, username, password.encode('utf-8'), bindkey)
        # 檢查是否有重覆的資料
        row = User.findByName(name, bindkey)
        if not row:
            print(myuser)
            session.add(myuser)
    session.commit()


# 刪除
def TestDelDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            session.delete(row)
    session.commit()


# 查詢
def TestFindDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            print(row)


# 修改
def TestModDBdata(dbkey=""):
    bindkey = None
    if dbkey != '':
        bindkey = dbkey
    session = dbmgr.getSession(bindkey)
    for idx in range(1, 10):
        name = dbkey+"user"+str(idx)
        row = session.query(User).filter_by(name=name).first()
        if row:
            row.username += "mod"
            print(row)
    session.commit()


if __name__ == "__main__":
    try:
        with app.app_context():
            # 增加
            print("增加資料----------------")
            TestAddDBdata()
            TestAddDBdata("asa")
            TestAddDBdata("ups")
            # 修改
            print("修改資料----------------")
            TestModDBdata()
            TestModDBdata("asa")
            TestModDBdata("ups")
            # # 查
            # print("查詢資料----------------")
            TestFindDBdata()
            TestFindDBdata("asa")
            TestFindDBdata("ups")
            # 刪
            # print("刪除資料----------------")
            TestDelDBdata()
            TestDelDBdata("asa")
            TestDelDBdata("ups")

    except Exception as e:
        print(e)
    print('資料表增刪查改完成')

因為後台動作的成功或失敗是要回傳結果給前台知道成功時可繼續來動作，失敗時可以通知前台為什麼失敗所以需要有物件來處理就是lib\retrespon.py

# 建立網頁回覆的json
def resData(status, msg, tokendata=None):
    if tokendata:
        responseObject = {
            "status": status,
            "message": msg,
            "auth_token": tokendata
        }
        return responseObject
    else:
        responseObject = {
            "status": status,
            "message": msg,
        }
        return responseObject

database\models.py主要的ORM物件

import hashlib
from .dyndbmanager import db, ma, dbmgr


class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    username = db.Column(db.String(80))
    password = db.Column(db.String(80))

    def __init__(self, name, username, password, bind=None):
        self.__bind_key__ = bind
        self.name = name
        self.username = username
        self.password = hashlib.sha1(password).hexdigest()

    def __repr__(self):
        return "User('{}','{}','{}')".format(self.name, self.username, self.password)

    def findByName(name, dbname):
        session = dbmgr.getSession(dbname)
        print("findByName:{} (id={})".format(session, id(session)))
        row = session.query(User).filter_by(name=name).first()
        return row

    def getAll(dbname=None):
        session = dbmgr.getSession(dbname)
        return session.query(User).all()

    def delete(row, dbname=None):
        session = dbmgr.getSession(dbname)
        print("delete{} (id={})".format(session, id(session)))
        session.delete(row)
        session.commit()


class UserSchema(ma.Schema):
    class Meta:
        # Fields to expose
        fields = ('id', 'name', 'username', 'password')

database\dyndbmanager.py主要是來取得目前在處理那一個資料庫，在有問題的連結會移除和重新將exception丟出

加入和刪除資料庫的連結

from flask import current_app
from flask_sqlalchemy import SQLAlchemy
from flask_marshmallow import Marshmallow
db = SQLAlchemy()
ma = Marshmallow()


class DynDBManager():
    def __init__(self):
        print("DynDBManager.__init__")
        self.app = current_app
        self.db = db
        self.dburl = None
        self.session = None

    # 加入資料的連結

    def AddDBUrl(self, key, dburl):
        if key not in current_app.config['SQLALCHEMY_BINDS']:
            current_app.config['SQLALCHEMY_BINDS'][key] = dburl
        else:
            current_app.config['SQLALCHEMY_BINDS'][key] = dburl
        # 重新整理連結的資料庫
        try:
            currlist = self.db.get_binds(self.app)
        except Exception as e:
            print(e)
            current_app.config['SQLALCHEMY_BINDS'].pop(key, None)
            raise e

        print(current_app.config['SQLALCHEMY_BINDS'])
    # 移除資料庫的連結

    def DelDBUrl(self, key):
        if key in current_app.config['SQLALCHEMY_BINDS']:
            current_app.config['SQLALCHEMY_BINDS'].pop(key, None)
        # 重新整理連結的資料庫
        try:
            currlist = self.db.get_binds(self.app)
        except Exception as e:
            print(e)
            current_app.config['SQLALCHEMY_BINDS'].pop(key, None)
            raise e
        print(current_app.config['SQLALCHEMY_BINDS'])

    def getSession(self, dbname=None):
        # 檢查那一個資料庫
        db_name = None
        if dbname in current_app.config['SQLALCHEMY_BINDS']:
            db_name = dbname

        # 檢查是否是同一個session
        if self.session:
            if self.session.name == db_name:
                return self.session
            else:
                self.session = None

        if not self.session:
            engine = db.get_engine(self.app, dbname)
            db.session.bind = engine
            session_maker = db.sessionmaker()
            session_maker.configure(bind=engine)
            session = session_maker()
            session.name = db_name
            self.session = session
            return session

    def getAllDB(self):
        dbbinds = []
        dicdbbinds = {"dbkey": "", "dburl": self.app.config['SQLALCHEMY_DATABASE_URI']}
        dbbinds.append(dicdbbinds)
        for k, v in current_app.config['SQLALCHEMY_BINDS'].items():
            dicdbbinds = {"dbkey": k, "dburl": v}
            dbbinds.append(dicdbbinds)
        return dbbinds


dbmgr = DynDBManager()

run.py主要程式

from flask import Flask, Response, request, jsonify
from flask_sqlalchemy import SQLAlchemy
from flask import redirect
from database.models import User, UserSchema
from database.dyndbmanager import db, ma, dbmgr
from flask_cors import CORS
from lib.retrespon import *

app = Flask(__name__)
CORS(app)
# 資料庫設定
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345678@localhost:3306/dbhisharp'
app.config["SQLALCHEMY_BINDS"] = {}

# 設置每次請求當結束後會自動提交數據庫中的改動
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = False

app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 查詢時會顯示原始SQL語句
app.config['SQLALCHEMY_ECHO'] = False

db.init_app(app)
ma.init_app(app)

# 檢查資料庫是否合法


def checkdbname(dbname=None):
    if dbname == "" or dbname == "default":
        return None
    else:
        return dbname


@app.route("/databaseproc", methods=["GET", "POST"])
def databaselist():

    try:
        if request.form:
            dbkey = request.form.get("dbkey")
            dburl = request.form.get("dburl")
            password = request.form.get("password")
            fmt = '%s-> %s' % (dbkey, dburl)
            print(fmt)
            dbmgr.AddDBUrl(dbkey, dburl)
            responseObject = resData("success", "資料庫連結加入成功")
            return jsonify(responseObject), 200
        else:
            # 取得資料庫列表
            result = dbmgr.getAllDB()
            X_Total_Count = len(result)
            resp = jsonify(result)
            resp.status_code = 200
            # 可加入多少個
            resp.headers.add('X-Total-Count', X_Total_Count)
            return resp
            # return jsonify(result)
    except Exception as e:
        print("Failed to api databaseproc")
        print(e)
        responseObject = resData("fail", "Failed to api databaseproc")
        return jsonify(responseObject), 500


@app.route("/databaseproc/<dbkey>", methods=["DELETE"])
def database_delete(dbkey):
    try:
        # if request.form:
        #     dbkey = request.form.get("dbkey")
        #     dburl = request.form.get("dburl")
        #     fmt = '%s-> %s' % (dbkey, dburl)
        # print(dbkey)
        dbmgr.DelDBUrl(dbkey)
        responseObject = resData("success", "資料庫連結刪除成功")
        return jsonify(responseObject), 200
        # user = User.query.get(id)
        # db.session.delete(user)
        # db.session.commit()
        # return user_schema.jsonify(user)
    except Exception as e:
        print("Failed to delete api databaseproc")
        print(e)
        responseObject = resData("fail", "Failed to delete api databaseproc")
        return jsonify(responseObject), 500

# 使用者相關
# endpoint to show all users
@app.route("/user/<dbkey>", methods=["GET"])
def get_user(dbkey):
    try:
        if dbkey == "default":
            dbkey = None
        page = int(request.args.get("page"))
        limit = int(request.args.get("limit"))
        # print("page=", page)
        # print("limit=", limit)
        pageobj = User.paginate(dbkey, page, limit)
        user_schema = UserSchema(many=True)
        result = user_schema.dump(pageobj.object_list)
        resp = jsonify(result)
        resp.status_code = 200
        # 全部有多少個
        X_Total_Count = pageobj.paginator.count
        resp.headers['Access-Control-Expose-Headers'] = 'X-Total-Count'
        resp.headers.add('X-Total-Count', X_Total_Count)
        return resp

    except Exception as e:
        print("Failed to get all user")
        print(e)
        result = {'data': 'notok'}
        return jsonify(result), 500

# endpoint to create new user
@app.route("/user", methods=["POST"])
def add_user():
    if request.form:
        try:
            dbkey = checkdbname(request.form.get('dbkey'))
            name = request.form.get("name")
            username = request.form.get("username")
            password = request.form.get("password")

            new_user = User(name, username, password.encode('utf-8'), dbkey)
            session = dbmgr.getSession(dbkey)
            session.add(new_user)
            session.commit()
            responseObject = resData("success", "使用者加入成功")
            return jsonify(responseObject), 200

        except Exception as e:
            print("Failed to add user")
            print(e)
            result = {'data': '加入使用者失敗'}
            return jsonify(result), 500

# endpoint to update user
@app.route("/user/<id>", methods=["PUT"])
def user_update(id):
    try:
        dbkey = checkdbname(request.form.get('dbkey'))
        name = request.form.get("name")
        username = request.form.get("username")
        password = request.form.get("password")
        row = User.getUser(id, dbkey)
        row.name = name
        row.username = username
        row.password = password
        session = dbmgr.getSession(dbkey)
        session.flush()
        responseObject = resData("success", "修改使用者成功")
        return jsonify(responseObject), 200

    except Exception as e:
        print("Failed to update user")
        print(e)
        result = {'data': '修改使用者失敗'}
        return jsonify(result), 500

# endpoint to delete user


@app.route("/user/<dbkey>/<id>", methods=["DELETE"])
def user_delete(dbkey, id):
    try:
        if dbkey == "default":
            dbkey = None
        row = User.getUser(id, dbkey)
        User.delete(row, dbkey)
        responseObject = resData("success", "使用者刪除成功")
        return jsonify(responseObject), 200
    except Exception as e:
        print("Failed to del user")
        print(e)
        result = {'data': '刪除使用者失敗'}
        return jsonify(result), 500


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=3000, debug=False)

建立基本的前台UI介面

在TestFlaskMultDB5\client下以命令列來建立angular的程式

1	ng new ngclient

建立基本的Layout

加人dashboard的模組

1	ng g m dashboard --routing

加人dashboard的元件

1	ng g c dashboard

安裝@angular/material和@angular/cdk

1	npm install --save @angular/material @angular/cdk

加入SharedAngularMaterial <https://ithelp.ithome.com.tw/articles/10209937

1	ng g m share\SharedAngularMaterial

加入theme設定

在’styles.scss’中,有四種選一種

@import "~@angular/material/prebuilt-themes/deeppurple-amber.css";
//@import "~@angular/material/prebuilt-themes/indigo-pink.css";
//@import "~@angular/material/prebuilt-themes/pink-bluegrey.css";
//@import "~@angular/material/prebuilt-themes/purple-green.css";

加入Material Icons

可以到此網站找到需要的Icon,Material Icons

在index.html

1	<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">

在MatIcon中使用SVG

必須透過Angular提供的DomSanitizer Service來信任這個路徑。接著我們就可以透過MatIconRegistry來擴充SVG icon

export class SharedAngularMaterialModule implements OnInit {
  constructor(private matIconRegistry: MatIconRegistry, private domSanitizer: DomSanitizer) {
    this.ngOnInit();
  }
  ngOnInit(): void {
    // this.matIconRegistry.addSvgIconInNamespace(
    //   "custom-svg",
    //   "angular",
    //   this.domSanitizer.bypassSecurityTrustResourceUrl("assets/images/angular_solidBlack.svg")
    // );
  }

在MatIcon中使用Icon Font

以FontAwesome為例，我們先來安裝庫

1	npm install --save @fortawesome/fontawesome-free

接下來在src\styles.scss中

@import "~@angular/material/prebuilt-themes/deeppurple-amber.css";
//@import "~@angular/material/prebuilt-themes/indigo-pink.css";
//@import "~@angular/material/prebuilt-themes/pink-bluegrey.css";
//@import "~@angular/material/prebuilt-themes/purple-green.css";
@import "~@fortawesome/fontawesome-free/css/all.css";

加入通知訊息

1	ng g s services\toaster

import { Injectable } from '@angular/core';
import { MatSnackBar } from '@angular/material/snack-bar';

@Injectable({
  providedIn: 'root'
})
export class ToasterService {

  constructor(private snackBar: MatSnackBar) { }
  showToaster(msg: string) {
    this.snackBar.open(msg, null, {
      duration: 2000,
    });
  }
}

打造基本後台UI

在src\app\dashboard\dashboard.component.html

<mat-toolbar class="demo-app-header" color="primary">
  <button mat-icon-button (click)="sideNav.toggle()">
    <mat-icon>{{sideNav.opened ? 'close':'menu'}}</mat-icon>
  </button>
  <span>動態元件 demo</span>
  <!-- 在這之後的都會被推到右邊去 -->
  <span class="toolbar-seprator"></span>
  <button mat-icon-button>
    <mat-icon>message</mat-icon>
  </button>
  <button mat-icon-button>
    <mat-icon>exit_to_app</mat-icon>
  </button>

</mat-toolbar>
<mat-sidenav-container class="demo-app-container">
  <mat-sidenav class="demo-app-sidenav" #sideNav mode="push">
    <mat-nav-list>
      <h3 matSubheader>示範用頁面</h3>
      <a [routerLink]="['/', 'dashboard', 'survey']" mat-list-item>問卷調查</a>
      <a [routerLink]="['/', 'dashboard', 'blog']" mat-list-item>部落格</a>
      <a [routerLink]="['/', 'dashboard', 'inbox']" mat-list-item>收件夾</a>
      <mat-divider></mat-divider>
      <!-- 另外一組選單 -->
      <h3 matSubheader>其他頁面</h3>
      <a [routerLink]="['/']" mat-list-item>首頁</a>
      <a [routerLink]="['/']" mat-list-item>Google</a>
      <a [routerLink]="['/']" mat-list-item>Facebook</a>
    </mat-nav-list>
  </mat-sidenav>
  <mat-sidenav-content>
    <div style="width: 100%;height: 20em;background-color: blueviolet">

    </div>
    <router-outlet></router-outlet>
  </mat-sidenav-content>
</mat-sidenav-container>

在src\app\dashboard\dashboard.component.ts

import { Component, OnInit } from '@angular/core';

@Component({
  selector: 'app-dashboard',
  templateUrl: './dashboard.component.html',
  styleUrls: ['./dashboard.component.scss']
})
export class DashboardComponent implements OnInit {

  constructor() { }

  ngOnInit() {
  }

}

在加入ToolBar有點小小問題，會隨著畫面捲動要讓toolbar個定在最上方設定一下CSS就好了在src\app\dashboard\dashboard.component.scss

mat-sidenav-container{
  width: 100%;
}
.demo-app-header {
    position: fixed;
    top: 0;
    z-index: 2;
  }

  .demo-app-container,
  .demo-app-sidenav {
    position: absolute;
    padding-top: 64px;
    //width: 100%;
 }
.toolbar-seprator{
  flex:1 1 auto;
}

修改src\app\app.module.ts加入需要的模組

imports: [
    BrowserModule,
    BrowserAnimationsModule,
    FormsModule,
    AppRoutingModule,
    DashboardModule
  ],

修改src\app\dashboard\dashboard.module.ts中加入需要的模組

imports: [
    CommonModule,
    FormsModule,
    SharedAngularMaterialModule,
    DashboardRoutingModule
  ]

修改路徑

src\app\app-routing.module.ts中

1
2
3

const routes: Routes = [
  { path: '', redirectTo: 'dashboard', pathMatch: 'full' }
];

在src\app\dashboard\dashboard-routing.module.ts中

const routes: Routes = [
  {
    path: 'dashboard', component: DashboardComponent, children: [
      
    ]
  },
];

在加入處理資料庫的頁面

1	ng g component dashboard/DataBasePage

在src\app\dashboard\dashboard-routing.module.ts中修改一下

const routes: Routes = [
  {
    path: 'dashboard', component: DashboardComponent, children: [
      { path: '', redirectTo: 'dbpage', pathMatch: 'full' },
      { path: 'dbpage', component: DataBasePageComponent }
    ]
  },
];

在src\app\dashboard\dashboard.component.html中修改

<mat-toolbar class="demo-app-header" color="primary">
  <button mat-icon-button (click)="sideNav.toggle()">
    <mat-icon>{{sideNav.opened ? 'close':'menu'}}</mat-icon>
  </button>
  <span>動態元件 demo</span>
  <!-- 在這之後的都會被推到右邊去 -->
  <span class="toolbar-seprator"></span>
  <button mat-icon-button>
    <mat-icon>message</mat-icon>
  </button>
  <button mat-icon-button>
    <mat-icon>exit_to_app</mat-icon>
  </button>

</mat-toolbar>
<mat-sidenav-container class="demo-app-container">
  <mat-sidenav class="demo-app-sidenav" #sideNav mode="push">
    <mat-nav-list>
      <h3 matSubheader>示範用頁面</h3>
      <a [routerLink]="['/', 'dashboard', 'survey']" mat-list-item>問卷調查</a>
      <a [routerLink]="['/', 'dashboard', 'blog']" mat-list-item>部落格</a>
      <a [routerLink]="['/', 'dashboard', 'inbox']" mat-list-item>收件夾</a>
      <mat-divider></mat-divider>
      <!-- 另外一組選單 -->
      <h3 matSubheader>管理資料庫</h3>
      <a [routerLink]="['/','dashboard','dbpage']" mat-list-item>資料庫</a>
    </mat-nav-list>
  </mat-sidenav>
  <mat-sidenav-content>
    <div style="width: 100%;height: 20em;background-color: blueviolet">

    </div>
    <router-outlet></router-outlet>
  </mat-sidenav-content>
</mat-sidenav-container>

產生加入資料庫的Dialog

因為要使用對話框來加入資料庫的資料，所以來產生一下對話框

1	ng g c dialog/add-database-dialog

在src\app\dialog\add-database-dialog\add-database-dialog.component.html

<h2 mat-dialog-title>新增資料庫連紿</h2>
<mat-dialog-content class="post-form">
  <mat-form-field>
    <input matInput placeholder="資料庫鍵值" [(ngModel)]="dbinof.dbkey" />
  </mat-form-field>
  <mat-form-field>
    <input matInput placeholder="資料庫連結" [(ngModel)]="dbinof.dburl" />
  </mat-form-field>
</mat-dialog-content>
<mat-dialog-actions>
  <button mat-button color="primary" (click)="Add()">加入</button>
  <button mat-button mat-dialog-close color="warn">取消</button>
</mat-dialog-actions>

在src\app\dialog\add-database-dialog\add-database-dialog.component.ts中有Add()的功能來加入資料到python的後台，之後在關畢對話框時，重新來刷新頁面來讀取新的資料庫列表。

import { Component, OnInit } from '@angular/core';
import { MatDialogRef, MatDialog } from '@angular/material/dialog';
import { DataBaseInfo } from 'src/app/models/database';
import { ToasterService } from 'src/app/services/toaster.service';
import { DbInfoService } from 'src/app/services/db-info.service';

@Component({
  selector: 'app-add-database-dialog',
  templateUrl: './add-database-dialog.component.html',
  styleUrls: ['./add-database-dialog.component.scss']
})
export class AddDatabaseDialogComponent implements OnInit {
  public dbinof: DataBaseInfo = new DataBaseInfo();
  constructor(private dialogRef: MatDialogRef<AddDatabaseDialogComponent>,
    private dbinfoservice: DbInfoService,
    private dialog: MatDialog,
    private toasterService: ToasterService) { }

  ngOnInit() {

  }
  //加入database的資料
  Add() {
    //console.log(this.dbinof);
    this.dbinfoservice.addDataBaseInfo(this.dbinof).subscribe(
      data => {
        console.log('success------------------' + data);
        //this.getDBInfos();
        this.toasterService.showToaster(data.message);
        this.dialogRef.close();
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.error.message);
        //this.toasterService.showToaster(error.error.message);
        this.dialogRef.close();
      }
    );

  }
}

在資料庫的頁面src\app\dashboard\data-base-page\data-base-page.component.html中加入

資料庫
<button mat-raised-button (click)="shwoAddDataBaseDialog()" color="primary">新增資料庫</button>
<hr>
資料庫列表
<mat-table #table [dataSource]="dataSource">
  <ng-container matColumnDef="dbkey">
    <mat-header-cell *matHeaderCellDef>資料庫主鍵</mat-header-cell>
    <mat-cell *matCellDef="let row">{{row.dbkey}} </mat-cell>
  </ng-container>
  <ng-container matColumnDef="dburl">
    <mat-header-cell *matHeaderCellDef>資料庫連結</mat-header-cell>
    <mat-cell *matCellDef="let row">{{row.dburl}} </mat-cell>
  </ng-container>
  <ng-container matColumnDef="management">
    <mat-header-cell *matHeaderCellDef>
      <u>管理</u>
    </mat-header-cell>
    <mat-cell *matCellDef="let row">
      <button mat-button color="primary">回覆</button>
      <button mat-button color="warn">刪除</button>
    </mat-cell>
  </ng-container>

  <mat-header-row *matHeaderRowDef="['dbkey','dburl','management']"></mat-header-row>
  <mat-row *matRowDef="let eachrows;columns:['dbkey','dburl','management']"></mat-row>
</mat-table>

在處理的主要程式src\app\dashboard\data-base-page\data-base-page.component.ts中加入

import { Component, OnInit } from '@angular/core';
import { DbInfoService } from 'src/app/services/db-info.service';
import { DataBaseInfo } from 'src/app/models/database';
import { ToasterService } from 'src/app/services/toaster.service';
import { MatTableDataSource } from '@angular/material/table';
import { MatDialog } from '@angular/material/dialog';
import { AddDatabaseDialogComponent } from 'src/app/dialog/add-database-dialog/add-database-dialog.component';

@Component({
  selector: 'app-data-base-page',
  templateUrl: './data-base-page.component.html',
  styleUrls: ['./data-base-page.component.scss']
})
export class DataBasePageComponent implements OnInit {
  dataSource = new MatTableDataSource<any>();
  totalCount: number;


  public dbinof: DataBaseInfo = new DataBaseInfo();

  constructor(
    private dbinfoservice: DbInfoService,
    private dialog: MatDialog,
    private toasterService: ToasterService) { }

  ngOnInit() {
    this.getDBInfos();
  }
  getDBInfos() {
    //console.log('getDBInfos------------------');
    // //curdblist = [];
    this.dbinfoservice.getDBInfos().subscribe(
      res => {
        //console.log(res);
        //this.dblist = res;
        this.dataSource.data = res;
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.statusText);
      }
    );
  }
  shwoAddDataBaseDialog() {
    const dialogRef = this.dialog.open(AddDatabaseDialogComponent);
    dialogRef.afterClosed().subscribe(res => {
      //console.log("對話框被關畢");
      //console.log(res);
      this.getDBInfos();
    });
  }
}

在主要呼叫對話框是在shwoAddDataBaseDialog()裏的this.dialog.open將要呼叫的對話框的元件傳入，即可呼叫會回傳它的參考傳，可以接起來，之後在對話框關畢的時候可以來訂閱它，收到之後可以重新來取資料庫的列表，在src\app\dashboard\dashboard.module.ts的模組，要將對話框的元件加入

imports: [
    CommonModule,
    FormsModule,
    SharedAngularMaterialModule,
    DashboardRoutingModule
  ],
  entryComponents: [AddDatabaseDialogComponent]

加入刪除資料庫的Dialog

先來產生刪除的對話框，這個對話框在之後有刪除使用者也可以使用到

1	ng g component dialog/delete-database-dialog

在src\app\dialog\delete-database-dialog\delete-database-dialog.component.html中加入

<h2 mat-dialog-title>移除資料庫的連結</h2>
<mat-dialog-content>
  確定要移除這資料庫的連結？<br>
  資料庫主鍵-> {{dbkey}} 資料庫連結-> {{dburl}}
</mat-dialog-content>
<mat-dialog-actions>
  <button mat-button color="primary" (click)="confirm()">確認</button>
  <button mat-button mat-dialog-close color="warn">取消</button>
</mat-dialog-actions>

在src\app\dialog\delete-database-dialog\delete-database-dialog.component.ts中加入

import { Component, OnInit, Inject } from '@angular/core';
import { MatDialog, MAT_DIALOG_DATA, MatDialogRef } from '@angular/material/dialog';
import { DbInfoService } from 'src/app/services/db-info.service';
import { ToasterService } from 'src/app/services/toaster.service';

@Component({
  selector: 'app-delete-database-dialog',
  templateUrl: './delete-database-dialog.component.html',
  styleUrls: ['./delete-database-dialog.component.scss']
})
export class DeleteDatabaseDialogComponent implements OnInit {
  get dbkey() {
    return this.data.dbinfo.dbkey;
  }
  get dburl() {
    return this.data.dbinfo.dburl;
  }
  constructor(
    private dbinfoservice: DbInfoService,
    private toasterService: ToasterService,
    private dialogRef: MatDialogRef<DeleteDatabaseDialogComponent>,
    @Inject(MAT_DIALOG_DATA) private data: any) { }

  ngOnInit() {
  }
  public confirm() {

    this.delete(this.data.dbinfo);
  }
  delete(dbinfo) {
    //console.log(dbinfo);
    this.dbinfoservice.delDataBaseInfo(dbinfo).subscribe(res => {
      //console.log(res);
      this.dialogRef.close();
      this.toasterService.showToaster(res.message);
    }, error => {
      console.log(error);
      this.toasterService.showToaster(error.statusText);
      this.dialogRef.close();
    });

  }
}

在模組src\app\dashboard\dashboard.module.ts要加入

1	entryComponents: [AddDatabaseDialogComponent,DeleteDialogComponent]

在資料庫的頁面src\app\dashboard\data-base-page\data-base-page.component.html中修改成如下，有加入移除的功能

資料庫
<button mat-raised-button (click)="shwoAddDataBaseDialog()" color="primary">新增資料庫</button>
<hr>
資料庫列表
<mat-table #table [dataSource]="dataSource">
  <ng-container matColumnDef="dbkey">
    <mat-header-cell *matHeaderCellDef>資料庫主鍵</mat-header-cell>
    <mat-cell *matCellDef="let row;">{{row.dbkey}}</mat-cell>
  </ng-container>
  <ng-container matColumnDef="dburl">
    <mat-header-cell *matHeaderCellDef>資料庫連結</mat-header-cell>
    <mat-cell *matCellDef="let row">{{row.dburl}} </mat-cell>
  </ng-container>
  <ng-container matColumnDef="management">
    <mat-header-cell *matHeaderCellDef>
      <u>管理</u>
    </mat-header-cell>
    <mat-cell *matCellDef="let row;">
      <!-- <button mat-button color="primary">回覆 </button> -->
      <button *ngIf="row.dbkey!=''" mat-button color="warn" (click)="showDeleteDialog(row)">移除</button>
    </mat-cell>
  </ng-container>

  <mat-header-row *matHeaderRowDef="['dbkey','dburl','management']"></mat-header-row>
  <mat-row *matRowDef="let eachrows;columns:['dbkey','dburl','management']"></mat-row>
</mat-table>

在處理的主要程式src\app\dashboard\data-base-page\data-base-page.component.ts中修改

import { Component, OnInit } from '@angular/core';
import { DbInfoService } from 'src/app/services/db-info.service';
import { DataBaseInfo } from 'src/app/models/database';
import { ToasterService } from 'src/app/services/toaster.service';
import { MatTableDataSource } from '@angular/material/table';
import { MatDialog } from '@angular/material/dialog';
import { AddDatabaseDialogComponent } from 'src/app/dialog/add-database-dialog/add-database-dialog.component';
import { DeleteDatabaseDialogComponent } from 'src/app/dialog/delete-database-dialog/delete-database-dialog.component';

@Component({
  selector: 'app-data-base-page',
  templateUrl: './data-base-page.component.html',
  styleUrls: ['./data-base-page.component.scss']
})
export class DataBasePageComponent implements OnInit {
  dataSource = new MatTableDataSource<any>();
  totalCount: number;


  public dbinof: DataBaseInfo = new DataBaseInfo();

  constructor(
    private dbinfoservice: DbInfoService,
    private dialog: MatDialog,
    private toasterService: ToasterService) { }

  ngOnInit() {
    this.getDBInfos();
  }
  getDBInfos() {
    //console.log('getDBInfos------------------');
    // //curdblist = [];
    this.dbinfoservice.getDBInfos().subscribe(
      res => {
        //console.log(res);
        //this.dblist = res;
        this.dataSource.data = res;
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.statusText);
      }
    );
  }
  shwoAddDataBaseDialog() {
    const dialogRef = this.dialog.open(AddDatabaseDialogComponent);
    dialogRef.afterClosed().subscribe(res => {
      //console.log("對話框被關畢");
      //console.log(res);
      this.getDBInfos();
    });
  }
  // 顯示刪除的對話框
  showDeleteDialog(row) {
    console.log(row);
    const dialogRef = this.dialog.open(DeleteDatabaseDialogComponent, {
      data: {
        dbinfo: row
      }
    });
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.getDBInfos();
    });
  }

}

加入處理使用者頁面

1	ng g component dashboard/UserPage

修改一下路徑src\app\dashboard\dashboard-routing.module.ts

const routes: Routes = [
  {
    path: 'dashboard', component: DashboardComponent, children: [
      { path: '', redirectTo: 'userpage', pathMatch: 'full' },
      { path: 'userpage', component: UserPageComponent },
      { path: 'dbpage', component: DataBasePageComponent }
    ]
  },
];

在src\app\dashboard\dashboard.component.html中加入連結

<mat-toolbar class="demo-app-header" color="primary">
  <button mat-icon-button (click)="sideNav.toggle()">
    <mat-icon>{{sideNav.opened ? 'close':'menu'}}</mat-icon>
  </button>
  <span>動態資料庫demo</span>
  <!-- 在這之後的都會被推到右邊去 -->
  <span class="toolbar-seprator"></span>
  <button mat-icon-button>
    <mat-icon>message</mat-icon>
  </button>
  <button mat-icon-button>
    <mat-icon>exit_to_app</mat-icon>
  </button>

</mat-toolbar>
<mat-sidenav-container class="demo-app-container">
  <mat-sidenav class="demo-app-sidenav" #sideNav mode="push">
    <mat-nav-list>
      <h3 matSubheader>使用者相關</h3>
      <a [routerLink]="['/', 'dashboard', 'userpage']" mat-list-item>使用者</a>
      <mat-divider></mat-divider>
      <!-- 另外一組選單 -->
      <h3 matSubheader>管理資料庫</h3>
      <a [routerLink]="['/','dashboard','dbpage']" mat-list-item>資料庫</a>
    </mat-nav-list>
  </mat-sidenav>
  <mat-sidenav-content>
    <!-- <div style="width: 100%;height: 20em;background-color: blueviolet">

    </div> -->
    <router-outlet></router-outlet>
  </mat-sidenav-content>
</mat-sidenav-container>

產生加入使用者的Dialog

1	ng g component dialog/add-user-dialog

在src\app\dialog\add-user-dialog\add-user-dialog.component.html中修改

<h2 mat-dialog-title>新增資料庫連紿</h2>
<mat-dialog-content class="post-form">
  <mat-form-field>
    <input matInput placeholder="資料庫鍵值" [(ngModel)]="dbinof.dbkey" />
  </mat-form-field>
  <br>
  <mat-form-field>
    <input matInput placeholder="資料庫連結" [(ngModel)]="dbinof.dburl" />
  </mat-form-field>
</mat-dialog-content>
<mat-dialog-actions>
  <button mat-button color="primary" (click)="Add()">加入</button>
  <button mat-button mat-dialog-close color="warn">取消</button>
</mat-dialog-actions>

在src\app\dialog\add-database-dialog\add-database-dialog.component.ts中修改

import { Component, OnInit } from '@angular/core';
import { MatDialogRef, MatDialog } from '@angular/material/dialog';
import { DataBaseInfo } from 'src/app/models/database';
import { ToasterService } from 'src/app/services/toaster.service';
import { DbInfoService } from 'src/app/services/db-info.service';

@Component({
  selector: 'app-add-database-dialog',
  templateUrl: './add-database-dialog.component.html',
  styleUrls: ['./add-database-dialog.component.scss']
})
export class AddDatabaseDialogComponent implements OnInit {
  public dbinof: DataBaseInfo = new DataBaseInfo();
  constructor(private dialogRef: MatDialogRef<AddDatabaseDialogComponent>,
    private dbinfoservice: DbInfoService,
    private dialog: MatDialog,
    private toasterService: ToasterService) { }

  ngOnInit() {

  }
  //加入database的資料
  Add() {
    //console.log(this.dbinof);
    this.dbinfoservice.addDataBaseInfo(this.dbinof).subscribe(
      data => {
        //console.log('success------------------' + data);
        //this.getDBInfos();
        this.toasterService.showToaster(data.message);
        this.dialogRef.close();
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.error.message);
        //this.toasterService.showToaster(error.error.message);
        this.dialogRef.close();
      }
    );

  }
}

在src\app\dashboard\user-page\user-page.component.html中修改

使用者相關
<hr>
<mat-form-field>
  <mat-label>選擇資料庫</mat-label>
  <mat-select [(ngModel)]="dbinfoservice.Seldbkey" name="dbkey" (ngModelChange)="onDBSelection()">
    <mat-option *ngFor="let db of dblist" [value]="db.dbkey">
      {{db.dbkey}}
    </mat-option>
  </mat-select>
</mat-form-field>
<hr>
加入使用者
<button [disabled]="!dbinfoservice.Seldbkey" mat-raised-button (click)="showAddUserDialog()" color="primary">加入</button>

<hr>
使用者列表
<mat-paginator #paginator [length]="totalCount" [pageIndex]="0" [pageSize]="pageSize"
  [pageSizeOptions]="pageSizeOptions">

</mat-paginator>
<mat-table #table [dataSource]="dataSource">
  <ng-container matColumnDef="name">
    <mat-header-cell *matHeaderCellDef>姓名</mat-header-cell>
    <mat-cell *matCellDef="let row;">{{row.name}}</mat-cell>
  </ng-container>
  <ng-container matColumnDef="username">
    <mat-header-cell *matHeaderCellDef>暱稱</mat-header-cell>
    <mat-cell *matCellDef="let row">{{row.username}} </mat-cell>
  </ng-container>
  <ng-container matColumnDef="management">
    <mat-header-cell *matHeaderCellDef>
      <u>管理</u>
    </mat-header-cell>
    <mat-cell *matCellDef="let row;">
      <!-- <button mat-button color="primary">回覆 </button> -->
      <button *ngIf="row.dbkey!=''" mat-button color="warn">刪除</button>
    </mat-cell>
  </ng-container>

  <mat-header-row *matHeaderRowDef="['name','username','management']"></mat-header-row>
  <mat-row *matRowDef="let eachrows;columns:['name','username','management']"></mat-row>
</mat-table>

在src\app\dashboard\user-page\user-page.component.ts中修改

import { Component, OnInit, ViewChild } from '@angular/core';
import { DataBaseInfo } from 'src/app/models/database';
import { DbInfoService } from 'src/app/services/db-info.service';
import { User } from 'src/app/models/user';
import { UserService } from 'src/app/services/user.service';
import { MatTableDataSource } from '@angular/material/table';
import { ToasterService } from 'src/app/services/toaster.service';
import { MatPaginator, PageEvent, MatPaginatorIntl } from '@angular/material/paginator';
import { MatDialog } from '@angular/material/dialog';
import { AddUserDialogComponent } from 'src/app/dialog/add-user-dialog/add-user-dialog.component';

@Component({
  selector: 'app-user-page',
  templateUrl: './user-page.component.html',
  styleUrls: ['./user-page.component.scss']
})
export class UserPageComponent implements OnInit {
  //分頁功能
  @ViewChild(MatPaginator, { static: true }) paginator: MatPaginator;
  totalCount: number = 0;
  pageSize = 2;
  pageSizeOptions: number[] = [2, 4, 6];
  private userUrl = "http://localhost:3000";

  dblist: DataBaseInfo[] = [];
  dataSource = new MatTableDataSource<any>();
  constructor(
    private dialog: MatDialog,
    public dbinfoservice: DbInfoService,
    private userservice: UserService,
    private toasterService: ToasterService,
    private matPaginatorIntl: MatPaginatorIntl, ) { }
  ngOnInit() {
    this.getDBInfos();

    // 分頁切換時，重新取得資料
    this.paginator.page.subscribe((page: PageEvent) => {
      //console.log(`pageIndex=${page.pageIndex}:pageSize=${page.pageSize}`);
      this.getPages(page.pageIndex, page.pageSize);
    });
    // 設定顯示筆數資訊文字
    this.matPaginatorIntl.getRangeLabel = (page: number, pageSize: number, length: number): string => {
      if (length === 0 || pageSize === 0) {
        return `第 0 筆、共  ${length} 筆`;
      }
      length = Math.max(length, 0);
      const startIndex = page * pageSize;
      const ednIndex = startIndex < length ? Math.min(startIndex + pageSize, length) : startIndex + pageSize;
      return `第 ${startIndex + 1} - ${ednIndex} 筆、共  ${length} 筆`
    };
    // 設定其他顯示資訊文字
    this.matPaginatorIntl.itemsPerPageLabel = '每頁筆數：';
    this.matPaginatorIntl.nextPageLabel = '下一頁';
    this.matPaginatorIntl.previousPageLabel = '上一頁';
  }
  getDBInfos() {
    //console.log('getDBInfos------------------');
    // //curdblist = [];
    this.dbinfoservice.getDBInfos().subscribe((res) => {

      //console.log(res);
      res.forEach(item => {
        //console.log(item);
        if (item.dbkey == "") {
          item.dbkey = "default"
        }
      });
      this.dblist = res;
    });
  }
  onDBSelection() {
    let fmt = `onDBSelection:Seldbkey=${this.dbinfoservice.Seldbkey}`;
    console.log(fmt);
    this.getPages(0, 2);
  }

  //分頁功能相關
  getPages(pageIndex, PageSize) {
    this.userservice.getUsers(this.dbinfoservice.Seldbkey, pageIndex, PageSize).subscribe(
      resp => {
        //console.log(res);
        this.dataSource.data = resp.body;
        // //分頁功能
        this.totalCount = Number(resp.headers.get('X-Total-Count'));
        //console.log(resp.headers.get('X-Total-Count'));
        // 從後端取得資料時，就不用指定data srouce的paginator了
        //this.dataSource.paginator = this.paginator;
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.statusText);
      }
    );
  }
  // 顯示加入使用者
  showAddUserDialog() {
    const dialogRef = this.dialog.open(AddUserDialogComponent);
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.paginator.pageSize = this.pageSize;
      this.onDBSelection();
    });
  }
}

加入刪除使用者的Dialog

1	ng g component dialog/delete-user-dialog

在src\app\dialog\delete-user-dialog\delete-user-dialog.component.html中加入

<h2 mat-dialog-title>刪除使用者</h2>
<mat-dialog-content>
  確定要刪除這使用者？<br>
  名稱-> {{name}} 暱稱-> {{username}}
</mat-dialog-content>
<mat-dialog-actions>
  <button mat-button color="primary" (click)="confirm()">確認</button>
  <button mat-button mat-dialog-close color="warn">取消</button>
</mat-dialog-actions>

在src\app\dialog\delete-user-dialog\delete-user-dialog.component.ts中加入

import { Component, OnInit, Inject } from '@angular/core';
import { MAT_DIALOG_DATA, MatDialogRef } from '@angular/material/dialog';
import { UserService } from 'src/app/services/user.service';
import { ToasterService } from 'src/app/services/toaster.service';

@Component({
  selector: 'app-delete-user-dialog',
  templateUrl: './delete-user-dialog.component.html',
  styleUrls: ['./delete-user-dialog.component.scss']
})
export class DeleteUserDialogComponent implements OnInit {

  get name() {
    return this.data.iteminfo.name;
  }
  get username() {
    return this.data.iteminfo.username;
  }

  constructor(
    private dialogRef: MatDialogRef<DeleteUserDialogComponent>,
    private userservice: UserService,
    private toasterService: ToasterService,
    @Inject(MAT_DIALOG_DATA) private data: any
  ) { }

  ngOnInit() {
  }
  confirm() {
    this.delete(this.data.iteminfo);
  }
  delete(iteminfo) {
    //console.log(iteminfo);
    this.userservice.delUser(iteminfo).subscribe(res => {
      //console.log(res);
      this.dialogRef.close();
      this.toasterService.showToaster(res.message);
    }, error => {
      console.log(error);
      this.toasterService.showToaster(error.statusText);
      this.dialogRef.close();
    });
  }
}

在src\app\dashboard\user-page\user-page.component.ts中加入

import { Component, OnInit, ViewChild } from '@angular/core';
import { DataBaseInfo } from 'src/app/models/database';
import { DbInfoService } from 'src/app/services/db-info.service';
import { User } from 'src/app/models/user';
import { UserService } from 'src/app/services/user.service';
import { MatTableDataSource } from '@angular/material/table';
import { ToasterService } from 'src/app/services/toaster.service';
import { MatPaginator, PageEvent, MatPaginatorIntl } from '@angular/material/paginator';
import { MatDialog } from '@angular/material/dialog';
import { AddUserDialogComponent } from 'src/app/dialog/add-user-dialog/add-user-dialog.component';
import { DeleteUserDialogComponent } from 'src/app/dialog/delete-user-dialog/delete-user-dialog.component';

@Component({
  selector: 'app-user-page',
  templateUrl: './user-page.component.html',
  styleUrls: ['./user-page.component.scss']
})
export class UserPageComponent implements OnInit {
  //分頁功能
  @ViewChild(MatPaginator, { static: true }) paginator: MatPaginator;
  totalCount: number = 0;
  pageSize = 2;
  pageSizeOptions: number[] = [2, 4, 6];
  private userUrl = "http://localhost:3000";

  dblist: DataBaseInfo[] = [];
  dataSource = new MatTableDataSource<any>();
  constructor(
    private dialog: MatDialog,
    public dbinfoservice: DbInfoService,
    private userservice: UserService,
    private toasterService: ToasterService,
    private matPaginatorIntl: MatPaginatorIntl, ) { }
  ngOnInit() {
    this.getDBInfos();

    // 分頁切換時，重新取得資料
    this.paginator.page.subscribe((page: PageEvent) => {
      //console.log(`pageIndex=${page.pageIndex}:pageSize=${page.pageSize}`);
      this.getPages(page.pageIndex, page.pageSize);
    });
    // 設定顯示筆數資訊文字
    this.matPaginatorIntl.getRangeLabel = (page: number, pageSize: number, length: number): string => {
      if (length === 0 || pageSize === 0) {
        return `第 0 筆、共  ${length} 筆`;
      }
      length = Math.max(length, 0);
      const startIndex = page * pageSize;
      const ednIndex = startIndex < length ? Math.min(startIndex + pageSize, length) : startIndex + pageSize;
      return `第 ${startIndex + 1} - ${ednIndex} 筆、共  ${length} 筆`
    };
    // 設定其他顯示資訊文字
    this.matPaginatorIntl.itemsPerPageLabel = '每頁筆數：';
    this.matPaginatorIntl.nextPageLabel = '下一頁';
    this.matPaginatorIntl.previousPageLabel = '上一頁';
  }
  getDBInfos() {
    //console.log('getDBInfos------------------');
    // //curdblist = [];
    this.dbinfoservice.getDBInfos().subscribe((res) => {

      //console.log(res);
      res.forEach(item => {
        //console.log(item);
        if (item.dbkey == "") {
          item.dbkey = "default"
        }
      });
      this.dblist = res;
    });
  }
  onDBSelection() {
    let fmt = `onDBSelection:Seldbkey=${this.dbinfoservice.Seldbkey}`;
    console.log(fmt);
    this.getPages(0, 2);
  }

  //分頁功能相關
  getPages(pageIndex, PageSize) {
    this.userservice.getUsers(this.dbinfoservice.Seldbkey, pageIndex, PageSize).subscribe(
      resp => {
        //console.log(res);
        this.dataSource.data = resp.body;
        // //分頁功能
        this.totalCount = Number(resp.headers.get('X-Total-Count'));
        //console.log(resp.headers.get('X-Total-Count'));
        // 從後端取得資料時，就不用指定data srouce的paginator了
        //this.dataSource.paginator = this.paginator;
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.statusText);
      }
    );
  }
  reloaduser() {
    this.paginator.pageSize = this.pageSize;
    this.onDBSelection();
  }
  // 顯示加入使用者
  showAddUserDialog() {
    const dialogRef = this.dialog.open(AddUserDialogComponent);
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.reloaduser();
    });
  }
  // 顯示刪除的對話框
  showDeleteUserDialog(row) {
    //console.log(row);
    const dialogRef = this.dialog.open(DeleteUserDialogComponent, {
      data: {
        iteminfo: row
      }
    });
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.reloaduser();
    });
  }
}

加入修改使用者

1	ng g component dialog/modify-user-dialog

在src\app\dialog\delete-user-dialog\delete-user-dialog.component.ts中加入

<h2 mat-dialog-title>修改使用者</h2>
<mat-dialog-content>
  確定要修改這使用者？<br>
  名稱-> {{name}} 暱稱-> {{username}}
</mat-dialog-content>
<mat-dialog-actions>
  <button mat-button color="primary" (click)="confirm()">確認</button>
  <button mat-button mat-dialog-close color="warn">取消</button>
</mat-dialog-actions>

在src\app\dialog\modify-user-dialog\modify-user-dialog.component.ts中加入

import { Component, OnInit, Inject } from '@angular/core';
import { MatDialogRef, MAT_DIALOG_DATA } from '@angular/material/dialog';
import { UserService } from 'src/app/services/user.service';
import { ToasterService } from 'src/app/services/toaster.service';

@Component({
  selector: 'app-modify-user-dialog',
  templateUrl: './modify-user-dialog.component.html',
  styleUrls: ['./modify-user-dialog.component.scss']
})
export class ModifyUserDialogComponent implements OnInit {
  get name() {
    return this.data.iteminfo.name;
  }
  get username() {
    return this.data.iteminfo.username;
  }
  constructor(
    private dialogRef: MatDialogRef<ModifyUserDialogComponent>,
    private userservice: UserService,
    private toasterService: ToasterService,
    @Inject(MAT_DIALOG_DATA) private data: any
  ) { }

  ngOnInit() {
  }
  confirm() {
    this.modify(this.data.iteminfo);
  }
  modify(iteminfo) {
    this.userservice.modify(iteminfo).subscribe(res => {
      //console.log(res);
      this.dialogRef.close();
      this.toasterService.showToaster(res.message);
    }, error => {
      console.log(error);
      this.dialogRef.close();
      this.toasterService.showToaster(error.statusText);

    });

  }
}

在src\app\dashboard\user-page\user-page.component.html中加入修改使用者

使用者相關
<hr>
<mat-form-field>
  <mat-label>選擇資料庫</mat-label>
  <mat-select [(ngModel)]="dbinfoservice.Seldbkey" name="dbkey" (ngModelChange)="onDBSelection()">
    <mat-option *ngFor="let db of dblist" [value]="db.dbkey">
      {{db.dbkey}}
    </mat-option>
  </mat-select>
</mat-form-field>
<hr>
加入使用者
<button [disabled]="!dbinfoservice.Seldbkey" mat-raised-button (click)="showAddUserDialog()" color="primary">加入</button>

<hr>
使用者列表
<mat-paginator #paginator [length]="totalCount" [pageIndex]="0" [pageSize]="pageSize"
  [pageSizeOptions]="pageSizeOptions">

</mat-paginator>
<mat-table #table [dataSource]="dataSource">
  <ng-container matColumnDef="name">
    <mat-header-cell *matHeaderCellDef>姓名</mat-header-cell>
    <mat-cell *matCellDef="let row;">
      <mat-form-field floatLabel="never">
        <input matInput placeholder="Name" [value]="row.name" [(ngModel)]="row.name">
      </mat-form-field>
    </mat-cell>
  </ng-container>
  <ng-container matColumnDef="username">
    <mat-header-cell *matHeaderCellDef>暱稱</mat-header-cell>
    <mat-cell *matCellDef="let row">{{row.username}} </mat-cell>
  </ng-container>
  <ng-container matColumnDef="management">
    <mat-header-cell *matHeaderCellDef>
      <u>管理</u>
    </mat-header-cell>
    <mat-cell *matCellDef="let row;">
      <button *ngIf="row.dbkey!=''" mat-button color="primary" (click)="showModifyUserDialog(row)">修改 </button>
      <button *ngIf="row.dbkey!=''" mat-button color="warn" (click)="showDeleteUserDialog(row)">刪除</button>
    </mat-cell>
  </ng-container>

  <mat-header-row *matHeaderRowDef="['name','username','management']"></mat-header-row>
  <mat-row *matRowDef="let eachrows;columns:['name','username','management']"></mat-row>
</mat-table>

在src\app\dashboard\user-page\user-page.component.ts中加入修改使用者

import { Component, OnInit, ViewChild } from '@angular/core';
import { DataBaseInfo } from 'src/app/models/database';
import { DbInfoService } from 'src/app/services/db-info.service';
import { User } from 'src/app/models/user';
import { UserService } from 'src/app/services/user.service';
import { MatTableDataSource } from '@angular/material/table';
import { ToasterService } from 'src/app/services/toaster.service';
import { MatPaginator, PageEvent, MatPaginatorIntl } from '@angular/material/paginator';
import { MatDialog } from '@angular/material/dialog';
import { AddUserDialogComponent } from 'src/app/dialog/add-user-dialog/add-user-dialog.component';
import { DeleteUserDialogComponent } from 'src/app/dialog/delete-user-dialog/delete-user-dialog.component';
import { ModifyUserDialogComponent } from 'src/app/dialog/modify-user-dialog/modify-user-dialog.component';

@Component({
  selector: 'app-user-page',
  templateUrl: './user-page.component.html',
  styleUrls: ['./user-page.component.scss']
})
export class UserPageComponent implements OnInit {
  //分頁功能
  @ViewChild(MatPaginator, { static: true }) paginator: MatPaginator;
  totalCount: number = 0;
  pageSize = 2;
  pageSizeOptions: number[] = [2, 4, 6];
  private userUrl = "http://localhost:3000";

  dblist: DataBaseInfo[] = [];
  dataSource = new MatTableDataSource<any>();
  constructor(
    private dialog: MatDialog,
    public dbinfoservice: DbInfoService,
    private userservice: UserService,
    private toasterService: ToasterService,
    private matPaginatorIntl: MatPaginatorIntl, ) { }
  ngOnInit() {
    this.getDBInfos();

    // 分頁切換時，重新取得資料
    this.paginator.page.subscribe((page: PageEvent) => {
      //console.log(`pageIndex=${page.pageIndex}:pageSize=${page.pageSize}`);
      this.getPages(page.pageIndex, page.pageSize);
    });
    // 設定顯示筆數資訊文字
    this.matPaginatorIntl.getRangeLabel = (page: number, pageSize: number, length: number): string => {
      if (length === 0 || pageSize === 0) {
        return `第 0 筆、共  ${length} 筆`;
      }
      length = Math.max(length, 0);
      const startIndex = page * pageSize;
      const ednIndex = startIndex < length ? Math.min(startIndex + pageSize, length) : startIndex + pageSize;
      return `第 ${startIndex + 1} - ${ednIndex} 筆、共  ${length} 筆`
    };
    // 設定其他顯示資訊文字
    this.matPaginatorIntl.itemsPerPageLabel = '每頁筆數：';
    this.matPaginatorIntl.nextPageLabel = '下一頁';
    this.matPaginatorIntl.previousPageLabel = '上一頁';
  }
  getDBInfos() {
    //console.log('getDBInfos------------------');
    // //curdblist = [];
    this.dbinfoservice.getDBInfos().subscribe((res) => {

      //console.log(res);
      res.forEach(item => {
        //console.log(item);
        if (item.dbkey == "") {
          item.dbkey = "default"
        }
      });
      this.dblist = res;
    });
  }
  onDBSelection() {
    let fmt = `onDBSelection:Seldbkey=${this.dbinfoservice.Seldbkey}`;
    console.log(fmt);
    this.getPages(0, 2);
  }

  //分頁功能相關
  getPages(pageIndex, PageSize) {
    this.userservice.getUsers(this.dbinfoservice.Seldbkey, pageIndex, PageSize).subscribe(
      resp => {
        //console.log(res);
        this.dataSource.data = resp.body;
        // //分頁功能
        this.totalCount = Number(resp.headers.get('X-Total-Count'));
        //console.log(resp.headers.get('X-Total-Count'));
        // 從後端取得資料時，就不用指定data srouce的paginator了
        //this.dataSource.paginator = this.paginator;
      },
      error => {
        console.log(error);
        this.toasterService.showToaster(error.statusText);
      }
    );
  }
  reloaduser() {
    this.paginator.pageSize = this.pageSize;
    this.onDBSelection();
  }
  // 顯示加入使用者
  showAddUserDialog() {
    const dialogRef = this.dialog.open(AddUserDialogComponent);
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.reloaduser();
    });
  }
  // 顯示刪除的對話框
  showDeleteUserDialog(row) {
    //console.log(row);
    const dialogRef = this.dialog.open(DeleteUserDialogComponent, {
      data: {
        iteminfo: row
      }
    });
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.reloaduser();
    });
  }
  //顯示修改的對話框
  showModifyUserDialog(row) {
    const dialogRef = this.dialog.open(ModifyUserDialogComponent, {
      data: {
        iteminfo: row
      }
    });
    dialogRef.afterClosed().subscribe(res => {
      //console.log(res);
      this.reloaduser();
    });
  }
}

加入分頁

先來安裝分頁模組

1	pip install SQLAlchemy-Paginator

在安裝模組要修改一下，以免不能執行D:\project\github\studypython\pyvirenv\pyenv37\lib\site-packages\sqlalchemy_paginator\paginator.py參考一下

1	from .exceptions import PageNotAnInteger, EmptyPage

在run.py中的get_user的功能要修改一下，因為想要在response heads將全部的數量加到reaponse heads所以要改寫一下注意如果client要可以取回加在head裏的值，在python回傳時也要設定一下Access-Control-Expose-Headers在下面的程式碼會看到，如果不加，目前client取不到此值

@app.route("/user/<dbkey>", methods=["GET"])
def get_user(dbkey):
    try:
        if dbkey == "default":
            dbkey = None
        page = int(request.args.get("page"))
        limit = int(request.args.get("limit"))
        # print("page=", page)
        # print("limit=", limit)
        pageobj = User.paginate(dbkey, page, limit)
        user_schema = UserSchema(many=True)
        result = user_schema.dump(pageobj.object_list)
        resp = jsonify(result)
        resp.status_code = 200
        # 全部有多少個
        X_Total_Count = pageobj.paginator.count
        resp.headers['Access-Control-Expose-Headers'] = 'X-Total-Count'
        resp.headers.add('X-Total-Count', X_Total_Count)
        return resp

    except Exception as e:
        print("Failed to get all user")
        print(e)
        result = {'data': 'notok'}
        return jsonify(result), 500

設定mat-paginator的提示文字

可以在src\app\dashboard\user-page\user-page.component.ts裏的<mat-aginator>中的文字內容都是英文的包含上一頁及下一頁按鈕，當滑鼠移過去時會呈現一個tooltip。當然這個文字我們也可以進行調整，只要在MatPaginatorIntl這個service裡面設定即可：

ngOnInit() {
    this.getDBInfos();

    // 分頁切換時，重新取得資料
    this.paginator.page.subscribe((page: PageEvent) => {
      //console.log(`pageIndex=${page.pageIndex}:pageSize=${page.pageSize}`);
      this.getPages(page.pageIndex, page.pageSize);
    });
    // 設定顯示筆數資訊文字
    this.matPaginatorIntl.getRangeLabel = (page: number, pageSize: number, length: number): string => {
      if (length === 0 || pageSize === 0) {
        return `第 0 筆、共  ${length} 筆`;
      }
      length = Math.max(length, 0);
      const startIndex = page * pageSize;
      const ednIndex = startIndex < length ? Math.min(startIndex + pageSize, length) : startIndex + pageSize;
      return `第 ${startIndex + 1} - ${ednIndex} 筆、共  ${length} 筆`
    };
    // 設定其他顯示資訊文字
    this.matPaginatorIntl.itemsPerPageLabel = '每頁筆數：';
    this.matPaginatorIntl.nextPageLabel = '下一頁';
    this.matPaginatorIntl.previousPageLabel = '上一頁';
  }

參考資料

CORS Access-Control-Expose-Headers

sqlalchemy-paginator

Implementing a RESTful Web API with Python & Flask

How to use HttpClient send a form data request?

How to remove a key from a Python dictionary?

Flask SQLAlchemy setup dynamic URI

使用flask-sqlalchemy玩转MySQL

Python SQLAlchemy ORM - 1
Python SQLAlchemy ORM - 2
Python SQLAlchemy ORM - 3

关于flask-SQLAlchemy的初级使用教程

Sqlalchemy db.create_all() not creating tables

Using multiple database with same model in flask-sqlalchemy

Building a CRUD application with Flask and SQLAlchemy

前言

前言

前言

前言

前言

使用Angular

前言

前言

前言

前言

先來建立單一網頁CRUD

建立靜態網頁

可以處理使用者的輸入

建立一個輸入的form

處理拿到使用者輸入的資料

加入資料到資料庫

加入ORM模型

讀取資料庫的資料

Update更新資料

Delete刪除資料

最後的碼和html

建立使用多資料庫的方式來建立(有兩種)

declarative_base方法(不使用)

建立資料表

db.Model的方法

建立資料表

建立測試三個資料庫的資料-靜態

先來建立單一資料庫的網頁

建立靜態網頁

處理拿到使用者輸入的資料

Create資料

Update更新資料

以下的方法是codereview的做法

先來建立資料表

來測試單元測試三個資料庫的CRUD的功能

建立網頁來測試存取多個資料庫

建立測試三個資料庫的資料-動態

1. 以sqlalchemy的功能來建立

單元測試三個資料庫的CRUD的功能

建立網頁來存取多個資料庫

先來建立和取得所有的列表

加入更新資料

加入刪除資料

2 .以flask_sqlalchemy的功能來建立

單元測試三個資料庫的CRUD的功能

建立網頁來存取多個資料庫

先來建立和取得所有的列表

加入更新資料

加入刪除資料

以Angular來測試一下多個資料庫

建立基本後台

加入和刪除資料庫的連結

建立基本的前台UI介面

建立基本的Layout

加入通知訊息

打造基本後台UI

在加入處理資料庫的頁面

產生加入資料庫的Dialog

加入刪除資料庫的Dialog

加入處理使用者頁面

產生加入使用者的Dialog

加入刪除使用者的Dialog

加入修改使用者

加入分頁

設定mat-paginator的提示文字

參考資料