[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 會用到的指令

'''
manage.py
'''
from flask_script import Manager
from pcf import app

manager = Manager(app)


@manager.command
def initDB():
    """初始化資料庫"""
    pass

if __name__ == '__main__':
    manager.run()

在 main.py 中，初始化 app，並利用 register_blueprint 控管 view 路徑

'''
main.py
'''
from flask import Flask

# instance_relative_config 設定有 instance 資料夾的存在，預設路徑 ../instance
app = Flask(__name__, instance_relative_config=True)
# 基本設定，來自於上層的 config.py
app.config.from_object("basic_config")

# 來自於 instance 中的 config.py，直接覆蓋之前的設定
# 路徑可用 app.instance_path 得知
# debug 設定，不用時可註解
app.config.from_pyfile('debug_config.py')
# 產品設定，來自於環境變數所提供的路徑
app.config.from_envvar('FLASKR_SETTINGS', silent=True)

# 因為 .views 中有用到 app，所以只能把 .views 往後擺
from .views import pcf

app.register_blueprint(pcf)


@app.before_request
def before_request():
    """在 request 前做的事"""
    pass

各個設定檔

'''
basic_config.py
'''
# debug 模式
DEBUG = False

'''
debug_config.py
'''
# debug 模式
DEBUG = True

讓 manage.py 可以看得到 app，所以需建立一個 __init__.py

'''
__init__.py
'''
from .main import app

基本 view 的功能

'''
views.py
'''
from flask import render_template, Blueprint
from .main import app

pcf = Blueprint('pcf', __name__)


@pcf.route('/', methods=["GET"])
def index():
    return render_template('index.html', title="總覽")

網頁程式碼
基本框架 layout.html

<!doctype html>

<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="Project Controler" />
    <meta name="author" content="子風" />
    <title>{{title}}</title>
    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
        crossorigin="anonymous">
    <!-- custom -->
    <link rel=stylesheet type=text/css href="{{ url_for('static', filename='style.css') }}">
</head>

<body>
    <div class="container">
        <nav class="navbar fixed-top navbar-expand-lg navbar-dark bg-dark">
            <a class="navbar-brand" href="{{ url_for('pcf.index') }}">首頁</a>
        </nav>
    </div>
    <div class="container">
        {% for message in get_flashed_messages() %}
        <div class="alert alert-primary" role="alert">{{ message }}</div>
        {% endfor %}
    </div>
    {% block body %}{% endblock %}
    {% block script %}{% endblock %}
</body>

首頁 index.html

{% extends "layout.html" %}
{% block body %}
<div>
    <div class="flexbox-table table-dark">
        <div class='thead'>
            <div class='tr'>
                <div class='th' style="flex: 2;">JIRA</div>
                <div class='th' style="flex: 2;">Model Name</div>
                <div class='th' style="flex: 2;">Customer<br/>Model Name</div>
                <div class='th' style="flex: 2;">Product ID</div>
                <div class='th' style="flex: 1;">Project Code</div>
                <div class='th' style="flex: 1;">Stage</div>
                <div class='th' style="flex: 1;">EE Owner</div>
                <div class='th' style="flex: 1;">PM</div>
                <div class='th' style="flex: 1;">APM</div>
                <div class='th' style="flex: 1;">Customer</div>
                <div class='th' style="flex: 1;">Model Name Code</div>
                <div class='th' style="flex: 4;">Remark</div>
            </div>
        </div>
    </div>
</div>
{% endblock %}

style.css 利用 flex 構建出 table

body {
    /* 避免被導覽列蓋住 */
    padding-top: 65px;
    background-color: #AAAAAA;
}

td,
th {
    min-width: 90px;
    text-align: center;
}

/* 定義 div table */

.flexbox-table {}

.flexbox-table .thead {}

.flexbox-table .tbody {}

.flexbox-table .tr {
    display: flex;
    flex-flow: row nowrap;
    justify-content: space-around;
    width: 100%;
}

.flexbox-table .tbody .tr:hover {
    background-color: #343a40;
}

.flexbox-table .th {
    font-weight: bold;
}

.flexbox-table .th,
.flexbox-table .td {
    flex: 2 0 0;
    text-align: center;
    width: 100%;
    border: 1px solid #32383e;
}

.flexbox-form {
    display: flex;
    flex-flow: row wrap;
    justify-content: flex-start;
}

建立資料庫 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 加入初始化資料庫的程式碼

'''
manage.py
'''
from flask_script import Manager, prompt_bool
from pcf import app, db

manager = Manager(app)


@manager.command
def initDB():
    """初始化資料庫"""
    if prompt_bool("將失去所有資料，確定嗎？"):
        db.drop_all()
        db.create_all()

if __name__ == '__main__':
    manager.run()

在 main.py 中加入 db 的宣告，在此使用的 ORM 是 SQLAlchemy

'''
main.py
'''
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

# instance_relative_config 設定有 instance 資料夾的存在，預設路徑 ../instance
app = Flask(__name__, instance_relative_config=True)
# 基本設定，來自於上層的 config.py
app.config.from_object("basic_config")

# 來自於 instance 中的 config.py，直接覆蓋之前的設定
# 路徑可用 app.instance_path 得知
# debug 設定，不用時可註解
app.config.from_pyfile('debug_config.py')
# product 設定
app.config.from_pyfile('product_config.py')
# 產品設定，來自於環境變數所提供的路徑
app.config.from_envvar('FLASKR_SETTINGS', silent=True)

# 資料庫宣告
# 資料庫路徑 app.config['SQLALCHEMY_DATABASE_URI']
db = SQLAlchemy(app)

# 因為 .views 中有用到 app，所以只能把 .views 往後擺
from .views import pcf

app.register_blueprint(pcf)


@app.before_request
def before_request():
    """在 request 前做的事"""
    pass

在各個設定檔加入 SQLAlchemy 的設定

'''
basic_config.py
'''
# debug 模式
DEBUG = False
# 顯示 sql 查詢語句
SQLALCHEMY_ECHO = False
# SQLAlchemy 將會追蹤對象的修改並且發送信號。這需要額外的內存，如果不必要的可以禁用它。
SQLALCHEMY_TRACK_MODIFICATIONS = False

'''
product_config.py
'''
import os

appFolder = os.path.dirname(__file__)

# 資料庫的路徑
SQLALCHEMY_DATABASE_URI = 'sqlite:///' \
                        + os.path.join(appFolder, r"../", 'data.db')

讓 manage.py 看得到 db，故在 __init__.py 加入 db

'''
__init__.py
'''
from .main import app, db

建立 models.py 定義資料的架構，綠色線表示 many to many，藍色線表示 one to many

'''
models.py
'''
import datetime
from flask_sqlalchemy import SQLAlchemy

from .main import db

productIDs_components = db.Table('productIDs_components', db.Model.metadata,
                                 db.Column('productIDs_id', db.Integer,
                                           db.ForeignKey('productIDs.id')),
                                 db.Column('components_id', db.Integer,
                                           db.ForeignKey('components.id')))

productIDs_documents = db.Table('productIDs_documents', db.Model.metadata,
                                db.Column('productIDs_id', db.Integer,
                                          db.ForeignKey('productIDs.id')),
                                db.Column('documents_id', db.Integer,
                                          db.ForeignKey('documents.id')))

productIDs_histories = db.Table('productIDs_histories', db.Model.metadata,
                                db.Column('productIDs_id', db.Integer,
                                          db.ForeignKey('productIDs.id')),
                                db.Column('histories_id', db.Integer,
                                          db.ForeignKey('histories.id')))

documents_histories = db.Table('documents_histories', db.Model.metadata,
                               db.Column('documents_id', db.Integer,
                                         db.ForeignKey('documents.id')),
                               db.Column('histories_id', db.Integer,
                                         db.ForeignKey('histories.id')))


class Models(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'models'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    JIRA = db.Column(db.String(20))
    name = db.Column(db.String(20), nullable=False)
    projectCode = db.Column(db.String(20))
    stage = db.Column(db.String(5), nullable=False)
    PM = db.Column(db.String(4), nullable=False)
    APM = db.Column(db.String(4), nullable=False)
    modelNameCode = db.Column(db.String(20))

    remark = db.Column(db.Text)

    owner_id = db.Column(
        db.Integer, db.ForeignKey('owners.id'), nullable=False)
    owner = db.relationship(
        "Owners", back_populates="models", foreign_keys=[owner_id])

    productIDs = db.relationship(
        "ProductIDs",
        back_populates="model",
        foreign_keys='ProductIDs.model_id')

    def __repr__(self):
        return ''.format(self.name)


class Owners(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'owners'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    name = db.Column(db.String(10), nullable=False)

    models = db.relationship(
        "Models", back_populates="owner", foreign_keys='Models.owner_id')

    def __repr__(self):
        return ''.format(self.name)


class ProductIDs(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'productIDs'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    name = db.Column(db.Text, nullable=False, unique=True)
    customer = db.Column(db.String(20), nullable=False)
    customerModelName = db.Column(db.String(20))

    model_id = db.Column(
        db.Integer, db.ForeignKey('models.id'), nullable=False)
    model = db.relationship(
        "Models", back_populates="productIDs", foreign_keys=[model_id])

    components = db.relationship(
        "Components",
        secondary=productIDs_components,
        back_populates="productIDs")
    documents = db.relationship(
        "Documents",
        secondary=productIDs_documents,
        back_populates="productIDs")
    histories = db.relationship(
        "Histories",
        secondary=productIDs_histories,
        back_populates="productIDs")

    def __repr__(self):
        return ''.format(self.name)


class Components(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'components'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    PN_number = db.Column(db.String(30), unique=True)
    name = db.Column(db.String(30), nullable=False)
    type = db.Column(db.String(30), nullable=False)
    vendor = db.Column(db.String(30))
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_components,
        back_populates="components")

    def __repr__(self):
        return ''.format(self.PN_number, self.name,
                                              self.vendor)


class Documents(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'documents'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    DN_number = db.Column(db.String(9), nullable=False, unique=True)
    type = db.Column(db.String(30), nullable=False)
    stage = db.Column(db.String(10))
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_documents,
        back_populates="documents")
    histories = db.relationship(
        "Histories", secondary=documents_histories, back_populates="documents")

    def __repr__(self):
        return ''.format(self.DN_number, self.type,
                                             self.remark)


class Histories(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'histories'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    date = db.Column(db.Date, default=datetime.datetime.now(), nullable=False)
    type = db.Column(db.String(30), nullable=False)
    PCBAs = db.Column(db.Text, nullable=False)
    PCBA_version = db.Column(db.Integer, nullable=False)
    circuitVersion = db.Column(db.Integer, nullable=False)
    PCBs = db.Column(db.Text)
    PCB_version = db.Column(db.Integer)
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_histories,
        back_populates="histories")
    documents = db.relationship(
        "Documents", secondary=documents_histories, back_populates="histories")

    def __repr__(self):
        return ''.format(self.date, self.PCBAs,
                                             self.remark)

並在 views.py import models 以供在 view 中使用

'''
views.py
'''
from flask import render_template, Blueprint
from .main import app
from . import models

pcf = Blueprint('pcf', __name__)


@pcf.route('/', methods=["GET"])
def index():
    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 ，讓內部資料能互相傳遞

'''
product_config.py
'''
import os

appFolder = os.path.dirname(__file__)

# 資料庫的路徑
SQLALCHEMY_DATABASE_URI = 'sqlite:///' \
                        + os.path.join(appFolder, r"../", 'data.db')

# 用 os.urandom(24) 產生一組即可
SECRET_KEY = b'j\x92\xedV\xab~\xd1U#\xef\x9cp\xb0\x90\x1c]\x99\xd6\xd1\xf94\x8f\xb1\xe7'

新增 adminView.py，建立後台管理

'''
adminView.py
'''
from flask_admin import Admin

from .main import app

# Create admin
admin = Admin(app, 'PCF', template_mode='bootstrap3')

在 views.py 加入希望顯示的 models

'''
views.py
'''
from flask import render_template, Blueprint
from flask_admin.contrib.sqla import ModelView
from flask_admin.form import SecureForm

from .main import app, db
from . import models
from .adminView import admin

pcf = Blueprint('pcf', __name__)

# 定義一基本的設定，可適用於其他的 modelview
class BaseModelView(ModelView):
    # 可用 Ajax 建立 models
    create_modal = False
    # 可用 Ajax 修改 models
    edit_modal = False
    # 可 export 資料
    can_export = True
    # 可看詳細資料，但會看到密碼
    # can_view_details = True
    # 加入 CSRF 驗證
    form_base_class = SecureForm
    # 顯示所有 one to many 和 many to many 的資料
    column_display_all_relations = True
    # 一頁顯示的個數
    page_size = 50


class ModelsView(BaseModelView):
    # 想顯示的資料
    column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
                   'modelNameCode', 'remark', 'owner', 'productIDs')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.ProductIDs, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Models, session, **kwargs)


admin.add_view(ModelsView(db.session))


class ProductIDsView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'model', 'customer', 'customerModelName',
                   'components', 'documents', 'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('name', 'customerModelName')
    # 欲篩選的資料
    column_filters = ('model', 'customer', 'components')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Components, models.Histories)

    def __init__(self, session, **kwargs):
        super().__init__(models.ProductIDs, session, **kwargs)


admin.add_view(ProductIDsView(db.session))


class ComponentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
                   'productIDs')
    # 欲直接搜尋的資料
    column_searchable_list = (models.Components.PN_number,
                              models.Components.name)
    # 欲篩選的資料
    column_filters = ('type', 'vendor')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Components, session, **kwargs)


