[Python] Flask 實作：專案管理系統

程式語言：Python
Package：
flask
flask_script
flask_sqlalchemy
flask_admin
flask_login
pandas
GitHub 原始碼，可搭配歷史變更互相參照

簡介：簡單的專案管理網站，包含後台管理


初步配置 flask & flask_script 
Project-Control-Web
│  basic_config.py     (new)
│  manage.py           (new)
│
├─instance            (new)
│      debug_config.py (new)
│
└─pcf                 (new)
    │  main.py         (new)
    │  views.py        (new)
    │  __init__.py     (new)
    │
    ├─static          (new)
    │      style.css   (new)
    │
    └─templates       (new)
            index.html  (new)
            layout.html (new)
首先，在 manage.py 定義 cli 會用到的指令 
  1. '''
  2. manage.py
  3. '''
  4. from flask_script import Manager
  5. from pcf import app
  6.  
  7. manager = Manager(app)
  8.  
  9.  
  10. @manager.command
  11. def initDB():
  12.     """初始化資料庫"""
  13.     pass
  14.  
  15. if __name__ == '__main__':
  16.     manager.run()
在 main.py 中，初始化 app，並利用 register_blueprint 控管 view 路徑 
  1. '''
  2. main.py
  3. '''
  4. from flask import Flask
  5.  
  6. # instance_relative_config 設定有 instance 資料夾的存在，預設路徑 ../instance
  7. app = Flask(__name__, instance_relative_config=True)
  8. # 基本設定，來自於上層的 config.py
  9. app.config.from_object("basic_config")
  10.  
  11. # 來自於 instance 中的 config.py，直接覆蓋之前的設定
  12. # 路徑可用 app.instance_path 得知
  13. # debug 設定，不用時可註解
  14. app.config.from_pyfile('debug_config.py')
  15. # 產品設定，來自於環境變數所提供的路徑
  16. app.config.from_envvar('FLASKR_SETTINGS', silent=True)
  17.  
  18. # 因為 .views 中有用到 app，所以只能把 .views 往後擺
  19. from .views import pcf
  20.  
  21. app.register_blueprint(pcf)
  22.  
  23.  
  24. @app.before_request
  25. def before_request():
  26.     """在 request 前做的事"""
  27.     pass
各個設定檔 
  1. '''
  2. basic_config.py
  3. '''
  4. # debug 模式
  5. DEBUG = False
  1. '''
  2. debug_config.py
  3. '''
  4. # debug 模式
  5. DEBUG = True
讓 manage.py 可以看得到 app，所以需建立一個 __init__.py 
  1. '''
  2. __init__.py
  3. '''
  4. from .main import app
基本 view 的功能 
  1. '''
  2. views.py
  3. '''
  4. from flask import render_template, Blueprint
  5. from .main import app
  6.  
  7. pcf = Blueprint('pcf', __name__)
  8.  
  9.  
  10. @pcf.route('/', methods=["GET"])
  11. def index():
  12.     return render_template('index.html', title="總覽")
網頁程式碼
基本框架 layout.html 
  1. <!doctype html>
  2.  
  3. <head>
  4.     <meta charset="utf-8" />
  5.     <meta http-equiv="X-UA-Compatible" content="IE=edge" />
  6.     <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  7.     <meta name="description" content="Project Controler" />
  8.     <meta name="author" content="子風" />
  9.     <title>{{title}}</title>
  10.     <!-- Bootstrap -->
  11.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
  12.         crossorigin="anonymous">
  13.     <!-- custom -->
  14.     <link rel=stylesheet type=text/css href="{{ url_for('static', filename='style.css') }}">
  15. </head>
  16.  
  17. <body>
  18.     <div class="container">
  19.         <nav class="navbar fixed-top navbar-expand-lg navbar-dark bg-dark">
  20.             <a class="navbar-brand" href="{{ url_for('pcf.index') }}">首頁</a>
  21.         </nav>
  22.     </div>
  23.     <div class="container">
  24.         {% for message in get_flashed_messages() %}
  25.         <div class="alert alert-primary" role="alert">{{ message }}</div>
  26.         {% endfor %}
  27.     </div>
  28.     {% block body %}{% endblock %}
  29.     {% block script %}{% endblock %}
  30. </body>
首頁 index.html 
  1. {% extends "layout.html" %}
  2. {% block body %}
  3. <div>
  4.     <div class="flexbox-table table-dark">
  5.         <div class='thead'>
  6.             <div class='tr'>
  7.                 <div class='th' style="flex: 2;">JIRA</div>
  8.                 <div class='th' style="flex: 2;">Model Name</div>
  9.                 <div class='th' style="flex: 2;">Customer<br/>Model Name</div>
  10.                 <div class='th' style="flex: 2;">Product ID</div>
  11.                 <div class='th' style="flex: 1;">Project Code</div>
  12.                 <div class='th' style="flex: 1;">Stage</div>
  13.                 <div class='th' style="flex: 1;">EE Owner</div>
  14.                 <div class='th' style="flex: 1;">PM</div>
  15.                 <div class='th' style="flex: 1;">APM</div>
  16.                 <div class='th' style="flex: 1;">Customer</div>
  17.                 <div class='th' style="flex: 1;">Model Name Code</div>
  18.                 <div class='th' style="flex: 4;">Remark</div>
  19.             </div>
  20.         </div>
  21.     </div>
  22. </div>
  23. {% endblock %}
style.css 利用 flex 構建出 table 
  1. body {
  2.     /* 避免被導覽列蓋住 */
  3.     padding-top: 65px;
  4.     background-color: #AAAAAA;
  5. }
  6.  
  7. td,
  8. th {
  9.     min-width: 90px;
  10.     text-align: center;
  11. }
  12.  
  13. /* 定義 div table */
  14.  
  15. .flexbox-table {}
  16.  
  17. .flexbox-table .thead {}
  18.  
  19. .flexbox-table .tbody {}
  20.  
  21. .flexbox-table .tr {
  22.     display: flex;
  23.     flex-flow: row nowrap;
  24.     justify-content: space-around;
  25.     width: 100%;
  26. }
  27.  
  28. .flexbox-table .tbody .tr:hover {
  29.     background-color: #343a40;
  30. }
  31.  
  32. .flexbox-table .th {
  33.     font-weight: bold;
  34. }
  35.  
  36. .flexbox-table .th,
  37. .flexbox-table .td {
  38.     flex: 2 0 0;
  39.     text-align: center;
  40.     width: 100%;
  41.     border: 1px solid #32383e;
  42. }
  43.  
  44. .flexbox-form {
  45.     display: flex;
  46.     flex-flow: row wrap;
  47.     justify-content: flex-start;
  48. }

建立資料庫 flask_sqlalchemy 
Project-Control-Web        
│  basic_config.py       (modify) 
│  manage.py             (modify)                      
│                       
├─instance              
│      debug_config.py  
│      product_config.py (new)
│                       
└─pcf                   
    │  main.py          
    │  models.py         (new)       
    │  views.py          (modify)        
    │  __init__.py       (modify)      
    │                   
    ├─static            
    │      style.css    
    │                   
    └─templates         
            index.html  
            layout.html
