<style>

html, body, .ui-content {
background-color: #333;
color: #ddd;
}

body > .ui-infobar {
display: none;
}

.ui-view-area > .ui-infobar {
display: block;
}

.markdown-body h1,
.markdown-body h2,
.markdown-body h3,
.markdown-body h4,
.markdown-body h5,
.markdown-body h6 {
color: #ddd;
}

.markdown-body h1,
.markdown-body h2 {
border-bottom-color: #ffffff69;
}

.markdown-body h1 .octicon-link,
.markdown-body h2 .octicon-link,
.markdown-body h3 .octicon-link,
.markdown-body h4 .octicon-link,
.markdown-body h5 .octicon-link,
.markdown-body h6 .octicon-link {
color: #fff;
}

.markdown-body img {
background-color: transparent;
}

.ui-toc-dropdown .nav>.active:focus>a, .ui-toc-dropdown .nav>.active:hover>a, .ui-toc-dropdown .nav>.active>a {
color: white;
border-left: 2px solid white;
}

.expand-toggle:hover,
.expand-toggle:focus,
.back-to-top:hover,
.back-to-top:focus,
.go-to-bottom:hover,
.go-to-bottom:focus {
color: white;
}

.ui-toc-dropdown {
background-color: #333;
}

.ui-toc-label.btn {
background-color: #191919;
color: white;
}

.ui-toc-dropdown .nav>li>a:focus,
.ui-toc-dropdown .nav>li>a:hover {
color: white;
border-left: 1px solid white;
}

.markdown-body blockquote {
color: #bcbcbc;
}

.markdown-body table tr {
background-color: #5f5f5f;
}

.markdown-body table tr:nth-child(2n) {
background-color: #4f4f4f;
}

.markdown-body code,
.markdown-body tt {
color: #eee;
background-color: rgba(230, 230, 230, 0.36);
}

a,
.open-files-container li.selected a {
color: #5EB7E0;
}

</style>}
# Python + Flask 虛擬美國股票交易網站 Part2 （程式架構：資料庫/ 虛擬數據）
###### tags: `CS50` `Python` `Flask`

## 前言
vfinance 一共使用了四張表，分別是
1. User - 用來儲存用戶帳號密碼
2. TradeHistory - 用戶交易紀錄
3. Portfolio - 用戶目前持股明細
4. Watchlist - 用戶願望清單

以下開始建立數據庫模型

## 數據庫模型 vfinance/models.py

### 用戶資料庫

from vfinance.extensions import db


class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.String(20), unique = True)
    password_hash = db.Column(db.String(128))
    cash = db.Column(db.Numeric(10,2))
    position = db.Column(db.Numeric(10,2))
    
    def set_password(self, password):
        self.password_hash = generate_password_hash(password)
    
    def validate_password(self, password):
        return check_password_hash(self.password_hash, password)

### 交易紀錄
用來儲存交易紀錄的資料庫模型如下：

class TradeHistory(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow, index=True)
    symbol = db.Column(db.String(10))
    name = db.Column(db.String(50)) 
    price = db.Column(db.Numeric(10,2))
    action = db.Column(db.String(10))
    quantity = db.Column(db.Numeric(10,2))
   

    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    user = db.relationship("User", back_populates = 'trade_history')

在交易紀錄以及用戶帳號之間需要建立一堆多關係。因此在TradeHistory模型新增了一個user_id的Foregin_key, 作為指向用戶模型的外鍵，另外也建立了一個標量關係屬性user, 並在User類別建立集合關係屬性trade_history, 如下：

class User(db.Model, UserMixin):
    ...
    trade_history = db.relationship("TradeHistory", back_populates='user', cascade='all, delete-orphan')
    
class TradeHistory(db.Model):
    ...
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    user = db.relationship("User", back_populates = 'trade_history')

### 用戶持股清單

class Portfolio(db.Model):
    id = db.Column(db.Integer, primary_key= True)
    symbol = db.Column(db.String(10))
    name = db.Column(db.String(50))
    purchase_price = db.Column(db.Numeric(10,2))
    quantity = db.Column(db.Numeric(10,2))

與交易紀錄一樣，需要與用戶資料庫建立一堆多關係，如下

class User(db.Model, UserMixin):
    ...
    portfolio = db.relationship("Portfolio", back_populates = 'user', cascade='all, delete-orphan')
    
    
class Portfolio(db.Model):
    ...
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    user = db.relationship("User", back_populates = 'portfolio')

### 觀察清單 Watchlist

class Watchlist(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    symbol = db.Column(db.String(10))
    
    # 一堆多關係
    user_id = db.Column(db.Integer, db.ForeignKey("user.id"))
    user = db.relationship("User", back_populates = 'watchlist')
    
class User(db.Model, UserMixin):
    ...
    watchlist = db.relationship("Watchlist", back_populates = 'user', cascade='all, delete-orphan')

## 創造虛擬數據
為了方便編寫程式的前後端功能，可以藉由faker這個~~世界冠軍~~ package來幫助生成所需要的虛擬數據，在這個虛擬交易網站中主要需要的就是假人頭用戶，如下：

### vfinancial/fakers.py

from faker import Faker
from sqlalchemy.exc import IntegrityError
import random
from vfinance.extensions import db
from vfinance.models import User

fake = Faker()

def fake_user(count = 10):
    # register myself
    user = User(
        username = "ccjccy77828",
        cash = 10000000,
        position = 0
    )
    user.set_password("sulamoon")
    db.session.add(user)
    for i in range(count):
        simple_file = fake.simple_profile()
        user = User(
            username = simple_file["username"],
            cash = 1000000,
            position = 0
        )
        fake_password = fake.password(length = 12)
        user.set_password(fake_password)
        db.session.add(user)
        try:
            db.session.commit()
        except IntegrityError:
            db.session.rollback()     

然後在工廠函數中，建立一個forge()函數，目的是用來執行上面創造人頭用戶的函數：

### vfinancial/__ init __.py

import click

def register_commands(app):
    @app.cli.command()
    @click.option('--user', default = 10, help='Quantity of accounts, defalult is 10')
    def forge(user):
        from vfinance.fakes import fake_user

        db.drop_all()
        db.create_all()

        click.echo("Generating the User Account...")
        fake_user()

        click.echo("Done")

接著執行，打開資料庫後就可以看到拿到一堆假人頭帳戶

$flask forge
Generating the User Account...
Done