admin.add_view(ComponentsView(db.session))


class HistoriesView(BaseModelView):
    # 想顯示的資料
    column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
                   'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
    # 欲直接搜尋的資料
    column_searchable_list = ('PCBAs', )
    # 欲篩選的資料
    column_filters = ('type', )
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Documents, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Histories, session, **kwargs)


admin.add_view(HistoriesView(db.session))


class DocumentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
                   'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('DN_number', )
    # 欲篩選的資料
    column_filters = ('type', 'stage')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Documents, session, **kwargs)


admin.add_view(DocumentsView(db.session))


class OwnersView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'models')

    def __init__(self, session, **kwargs):
        super().__init__(models.Owners, session, **kwargs)


admin.add_view(OwnersView(db.session))


@pcf.route('/', methods=["GET"])
def index():
    return render_template('index.html', title="總覽")

在 layout.html 加入後台管理的連結

<!doctype html>

<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <meta name="description" content="Project Controler" />
    <meta name="author" content="子風" />
    <title>{{title}}</title>
    <!-- Bootstrap -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm"
        crossorigin="anonymous">
    <!-- custom -->
    <link rel=stylesheet type=text/css href="{{ url_for('static', filename='style.css') }}">
</head>

<body>
    <div class="container">
        <nav class="navbar fixed-top navbar-expand-lg navbar-dark bg-dark">
            <a class="navbar-brand" href="{{ url_for('pcf.index') }}">首頁</a>
            <div class="collapse navbar-collapse" id="navbarNavDropdown">
                <ul class="navbar-nav">
                    <a class="nav-link" href="{{ url_for('admin.index') }}">Admin</a>
                </ul>
            </div>
        </nav>
    </div>
    <div class="container">
        {% for message in get_flashed_messages() %}
        <div class="alert alert-primary" role="alert">{{ message }}</div>
        {% endfor %}
    </div>
    {% block body %}{% endblock %}
    {% block script %}{% endblock %}