在 manage.py 加入初始化資料庫的程式碼 
  1. '''
  2. manage.py
  3. '''
  4. from flask_script import Manager, prompt_bool
  5. from pcf import app, db
  6.  
  7. manager = Manager(app)
  8.  
  9.  
  10. @manager.command
  11. def initDB():
  12.     """初始化資料庫"""
  13.     if prompt_bool("將失去所有資料，確定嗎？"):
  14.         db.drop_all()
  15.         db.create_all()
  16.  
  17. if __name__ == '__main__':
  18.     manager.run()
在 main.py 中加入 db 的宣告，在此使用的 ORM 是 SQLAlchemy 
  1. '''
  2. main.py
  3. '''
  4. from flask import Flask
  5. from flask_sqlalchemy import SQLAlchemy
  6.  
  7. # instance_relative_config 設定有 instance 資料夾的存在，預設路徑 ../instance
  8. app = Flask(__name__, instance_relative_config=True)
  9. # 基本設定，來自於上層的 config.py
  10. app.config.from_object("basic_config")
  11.  
  12. # 來自於 instance 中的 config.py，直接覆蓋之前的設定
  13. # 路徑可用 app.instance_path 得知
  14. # debug 設定，不用時可註解
  15. app.config.from_pyfile('debug_config.py')
  16. # product 設定
  17. app.config.from_pyfile('product_config.py')
  18. # 產品設定，來自於環境變數所提供的路徑
  19. app.config.from_envvar('FLASKR_SETTINGS', silent=True)
  20.  
  21. # 資料庫宣告
  22. # 資料庫路徑 app.config['SQLALCHEMY_DATABASE_URI']
  23. db = SQLAlchemy(app)
  24.  
  25. # 因為 .views 中有用到 app，所以只能把 .views 往後擺
  26. from .views import pcf
  27.  
  28. app.register_blueprint(pcf)
  29.  
  30.  
  31. @app.before_request
  32. def before_request():
  33.     """在 request 前做的事"""
  34.     pass
在各個設定檔加入 SQLAlchemy 的設定 
  1. '''
  2. basic_config.py
  3. '''
  4. # debug 模式
  5. DEBUG = False
  6. # 顯示 sql 查詢語句
  7. SQLALCHEMY_ECHO = False
  8. # SQLAlchemy 將會追蹤對象的修改並且發送信號。這需要額外的內存，如果不必要的可以禁用它。
  9. SQLALCHEMY_TRACK_MODIFICATIONS = False
  1. '''
  2. product_config.py
  3. '''
  4. import os
  5.  
  6. appFolder = os.path.dirname(__file__)
  7.  
  8. # 資料庫的路徑
  9. SQLALCHEMY_DATABASE_URI = 'sqlite:///' \
  10.                         + os.path.join(appFolder, r"../", 'data.db')
讓 manage.py 看得到 db，故在 __init__.py 加入 db 
  1. '''
  2. __init__.py
  3. '''
  4. from .main import app, db