</body>

在 templates 中新建 admin 資料夾，並加入 index.html 後台管理的首頁

{% extends 'admin/master.html' %} 
{% block body %} 
{{ super() }}
<div class="row-fluid">
    <div>
        <p class="lead">
            後台管理
        </p>
        <p>
            Project Control File
            <br/>
        </p>
    </div>
    <a class="btn btn-primary" href="{{ url_for('pcf.index') }}">
        <i class="icon-arrow-left icon-white"></i> 回到首頁</a>
</div>
{% 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

'''
models.py
'''
import datetime
from flask_sqlalchemy import SQLAlchemy

from .main import db

productIDs_components = db.Table('productIDs_components', db.Model.metadata,
                                 db.Column('productIDs_id', db.Integer,
                                           db.ForeignKey('productIDs.id')),
                                 db.Column('components_id', db.Integer,
                                           db.ForeignKey('components.id')))

productIDs_documents = db.Table('productIDs_documents', db.Model.metadata,
                                db.Column('productIDs_id', db.Integer,
                                          db.ForeignKey('productIDs.id')),
                                db.Column('documents_id', db.Integer,
                                          db.ForeignKey('documents.id')))

productIDs_histories = db.Table('productIDs_histories', db.Model.metadata,
                                db.Column('productIDs_id', db.Integer,
                                          db.ForeignKey('productIDs.id')),
                                db.Column('histories_id', db.Integer,
                                          db.ForeignKey('histories.id')))

documents_histories = db.Table('documents_histories', db.Model.metadata,
                               db.Column('documents_id', db.Integer,
                                         db.ForeignKey('documents.id')),
                               db.Column('histories_id', db.Integer,
                                         db.ForeignKey('histories.id')))


class Models(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'models'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    JIRA = db.Column(db.String(20))
    name = db.Column(db.String(20), nullable=False)
    projectCode = db.Column(db.String(20))
    stage = db.Column(db.String(5), nullable=False)
    PM = db.Column(db.String(4), nullable=False)
    APM = db.Column(db.String(4), nullable=False)
    modelNameCode = db.Column(db.String(20))

    remark = db.Column(db.Text)

    owner_id = db.Column(
        db.Integer, db.ForeignKey('owners.id'), nullable=False)
    owner = db.relationship(
        "Owners", back_populates="models", foreign_keys=[owner_id])

    productIDs = db.relationship(
        "ProductIDs",
        back_populates="model",
        foreign_keys='ProductIDs.model_id')

    def __repr__(self):
        return ''.format(self.name)


class Owners(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'owners'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    name = db.Column(db.String(10), nullable=False)

    models = db.relationship(
        "Models", back_populates="owner", foreign_keys='Models.owner_id')

    def __repr__(self):
        return ''.format(self.name)


class ProductIDs(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'productIDs'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    name = db.Column(db.Text, nullable=False, unique=True)
    customer = db.Column(db.String(20), nullable=False)
    customerModelName = db.Column(db.String(20))

    model_id = db.Column(
        db.Integer, db.ForeignKey('models.id'), nullable=False)
    model = db.relationship(
        "Models", back_populates="productIDs", foreign_keys=[model_id])

    components = db.relationship(
        "Components",
        secondary=productIDs_components,
        back_populates="productIDs")
    documents = db.relationship(
        "Documents",
        secondary=productIDs_documents,
        back_populates="productIDs")
    histories = db.relationship(
        "Histories",
        secondary=productIDs_histories,
        back_populates="productIDs")

    def __repr__(self):
        return ''.format(self.name)


class Components(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'components'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    PN_number = db.Column(db.String(30), unique=True)
    name = db.Column(db.String(30), nullable=False)
    type = db.Column(db.String(30), nullable=False)
    vendor = db.Column(db.String(30))
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_components,
        back_populates="components")

    def __repr__(self):
        return ''.format(self.PN_number, self.name,
                                              self.vendor)


class Documents(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'documents'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    DN_number = db.Column(db.String(9), nullable=False, unique=True)
    type = db.Column(db.String(30), nullable=False)
    stage = db.Column(db.String(10))
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_documents,
        back_populates="documents")
    histories = db.relationship(
        "Histories", secondary=documents_histories, back_populates="documents")

    def __repr__(self):
        return ''.format(self.DN_number, self.type,
                                             self.remark)


class Histories(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'histories'
    # 設定 primary_key
    id = db.Column(
        db.Integer, primary_key=True, autoincrement=True, nullable=False)
    date = db.Column(db.Date, default=datetime.datetime.now(), nullable=False)
    type = db.Column(db.String(30), nullable=False)
    PCBAs = db.Column(db.Text, nullable=False)
    PCBA_version = db.Column(db.Integer, nullable=False)
    circuitVersion = db.Column(db.Integer, nullable=False)
    PCBs = db.Column(db.Text)
    PCB_version = db.Column(db.Integer)
    remark = db.Column(db.Text)

    productIDs = db.relationship(
        "ProductIDs",
        secondary=productIDs_histories,
        back_populates="histories")
    documents = db.relationship(
        "Documents", secondary=documents_histories, back_populates="histories")

    def __repr__(self):
        return ''.format(self.date, self.PCBAs,
                                             self.remark)


# Create user model.
class Users(db.Model):
    # 若不寫則看 class name
    __tablename__ = 'users'
    # 設定 primary_key
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(10))
    userName = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120))
    password = db.Column(db.String(64))

    # Flask-Login integration
    def is_authenticated(self):
        return True

    def is_active(self):
        return True

    def is_anonymous(self):
        return False

    def get_id(self):
        return self.id

    def __repr__(self):
        return ''.format(self.name, self.userName)

adminView.py 加入登入、驗證、註冊機制

'''
adminView.py
'''
from flask import url_for, redirect, render_template, request
from flask_admin import Admin, AdminIndexView, helpers, expose
from wtforms import form, fields, validators
from flask_login import LoginManager, current_user, login_user, logout_user
from werkzeug.security import generate_password_hash, check_password_hash

from .main import app, db
from . import models


# Define login and registration forms (for flask-login)
class LoginForm(form.Form):
    userName = fields.TextField(validators=[validators.required()])
    password = fields.PasswordField(validators=[validators.required()])

    def validate_login(self, field):
        user = self.get_user()

        if user is None:
            raise validators.ValidationError('Invalid user')

        # we're comparing the plaintext pw with the the hash from the db
        if not check_password_hash(user.password, self.password.data):
            # to compare plain text passwords use
            # if user.password != self.password.data:
            raise validators.ValidationError('Invalid password')

    def get_user(self):
        return db.session.query(
            models.Users).filter_by(userName=self.userName.data).first()


class RegistrationForm(form.Form):
    name = fields.TextField()
    userName = fields.TextField(validators=[validators.required()])
    email = fields.TextField()
    password = fields.PasswordField('New Password', [
        validators.DataRequired(),
        validators.EqualTo('confirm', message='Passwords must match')
    ])
    confirm = fields.PasswordField('Repeat Password')

    def validate_login(self, field):
        if db.session.query(models.Users).filter_by(
                userName=self.userName.data).count() > 0:
            raise validators.ValidationError('Duplicate username')


# Initialize flask-login
def init_login():
    login_manager = LoginManager()
    login_manager.init_app(app)

    # Create user loader function
    @login_manager.user_loader
    def load_user(user_id):
        return db.session.query(models.Users).get(user_id)


# Create customized index view class that handles login & registration
class MyAdminIndexView(AdminIndexView):
    @expose('/')
    def index(self):
        if not current_user.is_authenticated:
            return redirect(url_for('.login_view'))
        return super(MyAdminIndexView, self).index()

    @expose('/login/', methods=('GET', 'POST'))
    def login_view(self):
        # handle user login
        form = LoginForm(request.form)
        if helpers.validate_form_on_submit(form):
            user = form.get_user()
            if user:
                login_user(user)

        if current_user.is_authenticated:
            return redirect(url_for('.index'))
        link = 'Don\'t have an account? Click here to register.

'
        self._template_args['form'] = form
        self._template_args['link'] = link
        return super(MyAdminIndexView, self).index()

    @expose('/register/', methods=('GET', 'POST'))
    def register_view(self):
        form = RegistrationForm(request.form)
        if helpers.validate_form_on_submit(form):
            user = models.Users()

            form.populate_obj(user)
            # we hash the users password to avoid saving it as plaintext in the db,
            # remove to use plain text:
            user.password = generate_password_hash(form.password.data)

            db.session.add(user)
            db.session.commit()

            login_user(user)
            return redirect(url_for('.index'))
        link = 'Already have an account? Click here to log in.

'
        self._template_args['form'] = form
        self._template_args['link'] = link
        return super(MyAdminIndexView, self).index()

    @expose('/logout/')
    def logout_view(self):
        logout_user()
        return redirect(url_for('.index'))


# Initialize flask-login
init_login()

# Create admin
admin = Admin(
    app,
    'PCF',
    index_view=MyAdminIndexView(),
    base_template='admin/dropdown.html',
    template_mode='bootstrap3')

在 views.py 加入驗證機制，並增加 Users 顯示

'''
views.py
'''
from flask import render_template, Blueprint
from flask_admin.contrib.sqla import ModelView
from flask_admin.form import SecureForm
import flask_login
from wtforms import PasswordField
from werkzeug.security import generate_password_hash

from .main import app, db
from . import models
from .adminView import admin

pcf = Blueprint('pcf', __name__)


# 定義一基本的設定，可適用於其他的 modelview
class BaseModelView(ModelView):
    # 可用 Ajax 建立 models
    create_modal = False
    # 可用 Ajax 修改 models
    edit_modal = False
    # 可 export 資料
    can_export = True
    # 可看詳細資料，但會看到密碼
    # can_view_details = True
    # 加入 CSRF 驗證
    form_base_class = SecureForm
    # 顯示所有 one to many 和 many to many 的資料
    column_display_all_relations = True
    # 一頁顯示的個數
    page_size = 50

    def is_accessible(self):
        return flask_login.current_user.is_authenticated


class ModelsView(BaseModelView):
    # 想顯示的資料
    column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
                   'modelNameCode', 'remark', 'owner', 'productIDs')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.ProductIDs, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Models, session, **kwargs)


admin.add_view(ModelsView(db.session))


class ProductIDsView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'model', 'customer', 'customerModelName',
                   'components', 'documents', 'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('name', 'customerModelName')
    # 欲篩選的資料
    column_filters = ('model', 'customer', 'components')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Components, models.Histories)

    def __init__(self, session, **kwargs):
        super().__init__(models.ProductIDs, session, **kwargs)


admin.add_view(ProductIDsView(db.session))


class ComponentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
                   'productIDs')
    # 欲直接搜尋的資料
    column_searchable_list = (models.Components.PN_number,
                              models.Components.name)
    # 欲篩選的資料
    column_filters = ('type', 'vendor')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Components, session, **kwargs)


admin.add_view(ComponentsView(db.session))


class HistoriesView(BaseModelView):
    # 想顯示的資料
    column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
                   'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
    # 欲直接搜尋的資料
    column_searchable_list = ('PCBAs', )
    # 欲篩選的資料
    column_filters = ('type', )
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Documents, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Histories, session, **kwargs)


admin.add_view(HistoriesView(db.session))


class DocumentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
                   'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('DN_number', )
    # 欲篩選的資料
    column_filters = ('type', 'stage')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Documents, session, **kwargs)


admin.add_view(DocumentsView(db.session))


class OwnersView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'models')

    def __init__(self, session, **kwargs):
        super().__init__(models.Owners, session, **kwargs)