建立 models.py 定義資料的架構，綠色線表示 many to many，藍色線表示 one to many 
  1. '''
  2. models.py
  3. '''
  4. import datetime
  5. from flask_sqlalchemy import SQLAlchemy
  6.  
  7. from .main import db
  8.  
  9. productIDs_components = db.Table('productIDs_components', db.Model.metadata,
  10.                                  db.Column('productIDs_id', db.Integer,
  11.                                            db.ForeignKey('productIDs.id')),
  12.                                  db.Column('components_id', db.Integer,
  13.                                            db.ForeignKey('components.id')))
  14.  
  15. productIDs_documents = db.Table('productIDs_documents', db.Model.metadata,
  16.                                 db.Column('productIDs_id', db.Integer,
  17.                                           db.ForeignKey('productIDs.id')),
  18.                                 db.Column('documents_id', db.Integer,
  19.                                           db.ForeignKey('documents.id')))
  20.  
  21. productIDs_histories = db.Table('productIDs_histories', db.Model.metadata,
  22.                                 db.Column('productIDs_id', db.Integer,
  23.                                           db.ForeignKey('productIDs.id')),
  24.                                 db.Column('histories_id', db.Integer,
  25.                                           db.ForeignKey('histories.id')))
  26.  
  27. documents_histories = db.Table('documents_histories', db.Model.metadata,
  28.                                db.Column('documents_id', db.Integer,
  29.                                          db.ForeignKey('documents.id')),
  30.                                db.Column('histories_id', db.Integer,
  31.                                          db.ForeignKey('histories.id')))
  32.  
  33.  
  34. class Models(db.Model):
  35.     # 若不寫則看 class name
  36.     __tablename__ = 'models'
  37.     # 設定 primary_key
  38.     id = db.Column(
  39.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  40.     JIRA = db.Column(db.String(20))
  41.     name = db.Column(db.String(20), nullable=False)
  42.     projectCode = db.Column(db.String(20))
  43.     stage = db.Column(db.String(5), nullable=False)
  44.     PM = db.Column(db.String(4), nullable=False)
  45.     APM = db.Column(db.String(4), nullable=False)
  46.     modelNameCode = db.Column(db.String(20))
  47.  
  48.     remark = db.Column(db.Text)
  49.  
  50.     owner_id = db.Column(
  51.         db.Integer, db.ForeignKey('owners.id'), nullable=False)
  52.     owner = db.relationship(
  53.         "Owners", back_populates="models", foreign_keys=[owner_id])
  54.  
  55.     productIDs = db.relationship(
  56.         "ProductIDs",
  57.         back_populates="model",
  58.         foreign_keys='ProductIDs.model_id')
  59.  
  60.     def __repr__(self):
  61.         return ''.format(self.name)
  64. class Owners(db.Model):
  65.     # 若不寫則看 class name
  66.     __tablename__ = 'owners'
  67.     # 設定 primary_key
  68.     id = db.Column(
  69.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  70.     name = db.Column(db.String(10), nullable=False)
  72.     models = db.relationship(
  73.         "Models", back_populates="owner", foreign_keys='Models.owner_id')
  75.     def __repr__(self):
  76.         return ''.format(self.name)
  79. class ProductIDs(db.Model):
  80.     # 若不寫則看 class name
  81.     __tablename__ = 'productIDs'
  82.     # 設定 primary_key
  83.     id = db.Column(
  84.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  85.     name = db.Column(db.Text, nullable=False, unique=True)
  86.     customer = db.Column(db.String(20), nullable=False)
  87.     customerModelName = db.Column(db.String(20))
  89.     model_id = db.Column(
  90.         db.Integer, db.ForeignKey('models.id'), nullable=False)
  91.     model = db.relationship(
  92.         "Models", back_populates="productIDs", foreign_keys=[model_id])
  94.     components = db.relationship(
  95.         "Components",
  96.         secondary=productIDs_components,
  97.         back_populates="productIDs")
  98.     documents = db.relationship(
  99.         "Documents",
  100.         secondary=productIDs_documents,
  101.         back_populates="productIDs")
  102.     histories = db.relationship(
  103.         "Histories",
  104.         secondary=productIDs_histories,
  105.         back_populates="productIDs")
  107.     def __repr__(self):
  108.         return ''.format(self.name)
  111. class Components(db.Model):
  112.     # 若不寫則看 class name
  113.     __tablename__ = 'components'
  114.     # 設定 primary_key
  115.     id = db.Column(
  116.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  117.     PN_number = db.Column(db.String(30), unique=True)
  118.     name = db.Column(db.String(30), nullable=False)
  119.     type = db.Column(db.String(30), nullable=False)
  120.     vendor = db.Column(db.String(30))
  121.     remark = db.Column(db.Text)
  123.     productIDs = db.relationship(
  124.         "ProductIDs",
  125.         secondary=productIDs_components,
  126.         back_populates="components")
  128.     def __repr__(self):
  129.         return ''.format(self.PN_number, self.name,
  130.                                               self.vendor)
  133. class Documents(db.Model):
  134.     # 若不寫則看 class name
  135.     __tablename__ = 'documents'
  136.     # 設定 primary_key
  137.     id = db.Column(
  138.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  139.     DN_number = db.Column(db.String(9), nullable=False, unique=True)
  140.     type = db.Column(db.String(30), nullable=False)
  141.     stage = db.Column(db.String(10))
  142.     remark = db.Column(db.Text)
  144.     productIDs = db.relationship(
  145.         "ProductIDs",
  146.         secondary=productIDs_documents,
  147.         back_populates="documents")
  148.     histories = db.relationship(
  149.         "Histories", secondary=documents_histories, back_populates="documents")
  151.     def __repr__(self):
  152.         return ''.format(self.DN_number, self.type,
  153.                                              self.remark)
  156. class Histories(db.Model):
  157.     # 若不寫則看 class name
  158.     __tablename__ = 'histories'
  159.     # 設定 primary_key
  160.     id = db.Column(
  161.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  162.     date = db.Column(db.Date, default=datetime.datetime.now(), nullable=False)
  163.     type = db.Column(db.String(30), nullable=False)
  164.     PCBAs = db.Column(db.Text, nullable=False)
  165.     PCBA_version = db.Column(db.Integer, nullable=False)
  166.     circuitVersion = db.Column(db.Integer, nullable=False)
  167.     PCBs = db.Column(db.Text)
  168.     PCB_version = db.Column(db.Integer)
  169.     remark = db.Column(db.Text)
  171.     productIDs = db.relationship(
  172.         "ProductIDs",
  173.         secondary=productIDs_histories,
  174.         back_populates="histories")
  175.     documents = db.relationship(
  176.         "Documents", secondary=documents_histories, back_populates="histories")
  178.     def __repr__(self):
  179.         return ''.format(self.date, self.PCBAs,
  180.                                              self.remark)
並在 views.py import models 以供在 view 中使用 
  1. '''
  2. views.py
  3. '''
  4. from flask import render_template, Blueprint
  5. from .main import app
  6. from . import models
  7.  
  8. pcf = Blueprint('pcf', __name__)
  9.  
  10.  
  11. @pcf.route('/', methods=["GET"])
  12. def index():
  13.     return render_template('index.html', title="總覽")
初始化資料庫是必要的，除非已存在 data.db 
python manage.py initDB

建立後台管理 flask_admin 
Project-Control-Web
│  basic_config.py
│  data.db
│  manage.py
│
├─instance
│      debug_config.py
│      product_config.py  (modify) 
│
└─pcf
    │  adminView.py       (new)
    │  main.py
    │  models.py
    │  views.py           (modify) 
    │  __init__.py
    │
    ├─static
    │      style.css
    │
    └─templates
        │  index.html
        │  layout.html    (modify) 
        │
        └─admin
                index.html (new)
設定檔加入 SECRET_KEY ，讓內部資料能互相傳遞
  1. '''
  2. product_config.py
  3. '''
  4. import os
  5.  
  6. appFolder = os.path.dirname(__file__)
  7.  
  8. # 資料庫的路徑
  9. SQLALCHEMY_DATABASE_URI = 'sqlite:///' \
  10.                         + os.path.join(appFolder, r"../", 'data.db')
  11.  
  12. # 用 os.urandom(24) 產生一組即可
  13. SECRET_KEY = b'j\x92\xedV\xab~\xd1U#\xef\x9cp\xb0\x90\x1c]\x99\xd6\xd1\xf94\x8f\xb1\xe7'
新增 adminView.py，建立後台管理
  1. '''
  2. adminView.py
  3. '''
  4. from flask_admin import Admin
  5.  
  6. from .main import app
  7.  
  8. # Create admin
  9. admin = Admin(app, 'PCF', template_mode='bootstrap3')
在 views.py 加入希望顯示的 models 
  1. '''
  2. views.py
  3. '''
  4. from flask import render_template, Blueprint
  5. from flask_admin.contrib.sqla import ModelView
  6. from flask_admin.form import SecureForm
  7.  
  8. from .main import app, db
  9. from . import models
  10. from .adminView import admin
  11.  
  12. pcf = Blueprint('pcf', __name__)
  13.  
  14. # 定義一基本的設定，可適用於其他的 modelview
  15. class BaseModelView(ModelView):
  16.     # 可用 Ajax 建立 models
  17.     create_modal = False
  18.     # 可用 Ajax 修改 models
  19.     edit_modal = False
  20.     # 可 export 資料
  21.     can_export = True
  22.     # 可看詳細資料，但會看到密碼
  23.     # can_view_details = True
  24.     # 加入 CSRF 驗證
  25.     form_base_class = SecureForm
  26.     # 顯示所有 one to many 和 many to many 的資料
  27.     column_display_all_relations = True
  28.     # 一頁顯示的個數
  29.     page_size = 50
  30.  
  31.  
  32. class ModelsView(BaseModelView):
  33.     # 想顯示的資料
  34.     column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
  35.                    'modelNameCode', 'remark', 'owner', 'productIDs')
  36.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  37.     inline_models = (models.ProductIDs, )
  38.  
  39.     def __init__(self, session, **kwargs):
  40.         super().__init__(models.Models, session, **kwargs)
  41.  
  42.  
  43. admin.add_view(ModelsView(db.session))
  44.  
  45.  
  46. class ProductIDsView(BaseModelView):
  47.     # 想顯示的資料
  48.     column_list = ('name', 'model', 'customer', 'customerModelName',
  49.                    'components', 'documents', 'histories')
  50.     # 欲直接搜尋的資料
  51.     column_searchable_list = ('name', 'customerModelName')
  52.     # 欲篩選的資料
  53.     column_filters = ('model', 'customer', 'components')
  54.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  55.     inline_models = (models.Components, models.Histories)
  56.  
  57.     def __init__(self, session, **kwargs):
  58.         super().__init__(models.ProductIDs, session, **kwargs)
  59.  
  60.  
  61. admin.add_view(ProductIDsView(db.session))
  62.  
  63.  
  64. class ComponentsView(BaseModelView):
  65.     # 想顯示的資料
  66.     column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
  67.                    'productIDs')
  68.     # 欲直接搜尋的資料
  69.     column_searchable_list = (models.Components.PN_number,
  70.                               models.Components.name)
  71.     # 欲篩選的資料
  72.     column_filters = ('type', 'vendor')
  73.     # 預設排列
  74.     column_default_sort = 'type'
  75.  
  76.     def __init__(self, session, **kwargs):
  77.         super().__init__(models.Components, session, **kwargs)
  78.  
  79.  
  80. admin.add_view(ComponentsView(db.session))
  81.  
  82.  
  83. class HistoriesView(BaseModelView):
  84.     # 想顯示的資料
  85.     column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
  86.                    'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
  87.     # 欲直接搜尋的資料
  88.     column_searchable_list = ('PCBAs', )
  89.     # 欲篩選的資料
  90.     column_filters = ('type', )
  91.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  92.     inline_models = (models.Documents, )
  93.  
  94.     def __init__(self, session, **kwargs):
  95.         super().__init__(models.Histories, session, **kwargs)
  96.  
  97.  
  98. admin.add_view(HistoriesView(db.session))
  99.  
  100.  
  101. class DocumentsView(BaseModelView):
  102.     # 想顯示的資料
  103.     column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
  104.                    'histories')
  105.     # 欲直接搜尋的資料
  106.     column_searchable_list = ('DN_number', )
  107.     # 欲篩選的資料
  108.     column_filters = ('type', 'stage')
  109.     # 預設排列
  110.     column_default_sort = 'type'
  111.  
  112.     def __init__(self, session, **kwargs):
  113.         super().__init__(models.Documents, session, **kwargs)
  114.  
  115.  
  116. admin.add_view(DocumentsView(db.session))
  117.  
  118.  
  119. class OwnersView(BaseModelView):
  120.     # 想顯示的資料
  121.     column_list = ('name', 'models')
  122.  
  123.     def __init__(self, session, **kwargs):
  124.         super().__init__(models.Owners, session, **kwargs)
  125.  
  126.  
  127. admin.add_view(OwnersView(db.session))
  128.  
  129.  
  130. @pcf.route('/', methods=["GET"])
  131. def index():
  132.     return render_template('index.html', title="總覽")
在 layout.html 加入後台管理的連結 
  1. <!doctype html>
  2.  
  3. <head>
  4.     <meta charset="utf-8" />
  5.     <meta http-equiv="X-UA-Compatible" content="IE=edge" />
  6.     <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  7.     <meta name="description" content="Project Controler" />
  8.     <meta name="author" content="子風" />
  9.     <title>{{title}}</title>
  10.     <!-- Bootstrap -->
  11.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
  12.         crossorigin="anonymous">
  13.     <!-- custom -->
  14.     <link rel=stylesheet type=text/css href="{{ url_for('static', filename='style.css') }}">
  15. </head>
  16.  
  17. <body>
  18.     <div class="container">
  19.         <nav class="navbar fixed-top navbar-expand-lg navbar-dark bg-dark">
  20.             <a class="navbar-brand" href="{{ url_for('pcf.index') }}">首頁</a>
  21.             <div class="collapse navbar-collapse" id="navbarNavDropdown">
  22.                 <ul class="navbar-nav">
  23.                     <a class="nav-link" href="{{ url_for('admin.index') }}">Admin</a>
  24.                 </ul>
  25.             </div>
  26.         </nav>
  27.     </div>
  28.     <div class="container">
  29.         {% for message in get_flashed_messages() %}
  30.         <div class="alert alert-primary" role="alert">{{ message }}</div>
  31.         {% endfor %}
  32.     </div>
  33.     {% block body %}{% endblock %}
  34.     {% block script %}{% endblock %}
  35. </body>
在 templates 中 新建 admin 資料夾，並加入 index.html 後台管理的首頁 
  1. {% extends 'admin/master.html' %} 
  2. {% block body %} 
  3. {{ super() }}
  4. <div class="row-fluid">
  5.     <div>
  6.         <p class="lead">
  7.             後台管理
  8.         </p>
  9.         <p>
  10.             Project Control File
  11.             <br/>
  12.         </p>
  13.     </div>
  14.     <a class="btn btn-primary" href="{{ url_for('pcf.index') }}">
  15.         <i class="icon-arrow-left icon-white"></i> 回到首頁</a>
  16. </div>
  17. {% endblock body %}

使用者登入 flask_login 
Project-Control-Web
│  basic_config.py
│  data.db
│  manage.py                 (modify) 
│
├─instance
│      debug_config.py
│      product_config.py
│
└─pcf
    │  adminView.py          (modify) 
    │  main.py
    │  models.py             (modify) 
    │  views.py              (modify) 
    │  __init__.py
    │
    ├─static
    │      style.css
    │
    └─templates
        │  index.html
        │  layout.html
        │
        └─admin
                dropdown.html (new)
                index.html    (modify)
models.py 加入 Users 
  1. '''
  2. models.py
  3. '''
  4. import datetime
  5. from flask_sqlalchemy import SQLAlchemy
  6.  
  7. from .main import db
  8.  
  9. productIDs_components = db.Table('productIDs_components', db.Model.metadata,
  10.                                  db.Column('productIDs_id', db.Integer,
  11.                                            db.ForeignKey('productIDs.id')),
  12.                                  db.Column('components_id', db.Integer,
  13.                                            db.ForeignKey('components.id')))
  14.  
  15. productIDs_documents = db.Table('productIDs_documents', db.Model.metadata,
  16.                                 db.Column('productIDs_id', db.Integer,
  17.                                           db.ForeignKey('productIDs.id')),
  18.                                 db.Column('documents_id', db.Integer,
  19.                                           db.ForeignKey('documents.id')))
  20.  
  21. productIDs_histories = db.Table('productIDs_histories', db.Model.metadata,
  22.                                 db.Column('productIDs_id', db.Integer,
  23.                                           db.ForeignKey('productIDs.id')),
  24.                                 db.Column('histories_id', db.Integer,
  25.                                           db.ForeignKey('histories.id')))
  26.  
  27. documents_histories = db.Table('documents_histories', db.Model.metadata,
  28.                                db.Column('documents_id', db.Integer,
  29.                                          db.ForeignKey('documents.id')),
  30.                                db.Column('histories_id', db.Integer,
  31.                                          db.ForeignKey('histories.id')))
  32.  
  33.  
  34. class Models(db.Model):
  35.     # 若不寫則看 class name
  36.     __tablename__ = 'models'
  37.     # 設定 primary_key
  38.     id = db.Column(
  39.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  40.     JIRA = db.Column(db.String(20))
  41.     name = db.Column(db.String(20), nullable=False)
  42.     projectCode = db.Column(db.String(20))
  43.     stage = db.Column(db.String(5), nullable=False)
  44.     PM = db.Column(db.String(4), nullable=False)
  45.     APM = db.Column(db.String(4), nullable=False)
  46.     modelNameCode = db.Column(db.String(20))
  47.  
  48.     remark = db.Column(db.Text)
  49.  
  50.     owner_id = db.Column(
  51.         db.Integer, db.ForeignKey('owners.id'), nullable=False)
  52.     owner = db.relationship(
  53.         "Owners", back_populates="models", foreign_keys=[owner_id])
  54.  
  55.     productIDs = db.relationship(
  56.         "ProductIDs",
  57.         back_populates="model",
  58.         foreign_keys='ProductIDs.model_id')
  59.  
  60.     def __repr__(self):
  61.         return ''.format(self.name)
  64. class Owners(db.Model):
  65.     # 若不寫則看 class name
  66.     __tablename__ = 'owners'
  67.     # 設定 primary_key
  68.     id = db.Column(
  69.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  70.     name = db.Column(db.String(10), nullable=False)
  72.     models = db.relationship(
  73.         "Models", back_populates="owner", foreign_keys='Models.owner_id')
  75.     def __repr__(self):
  76.         return ''.format(self.name)
  79. class ProductIDs(db.Model):
  80.     # 若不寫則看 class name
  81.     __tablename__ = 'productIDs'
  82.     # 設定 primary_key
  83.     id = db.Column(
  84.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  85.     name = db.Column(db.Text, nullable=False, unique=True)
  86.     customer = db.Column(db.String(20), nullable=False)
  87.     customerModelName = db.Column(db.String(20))
  89.     model_id = db.Column(
  90.         db.Integer, db.ForeignKey('models.id'), nullable=False)
  91.     model = db.relationship(
  92.         "Models", back_populates="productIDs", foreign_keys=[model_id])
  94.     components = db.relationship(
  95.         "Components",
  96.         secondary=productIDs_components,
  97.         back_populates="productIDs")
  98.     documents = db.relationship(
  99.         "Documents",
  100.         secondary=productIDs_documents,
  101.         back_populates="productIDs")
  102.     histories = db.relationship(
  103.         "Histories",
  104.         secondary=productIDs_histories,
  105.         back_populates="productIDs")
  107.     def __repr__(self):
  108.         return ''.format(self.name)
  111. class Components(db.Model):
  112.     # 若不寫則看 class name
  113.     __tablename__ = 'components'
  114.     # 設定 primary_key
  115.     id = db.Column(
  116.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  117.     PN_number = db.Column(db.String(30), unique=True)
  118.     name = db.Column(db.String(30), nullable=False)
  119.     type = db.Column(db.String(30), nullable=False)
  120.     vendor = db.Column(db.String(30))
  121.     remark = db.Column(db.Text)
  123.     productIDs = db.relationship(
  124.         "ProductIDs",
  125.         secondary=productIDs_components,
  126.         back_populates="components")
  128.     def __repr__(self):
  129.         return ''.format(self.PN_number, self.name,
  130.                                               self.vendor)
  133. class Documents(db.Model):
  134.     # 若不寫則看 class name
  135.     __tablename__ = 'documents'
  136.     # 設定 primary_key
  137.     id = db.Column(
  138.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  139.     DN_number = db.Column(db.String(9), nullable=False, unique=True)
  140.     type = db.Column(db.String(30), nullable=False)
  141.     stage = db.Column(db.String(10))
  142.     remark = db.Column(db.Text)
  144.     productIDs = db.relationship(
  145.         "ProductIDs",
  146.         secondary=productIDs_documents,
  147.         back_populates="documents")
  148.     histories = db.relationship(
  149.         "Histories", secondary=documents_histories, back_populates="documents")
  151.     def __repr__(self):
  152.         return ''.format(self.DN_number, self.type,
  153.                                              self.remark)
  156. class Histories(db.Model):
  157.     # 若不寫則看 class name
  158.     __tablename__ = 'histories'
  159.     # 設定 primary_key
  160.     id = db.Column(
  161.         db.Integer, primary_key=True, autoincrement=True, nullable=False)
  162.     date = db.Column(db.Date, default=datetime.datetime.now(), nullable=False)
  163.     type = db.Column(db.String(30), nullable=False)
  164.     PCBAs = db.Column(db.Text, nullable=False)
  165.     PCBA_version = db.Column(db.Integer, nullable=False)
  166.     circuitVersion = db.Column(db.Integer, nullable=False)
  167.     PCBs = db.Column(db.Text)
  168.     PCB_version = db.Column(db.Integer)
  169.     remark = db.Column(db.Text)
  171.     productIDs = db.relationship(
  172.         "ProductIDs",
  173.         secondary=productIDs_histories,
  174.         back_populates="histories")
  175.     documents = db.relationship(
  176.         "Documents", secondary=documents_histories, back_populates="histories")
  178.     def __repr__(self):
  179.         return ''.format(self.date, self.PCBAs,
  180.                                              self.remark)
  183. # Create user model.
  184. class Users(db.Model):
  185.     # 若不寫則看 class name
  186.     __tablename__ = 'users'
  187.     # 設定 primary_key
  188.     id = db.Column(db.Integer, primary_key=True)
  189.     name = db.Column(db.String(10))
  190.     userName = db.Column(db.String(80), unique=True)
  191.     email = db.Column(db.String(120))
  192.     password = db.Column(db.String(64))
  194.     # Flask-Login integration
  195.     def is_authenticated(self):
  196.         return True
  198.     def is_active(self):
  199.         return True
  201.     def is_anonymous(self):
  202.         return False
  204.     def get_id(self):
  205.         return self.id
  207.     def __repr__(self):
  208.         return ''.format(self.name, self.userName)
adminView.py 加入登入、驗證、註冊機制 
  1. '''
  2. adminView.py
  3. '''
  4. from flask import url_for, redirect, render_template, request
  5. from flask_admin import Admin, AdminIndexView, helpers, expose
  6. from wtforms import form, fields, validators
  7. from flask_login import LoginManager, current_user, login_user, logout_user
  8. from werkzeug.security import generate_password_hash, check_password_hash
  9.  
  10. from .main import app, db
  11. from . import models
  12.  
  13.  
  14. # Define login and registration forms (for flask-login)
  15. class LoginForm(form.Form):
  16.     userName = fields.TextField(validators=[validators.required()])
  17.     password = fields.PasswordField(validators=[validators.required()])
  18.  
  19.     def validate_login(self, field):
  20.         user = self.get_user()
  21.  
  22.         if user is None:
  23.             raise validators.ValidationError('Invalid user')
  24.  
  25.         # we're comparing the plaintext pw with the the hash from the db
  26.         if not check_password_hash(user.password, self.password.data):
  27.             # to compare plain text passwords use
  28.             # if user.password != self.password.data:
  29.             raise validators.ValidationError('Invalid password')
  30.  
  31.     def get_user(self):
  32.         return db.session.query(
  33.             models.Users).filter_by(userName=self.userName.data).first()
  34.  
  35.  
  36. class RegistrationForm(form.Form):
  37.     name = fields.TextField()
  38.     userName = fields.TextField(validators=[validators.required()])
  39.     email = fields.TextField()
  40.     password = fields.PasswordField('New Password', [
  41.         validators.DataRequired(),
  42.         validators.EqualTo('confirm', message='Passwords must match')
  43.     ])
  44.     confirm = fields.PasswordField('Repeat Password')
  45.  
  46.     def validate_login(self, field):
  47.         if db.session.query(models.Users).filter_by(
  48.                 userName=self.userName.data).count() > 0:
  49.             raise validators.ValidationError('Duplicate username')
  50.  
  51.  
  52. # Initialize flask-login
  53. def init_login():
  54.     login_manager = LoginManager()
  55.     login_manager.init_app(app)
  56.  
  57.     # Create user loader function
  58.     @login_manager.user_loader
  59.     def load_user(user_id):
  60.         return db.session.query(models.Users).get(user_id)
  61.  
  62.  
  63. # Create customized index view class that handles login & registration
  64. class MyAdminIndexView(AdminIndexView):
  65.     @expose('/')
  66.     def index(self):
  67.         if not current_user.is_authenticated:
  68.             return redirect(url_for('.login_view'))
  69.         return super(MyAdminIndexView, self).index()
  70.  
  71.     @expose('/login/', methods=('GET', 'POST'))
  72.     def login_view(self):
  73.         # handle user login
  74.         form = LoginForm(request.form)
  75.         if helpers.validate_form_on_submit(form):
  76.             user = form.get_user()
  77.             if user:
  78.                 login_user(user)
  79.  
  80.         if current_user.is_authenticated:
  81.             return redirect(url_for('.index'))
  82.         link = 'Don\'t have an account? Click here to register.
  83.  
  84. '
  85.         self._template_args['form'] = form
  86.         self._template_args['link'] = link
  87.         return super(MyAdminIndexView, self).index()
  88.  
  89.     @expose('/register/', methods=('GET', 'POST'))
  90.     def register_view(self):
  91.         form = RegistrationForm(request.form)
  92.         if helpers.validate_form_on_submit(form):
  93.             user = models.Users()
  94.  
  95.             form.populate_obj(user)
  96.             # we hash the users password to avoid saving it as plaintext in the db,
  97.             # remove to use plain text:
  98.             user.password = generate_password_hash(form.password.data)
  99.  
  100.             db.session.add(user)
  101.             db.session.commit()
  102.  
  103.             login_user(user)
  104.             return redirect(url_for('.index'))
  105.         link = 'Already have an account? Click here to log in.
  106.  
  107. '
  108.         self._template_args['form'] = form
  109.         self._template_args['link'] = link
  110.         return super(MyAdminIndexView, self).index()
  111.  
  112.     @expose('/logout/')
  113.     def logout_view(self):
  114.         logout_user()
  115.         return redirect(url_for('.index'))
  116.  
  117.  
  118. # Initialize flask-login
  119. init_login()
  120.  
  121. # Create admin
  122. admin = Admin(
  123.     app,
  124.     'PCF',
  125.     index_view=MyAdminIndexView(),
  126.     base_template='admin/dropdown.html',
  127.     template_mode='bootstrap3')
在 views.py 加入驗證機制，並增加 Users 顯示 
  1. '''
  2. views.py
  3. '''
  4. from flask import render_template, Blueprint
  5. from flask_admin.contrib.sqla import ModelView
  6. from flask_admin.form import SecureForm
  7. import flask_login
  8. from wtforms import PasswordField
  9. from werkzeug.security import generate_password_hash
  10.  
  11. from .main import app, db
  12. from . import models
  13. from .adminView import admin
  14.  
  15. pcf = Blueprint('pcf', __name__)
  16.  
  17.  
  18. # 定義一基本的設定，可適用於其他的 modelview
  19. class BaseModelView(ModelView):
  20.     # 可用 Ajax 建立 models
  21.     create_modal = False
  22.     # 可用 Ajax 修改 models
  23.     edit_modal = False
  24.     # 可 export 資料
  25.     can_export = True
  26.     # 可看詳細資料，但會看到密碼
  27.     # can_view_details = True
  28.     # 加入 CSRF 驗證
  29.     form_base_class = SecureForm
  30.     # 顯示所有 one to many 和 many to many 的資料
  31.     column_display_all_relations = True
  32.     # 一頁顯示的個數
  33.     page_size = 50
  34.  
  35.     def is_accessible(self):
  36.         return flask_login.current_user.is_authenticated
  37.  
  38.  
  39. class ModelsView(BaseModelView):
  40.     # 想顯示的資料
  41.     column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
  42.                    'modelNameCode', 'remark', 'owner', 'productIDs')
  43.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  44.     inline_models = (models.ProductIDs, )
  45.  
  46.     def __init__(self, session, **kwargs):
  47.         super().__init__(models.Models, session, **kwargs)
  48.  
  49.  
  50. admin.add_view(ModelsView(db.session))
  51.  
  52.  
  53. class ProductIDsView(BaseModelView):
  54.     # 想顯示的資料
  55.     column_list = ('name', 'model', 'customer', 'customerModelName',
  56.                    'components', 'documents', 'histories')
  57.     # 欲直接搜尋的資料
  58.     column_searchable_list = ('name', 'customerModelName')
  59.     # 欲篩選的資料
  60.     column_filters = ('model', 'customer', 'components')
  61.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  62.     inline_models = (models.Components, models.Histories)
  63.  
  64.     def __init__(self, session, **kwargs):
  65.         super().__init__(models.ProductIDs, session, **kwargs)
  66.  
  67.  
  68. admin.add_view(ProductIDsView(db.session))
  69.  
  70.  
  71. class ComponentsView(BaseModelView):
  72.     # 想顯示的資料
  73.     column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
  74.                    'productIDs')
  75.     # 欲直接搜尋的資料
  76.     column_searchable_list = (models.Components.PN_number,
  77.                               models.Components.name)
  78.     # 欲篩選的資料
  79.     column_filters = ('type', 'vendor')
  80.     # 預設排列
  81.     column_default_sort = 'type'
  82.  
  83.     def __init__(self, session, **kwargs):
  84.         super().__init__(models.Components, session, **kwargs)
  85.  
  86.  
  87. admin.add_view(ComponentsView(db.session))
  88.  
  89.  
  90. class HistoriesView(BaseModelView):
  91.     # 想顯示的資料
  92.     column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
  93.                    'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
  94.     # 欲直接搜尋的資料
  95.     column_searchable_list = ('PCBAs', )
  96.     # 欲篩選的資料
  97.     column_filters = ('type', )
  98.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  99.     inline_models = (models.Documents, )
  100.  
  101.     def __init__(self, session, **kwargs):
  102.         super().__init__(models.Histories, session, **kwargs)
  103.  
  104.  
  105. admin.add_view(HistoriesView(db.session))
  106.  
  107.  
  108. class DocumentsView(BaseModelView):
  109.     # 想顯示的資料
  110.     column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
  111.                    'histories')
  112.     # 欲直接搜尋的資料
  113.     column_searchable_list = ('DN_number', )
  114.     # 欲篩選的資料
  115.     column_filters = ('type', 'stage')
  116.     # 預設排列
  117.     column_default_sort = 'type'
  118.  
  119.     def __init__(self, session, **kwargs):
  120.         super().__init__(models.Documents, session, **kwargs)
  121.  
  122.  
  123. admin.add_view(DocumentsView(db.session))
  124.  
  125.  
  126. class OwnersView(BaseModelView):
  127.     # 想顯示的資料
  128.     column_list = ('name', 'models')
  129.  
  130.     def __init__(self, session, **kwargs):
  131.         super().__init__(models.Owners, session, **kwargs)
  132.  
  133.  
  134. admin.add_view(OwnersView(db.session))
  135.  
  136.  
  137. class UsersView(BaseModelView):
  138.     # 想顯示的資料
  139.     column_list = ('name', 'userName', 'email')
  140.     # 另外新建表格欄位，以免密碼可見
  141.     form_extra_fields = {'password': PasswordField('Password')}
  142.  
  143.     def __init__(self, session, **kwargs):
  144.         super().__init__(models.Users, session, **kwargs)
  145.  
  146.     # 變更時，重新加密密碼
  147.     def on_model_change(self, form, User, is_created):
  148.         if form.password.data is not None:
  149.             User.password = generate_password_hash(form.password.data)
  150.  
  151.  
  152. admin.add_view(UsersView(db.session))
  153.  
  154.  
  155. @pcf.route('/', methods=["GET"])
  156. def index():
  157.     return render_template('index.html', title="總覽")
amdin 的 index.html 加入登入、驗證、註冊等連結 
  1. {% extends 'admin/master.html' %}
  2. {% block body %}
  3. {{ super() }}
  4. <div class="row-fluid">
  5.  
  6.     <div>
  7.         {% if current_user.is_authenticated %}
  8.         <p class="lead">
  9.             後台管理
  10.         </p>
  11.         <p>
  12.             Project Control File<br/>
  13.             {{ current_user }}
  14.         </p>
  15.         {% else %}
  16.         <form method="POST" action="">
  17.             {{ form.hidden_tag() if form.hidden_tag }}
  18.             {% for f in form if f.type != 'CSRFTokenField' %}
  19.             <div>
  20.             {{ f.label }}
  21.             {{ f }}
  22.             {% if f.errors %}
  23.             <ul>
  24.                 {% for e in f.errors %}
  25.                 <li>{{ e }}</li>
  26.                 {% endfor %}
  27.             </ul>
  28.             {% endif %}
  29.             </div>
  30.             {% endfor %}
  31.             <button class="btn" type="submit">Submit</button>
  32.         </form>
  33.         {{ link | safe }}
  34.         {% endif %}
  35.     </div>
  36.  
  37.     <a class="btn btn-primary" href="/"><i class="icon-arrow-left icon-white"></i> 回到首頁</a>
  38. </div>
  39. {% endblock body %}
新增 dropdown.html，可供使用者登出，與顯示目前登入的使用者 
  1. {% extends 'admin/base.html' %}
  2.  
  3. {% block access_control %}
  4. {% if current_user.is_authenticated %}
  5. <div class="btn-group pull-right">
  6.     <a class="btn dropdown-toggle" data-toggle="dropdown" href="#">
  7.         <i class="icon-user"></i> {{ current_user.name }} <span class="caret"></span>
  8.     </a>
  9.     <ul class="dropdown-menu">
  10.         <li><a href="{{ url_for('admin.logout_view') }}">Log out</a></li>
  11.     </ul>
  12. </div>
  13. {% endif %}
  14. {% endblock %}
manage.py 新增 cli 指令 updateDB 
  1. '''
  2. manage.py
  3. '''
  4. from flask_script import Manager, prompt_bool
  5. from pcf import app, db
  6.  
  7. manager = Manager(app)
  8.  
  9.  
  10. @manager.command
  11. def initDB():
  12.     """初始化資料庫"""
  13.     if prompt_bool("將失去所有資料，確定嗎？"):
  14.         db.drop_all()
  15.         db.create_all()
  16.  
  17.  
  18. @manager.command
  19. def updateDB():
  20.     """建立不存在的 table"""
  21.     db.create_all()
  22.  
  23.  
  24. if __name__ == '__main__':
  25.     manager.run()
更新資料庫是必要的，因要新增 Users，此舉不會破壞原本的資料 
python manage.py updateDB

首頁顯示資料 pandas 
Project-Control-Web
│  basic_config.py
│  data.db
│  manage.py
│
├─instance
│      debug_config.py
│      product_config.py
│
└─pcf
    │  adminView.py
    │  main.py
    │  models.py
    │  tools.py       (new)
    │  views.py       (modify) 
    │  __init__.py
    │
    ├─static
    │      style.css
    │
    └─templates
        │  index.html (modify) 
        │  layout.html
        │
        └─admin
                dropdown.html
                index.html
因 pandas 處理資料較為方便，故新增 tools.py，內有 sql to pandas 的 fucntion 
  1. '''
  2. tools.py
  3. '''
  4. import pandas as pd
  5.  
  6.  
  7. # sql to pandas
  8. def sqltoDF(query):
  9.     return pd.read_sql(query.statement, query.session.bind)
讓 views.py 的 index 可顯示資料 
  1. '''
  2. views.py
  3. '''
  4. from flask import render_template, Blueprint
  5. from flask_admin.contrib.sqla import ModelView
  6. from flask_admin.form import SecureForm
  7. import flask_login
  8. from wtforms import PasswordField
  9. from werkzeug.security import generate_password_hash
  10.  
  11. from .main import app, db
  12. from . import models
  13. from .adminView import admin
  14. from .tools import *
  15.  
  16. pcf = Blueprint('pcf', __name__)
  17.  
  18.  
  19. # 定義一基本的設定，可適用於其他的 modelview
  20. class BaseModelView(ModelView):
  21.     # 可用 Ajax 建立 models
  22.     create_modal = False
  23.     # 可用 Ajax 修改 models
  24.     edit_modal = False
  25.     # 可 export 資料
  26.     can_export = True
  27.     # 可看詳細資料，但會看到密碼
  28.     # can_view_details = True
  29.     # 加入 CSRF 驗證
  30.     form_base_class = SecureForm
  31.     # 顯示所有 one to many 和 many to many 的資料
  32.     column_display_all_relations = True
  33.     # 一頁顯示的個數
  34.     page_size = 50
  35.  
  36.     def is_accessible(self):
  37.         return flask_login.current_user.is_authenticated
  38.  
  39.  
  40. class ModelsView(BaseModelView):
  41.     # 想顯示的資料
  42.     column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
  43.                    'modelNameCode', 'remark', 'owner', 'productIDs')
  44.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  45.     inline_models = (models.ProductIDs, )
  46.  
  47.     def __init__(self, session, **kwargs):
  48.         super().__init__(models.Models, session, **kwargs)
  49.  
  50.  
  51. admin.add_view(ModelsView(db.session))
  52.  
  53.  
  54. class ProductIDsView(BaseModelView):
  55.     # 想顯示的資料
  56.     column_list = ('name', 'model', 'customer', 'customerModelName',
  57.                    'components', 'documents', 'histories')
  58.     # 欲直接搜尋的資料
  59.     column_searchable_list = ('name', 'customerModelName')
  60.     # 欲篩選的資料
  61.     column_filters = ('model', 'customer', 'components')
  62.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  63.     inline_models = (models.Components, models.Histories)
  64.  
  65.     def __init__(self, session, **kwargs):
  66.         super().__init__(models.ProductIDs, session, **kwargs)
  67.  
  68.  
  69. admin.add_view(ProductIDsView(db.session))
  70.  
  71.  
  72. class ComponentsView(BaseModelView):
  73.     # 想顯示的資料
  74.     column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
  75.                    'productIDs')
  76.     # 欲直接搜尋的資料
  77.     column_searchable_list = (models.Components.PN_number,
  78.                               models.Components.name)
  79.     # 欲篩選的資料
  80.     column_filters = ('type', 'vendor')
  81.     # 預設排列
  82.     column_default_sort = 'type'
  83.  
  84.     def __init__(self, session, **kwargs):
  85.         super().__init__(models.Components, session, **kwargs)
  86.  
  87.  
  88. admin.add_view(ComponentsView(db.session))
  89.  
  90.  
  91. class HistoriesView(BaseModelView):
  92.     # 想顯示的資料
  93.     column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
  94.                    'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
  95.     # 欲直接搜尋的資料
  96.     column_searchable_list = ('PCBAs', )
  97.     # 欲篩選的資料
  98.     column_filters = ('type', )
  99.     # 建立時可順便建立 one to many or many to many 的 relationship 資料
  100.     inline_models = (models.Documents, )
  101.  
  102.     def __init__(self, session, **kwargs):
  103.         super().__init__(models.Histories, session, **kwargs)
  104.  
  105.  
  106. admin.add_view(HistoriesView(db.session))
  107.  
  108.  
  109. class DocumentsView(BaseModelView):
  110.     # 想顯示的資料
  111.     column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
  112.                    'histories')
  113.     # 欲直接搜尋的資料
  114.     column_searchable_list = ('DN_number', )
  115.     # 欲篩選的資料
  116.     column_filters = ('type', 'stage')
  117.     # 預設排列
  118.     column_default_sort = 'type'
  119.  
  120.     def __init__(self, session, **kwargs):
  121.         super().__init__(models.Documents, session, **kwargs)
  122.  
  123.  
  124. admin.add_view(DocumentsView(db.session))
  125.  
  126.  
  127. class OwnersView(BaseModelView):
  128.     # 想顯示的資料
  129.     column_list = ('name', 'models')
  130.  
  131.     def __init__(self, session, **kwargs):
  132.         super().__init__(models.Owners, session, **kwargs)
  133.  
  134.  
  135. admin.add_view(OwnersView(db.session))
  136.  
  137.  
  138. class UsersView(BaseModelView):
  139.     # 想顯示的資料
  140.     column_list = ('name', 'userName', 'email')
  141.     # 另外新建表格欄位，以免密碼可見
  142.     form_extra_fields = {'password': PasswordField('Password')}
  143.  
  144.     def __init__(self, session, **kwargs):
  145.         super().__init__(models.Users, session, **kwargs)
  146.  
  147.     # 變更時，重新加密密碼
  148.     def on_model_change(self, form, User, is_created):
  149.         if form.password.data is not None:
  150.             User.password = generate_password_hash(form.password.data)
  151.  
  152.  
  153. admin.add_view(UsersView(db.session))
  154.  
  155.  
  156. @pcf.route('/', methods=["GET"])
  157. def index():
  158.     user = flask_login.current_user
  159.     query = db.session.query(models.Models, models.ProductIDs, models.Owners) \
  160.                 .filter(models.Owners.name==user.name) \
  161.                 .filter(models.Models.id==models.Owners.id) \
  162.                 .outerjoin(models.ProductIDs, models.Models.id==models.ProductIDs.model_id) \
  163.                 .with_labels()
  164.     df = sqltoDF(query)
  165.  
  166.     df = df[['models_name', 'models_JIRA', 'models_stage', 'models_PM', 'models_APM', 'models_projectCode', 'models_modelNameCode', 'models_remark',
  167.              'owners_name',
  168.              'productIDs_name', 'productIDs_customer', 'productIDs_customerModelName']]
  169.  
  170.     return render_template('index.html', df=df, title="總覽")
index.html 加入對應的程式碼 
  1. {% extends "layout.html" %}
  2. {% block body %}
  3. <div>
  4.     <div class="flexbox-table table-dark">
  5.         <div class='thead'>
  6.             <div class='tr'>
  7.                 <div class='th' style="flex: 2;">JIRA</div>
  8.                 <div class='th' style="flex: 2;">Model Name</div>
  9.                 <div class='th' style="flex: 2;">Customer<br/>Model Name</div>
  10.                 <div class='th' style="flex: 2;">Product ID</div>
  11.                 <div class='th' style="flex: 1;">Project Code</div>
  12.                 <div class='th' style="flex: 1;">Stage</div>
  13.                 <div class='th' style="flex: 1;">EE Owner</div>
  14.                 <div class='th' style="flex: 1;">PM</div>
  15.                 <div class='th' style="flex: 1;">APM</div>
  16.                 <div class='th' style="flex: 1;">Customer</div>
  17.                 <div class='th' style="flex: 1;">Model Name Code</div>
  18.                 <div class='th' style="flex: 4;">Remark</div>
  19.             </div>
  20.         </div>
  21.         {% for index, value in df.iterrows() %}                
  22.         <div class='tbody'>
  23.             <div class='tr'>
  24.                 <div class='td' style="flex: 2;">{{ value['models_JIRA'] }}</div>
  25.                 <div class='td' style="flex: 2;">{{ value['models_name'] }}</div>
  26.                 <div class='td' style="flex: 2;">{{ value['productIDs_customerModelName'] }}</div>
  27.                 <div class='td' style="flex: 2;">{{ value['productIDs_name'] }}</div>
  28.                 <div class='td' style="flex: 1;">{{ value['models_projectCode'] }}</div>
  29.                 <div class='td' style="flex: 1;">{{ value['models_stage'] }}</div>
  30.                 <div class='td' style="flex: 1;">{{ value['owners_name'] }}</div>
  31.                 <div class='td' style="flex: 1;">{{ value['models_PM'] }}</div>
  32.                 <div class='td' style="flex: 1;">{{ value['models_APM'] }}</div>
  33.                 <div class='td' style="flex: 1;">{{ value['productIDs_customer'] }}</div>
  34.                 <div class='td' style="flex: 1;">{{ value['models_modelNameCode'] }}</div>
  35.                 <div class='td' style="flex: 4;">{{ value['models_remark'] }}</div>
  36.             </div>
  37.         </div>
  38.         {% endfor %}
  39.     </div>
  40. </div>
  41. {% endblock %}

參考

flask 官方網站

留言

張貼留言