admin.add_view(OwnersView(db.session))


class UsersView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'userName', 'email')
    # 另外新建表格欄位，以免密碼可見
    form_extra_fields = {'password': PasswordField('Password')}

    def __init__(self, session, **kwargs):
        super().__init__(models.Users, session, **kwargs)

    # 變更時，重新加密密碼
    def on_model_change(self, form, User, is_created):
        if form.password.data is not None:
            User.password = generate_password_hash(form.password.data)


admin.add_view(UsersView(db.session))


@pcf.route('/', methods=["GET"])
def index():
    return render_template('index.html', title="總覽")

amdin 的 index.html 加入登入、驗證、註冊等連結

{% extends 'admin/master.html' %}
{% block body %}
{{ super() }}
<div class="row-fluid">

    <div>
        {% if current_user.is_authenticated %}
        <p class="lead">
            後台管理
        </p>
        <p>
            Project Control File<br/>
            {{ current_user }}
        </p>
        {% else %}
        <form method="POST" action="">
            {{ form.hidden_tag() if form.hidden_tag }}
            {% for f in form if f.type != 'CSRFTokenField' %}
            <div>
            {{ f.label }}
            {{ f }}
            {% if f.errors %}
            <ul>
                {% for e in f.errors %}
                <li>{{ e }}</li>
                {% endfor %}
            </ul>
            {% endif %}
            </div>
            {% endfor %}
            <button class="btn" type="submit">Submit</button>
        </form>
        {{ link | safe }}
        {% endif %}
    </div>

    <a class="btn btn-primary" href="/"><i class="icon-arrow-left icon-white"></i> 回到首頁</a>
</div>
{% endblock body %}

新增 dropdown.html，可供使用者登出，與顯示目前登入的使用者

{% extends 'admin/base.html' %}

{% block access_control %}
{% if current_user.is_authenticated %}
<div class="btn-group pull-right">
    <a class="btn dropdown-toggle" data-toggle="dropdown" href="#">
        <i class="icon-user"></i> {{ current_user.name }} <span class="caret"></span>
    </a>
    <ul class="dropdown-menu">
        <li><a href="{{ url_for('admin.logout_view') }}">Log out</a></li>
    </ul>
</div>
{% endif %}
{% endblock %}

manage.py 新增 cli 指令 updateDB

'''
manage.py
'''
from flask_script import Manager, prompt_bool
from pcf import app, db

manager = Manager(app)


@manager.command
def initDB():
    """初始化資料庫"""
    if prompt_bool("將失去所有資料，確定嗎？"):
        db.drop_all()
        db.create_all()


@manager.command
def updateDB():
    """建立不存在的 table"""
    db.create_all()


if __name__ == '__main__':
    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

'''
tools.py
'''
import pandas as pd


# sql to pandas
def sqltoDF(query):
    return pd.read_sql(query.statement, query.session.bind)

讓 views.py 的 index 可顯示資料

'''
views.py
'''
from flask import render_template, Blueprint
from flask_admin.contrib.sqla import ModelView
from flask_admin.form import SecureForm
import flask_login
from wtforms import PasswordField
from werkzeug.security import generate_password_hash

from .main import app, db
from . import models
from .adminView import admin
from .tools import *

pcf = Blueprint('pcf', __name__)


# 定義一基本的設定，可適用於其他的 modelview
class BaseModelView(ModelView):
    # 可用 Ajax 建立 models
    create_modal = False
    # 可用 Ajax 修改 models
    edit_modal = False
    # 可 export 資料
    can_export = True
    # 可看詳細資料，但會看到密碼
    # can_view_details = True
    # 加入 CSRF 驗證
    form_base_class = SecureForm
    # 顯示所有 one to many 和 many to many 的資料
    column_display_all_relations = True
    # 一頁顯示的個數
    page_size = 50

    def is_accessible(self):
        return flask_login.current_user.is_authenticated


class ModelsView(BaseModelView):
    # 想顯示的資料
    column_list = ('JIRA', 'name', 'projectCode', 'stage', 'PM', 'APM',
                   'modelNameCode', 'remark', 'owner', 'productIDs')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.ProductIDs, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Models, session, **kwargs)


admin.add_view(ModelsView(db.session))


class ProductIDsView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'model', 'customer', 'customerModelName',
                   'components', 'documents', 'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('name', 'customerModelName')
    # 欲篩選的資料
    column_filters = ('model', 'customer', 'components')
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Components, models.Histories)

    def __init__(self, session, **kwargs):
        super().__init__(models.ProductIDs, session, **kwargs)


admin.add_view(ProductIDsView(db.session))


class ComponentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('PN_number', 'name', 'type', 'vendor', 'remark',
                   'productIDs')
    # 欲直接搜尋的資料
    column_searchable_list = (models.Components.PN_number,
                              models.Components.name)
    # 欲篩選的資料
    column_filters = ('type', 'vendor')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Components, session, **kwargs)


admin.add_view(ComponentsView(db.session))


class HistoriesView(BaseModelView):
    # 想顯示的資料
    column_list = ('date', 'type', 'PCBAs', 'PCBA_version', 'circuitVersion',
                   'PCBs', 'PCB_version', 'remark', 'productIDs', 'documents')
    # 欲直接搜尋的資料
    column_searchable_list = ('PCBAs', )
    # 欲篩選的資料
    column_filters = ('type', )
    # 建立時可順便建立 one to many or many to many 的 relationship 資料
    inline_models = (models.Documents, )

    def __init__(self, session, **kwargs):
        super().__init__(models.Histories, session, **kwargs)


admin.add_view(HistoriesView(db.session))


class DocumentsView(BaseModelView):
    # 想顯示的資料
    column_list = ('DN_number', 'type', 'stage', 'remark', 'productIDs',
                   'histories')
    # 欲直接搜尋的資料
    column_searchable_list = ('DN_number', )
    # 欲篩選的資料
    column_filters = ('type', 'stage')
    # 預設排列
    column_default_sort = 'type'

    def __init__(self, session, **kwargs):
        super().__init__(models.Documents, session, **kwargs)


admin.add_view(DocumentsView(db.session))


class OwnersView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'models')

    def __init__(self, session, **kwargs):
        super().__init__(models.Owners, session, **kwargs)


admin.add_view(OwnersView(db.session))


class UsersView(BaseModelView):
    # 想顯示的資料
    column_list = ('name', 'userName', 'email')
    # 另外新建表格欄位，以免密碼可見
    form_extra_fields = {'password': PasswordField('Password')}

    def __init__(self, session, **kwargs):
        super().__init__(models.Users, session, **kwargs)

    # 變更時，重新加密密碼
    def on_model_change(self, form, User, is_created):
        if form.password.data is not None:
            User.password = generate_password_hash(form.password.data)


admin.add_view(UsersView(db.session))


@pcf.route('/', methods=["GET"])
def index():
    user = flask_login.current_user
    query = db.session.query(models.Models, models.ProductIDs, models.Owners) \
                .filter(models.Owners.name==user.name) \
                .filter(models.Models.id==models.Owners.id) \
                .outerjoin(models.ProductIDs, models.Models.id==models.ProductIDs.model_id) \
                .with_labels()
    df = sqltoDF(query)

    df = df[['models_name', 'models_JIRA', 'models_stage', 'models_PM', 'models_APM', 'models_projectCode', 'models_modelNameCode', 'models_remark',
             'owners_name',
             'productIDs_name', 'productIDs_customer', 'productIDs_customerModelName']]

    return render_template('index.html', df=df, title="總覽")

index.html 加入對應的程式碼

{% extends "layout.html" %}
{% block body %}
<div>
    <div class="flexbox-table table-dark">
        <div class='thead'>
            <div class='tr'>
                <div class='th' style="flex: 2;">JIRA</div>
                <div class='th' style="flex: 2;">Model Name</div>
                <div class='th' style="flex: 2;">Customer<br/>Model Name</div>
                <div class='th' style="flex: 2;">Product ID</div>
                <div class='th' style="flex: 1;">Project Code</div>
                <div class='th' style="flex: 1;">Stage</div>
                <div class='th' style="flex: 1;">EE Owner</div>
                <div class='th' style="flex: 1;">PM</div>
                <div class='th' style="flex: 1;">APM</div>
                <div class='th' style="flex: 1;">Customer</div>
                <div class='th' style="flex: 1;">Model Name Code</div>
                <div class='th' style="flex: 4;">Remark</div>
            </div>
        </div>
        {% for index, value in df.iterrows() %}                
        <div class='tbody'>
            <div class='tr'>
                <div class='td' style="flex: 2;">{{ value['models_JIRA'] }}</div>
                <div class='td' style="flex: 2;">{{ value['models_name'] }}</div>
                <div class='td' style="flex: 2;">{{ value['productIDs_customerModelName'] }}</div>
                <div class='td' style="flex: 2;">{{ value['productIDs_name'] }}</div>
                <div class='td' style="flex: 1;">{{ value['models_projectCode'] }}</div>
                <div class='td' style="flex: 1;">{{ value['models_stage'] }}</div>
                <div class='td' style="flex: 1;">{{ value['owners_name'] }}</div>
                <div class='td' style="flex: 1;">{{ value['models_PM'] }}</div>
                <div class='td' style="flex: 1;">{{ value['models_APM'] }}</div>
                <div class='td' style="flex: 1;">{{ value['productIDs_customer'] }}</div>
                <div class='td' style="flex: 1;">{{ value['models_modelNameCode'] }}</div>
                <div class='td' style="flex: 4;">{{ value['models_remark'] }}</div>
            </div>
        </div>
        {% endfor %}
    </div>
</div>
{% endblock %}

參考

flask 官方網站

子風的知識庫

搜尋此網誌

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

參考

留言

張貼留言