Flask 新增資料到 MySQL

本文的參考專案 https://github.com/shinder/flask-practice

承上篇，這裡要使用 Postman POST 傳送 JSON 文件，然後 Flask 接收後寫入資料庫。

MySQL 官網新增資料的範例

Flask route 寫法：

@app.route('/receive-json', methods=['POST'])
def receive_json():
    (cursor, cnx) = modules.mysql_connection.get_cursor()
    data = json.loads(request.get_data())  # JSON 字串轉換為 dict
    p = {}
    sids = []  # 用來記錄新增的 primary key
    p['name'] = data['name'] if 'name' in data else ''
    p['email'] = data['email'] if 'email' in data else ''
    p['mobile'] = data['mobile'] if 'mobile' in data else ''
    p['birthday'] = data['birthday'] if 'birthday' in data else '1900-01-01'
    p['address'] = data['address'] if 'address' in data else ''

    # 兩種作法
    sql1 = ("INSERT INTO `address_book`"
        "(`name`, `email`, `mobile`, `birthday`, `address`, `created_at`"
        ") VALUES (%s, %s, %s, %s, %s, NOW())")
    sql2 = ("INSERT INTO `address_book`"
        "(`name`, `email`, `mobile`, `birthday`, `address`, `created_at`"
        ") VALUES (%(name)s, %(email)s, %(mobile)s, %(birthday)s, %(address)s, NOW())")

    cursor.execute(sql1, (p['name'], p['email'], p['mobile'], p['birthday'], p['address']))
    sids.append(cursor.lastrowid)  # 取得新增項目的 primary key
    cursor.execute(sql2, p)  # 使用 dict
    sids.append(cursor.lastrowid)
    cnx.commit()  # 提交新增的資料才會生效

    return jsonify(sids)  # 輸出 JSON 格式

Postman 發需求的網址 http://localhost:5000/receive-json，JSON 文件如下：

{
    "address": "台南市",
    "birthday": "2000-11-22",
    "email": "wwww@test.com",
    "mobile": "0918777-777",
    "name": "陳小華"
}

Flask 使用 MySQL Connector

本文的參考專案 https://github.com/shinder/flask-practice

專案資料表 address_book 參考

連線 MySQL DB 的套件，這邊介紹最陽春的，就是 MySQL 官方出的 mysql-connector。可以依照 mysql-connector 開發人員指引 介紹的方式安裝。用 pip 安裝應該是最簡單的：

pip install mysql-connector

連線的功能我們把它獨立出來成為一個模組 app/modules/mysql_connection.py，其中 get_cursor() 可以同時回傳游標物件和連線物件：

import mysql.connector

connect_data = {
    'host': 'localhost',
    'user': 'root',
    'passwd': 'root',
    'database': 'test'
}
cnx = None

def get_connection():
    global cnx  # 將連線物件存放在全域變數
    if not cnx:
        cnx = mysql.connector.connect(**connect_data)
        return cnx
    else:
        return cnx

def get_cursor():
    cursor = get_connection().cursor(dictionary=True)  # 讀出資料使用 dict，預設為 tuple
    return (cursor, get_connection())  # 同時回傳 cursor 和 connection

在主檔案定義 route：

import modules.mysql_connection

@app.route('/try-mysql')
def try_mysql():
    (cursor, cnx) = modules.mysql_connection.get_cursor()
    sql = ("SELECT * FROM address_book")
    cursor.execute(sql)
    return render_template('data_table.html', t_data=cursor.fetchall())

樣版檔 app/templates/data_table.html：

    <tbody>
        {% for i in t_data %}
        <tr>
            <td>{{ i.name }}</td>
            <td>{{ i.email }}</td>
        </tr>
        {% endfor %}
    </tbody>

使用 pip freeze 記錄安裝的套件

一般會使用 pip freeze 查看和記錄目前專案使用的套件，常用的方式是存入 requirements.txt：

pip freeze > requirements.txt

搬移專案或 git clone 專案到別的地方重新安裝套件時：

pip -r requirements.txt

這種做法的缺點是，無法看到套件相依性的關係。可以另外使用 requirements-top.txt 來記錄手動安裝的套件。 例如，安裝 flask 只要記錄下式，而不用記錄 Jinja2、Werkzeug 等套件：

Flask==1.1.2

NodeJS 將 session 資料存入 MySQL

一般使用 express.js 時，使用的 session 套件為 express-session。使用記憶體存放 session 資料的做法：

const session = require('express-session');

app.use(session({
    saveUninitialized: false,
    resave: false,
    secret: '你的 cookie 加密字串',
    cookie: {
        maxAge: 1200000  // 單位為毫秒
    }
}));

若要將 session 存入資料庫，需要先安裝 express-mysql-session 套件。設定方式如下，其中的 db_connect2.js 請看 上篇：

const session = require('express-session');
const MysqlStore = require('express-mysql-session')(session);
const db = require(__dirname + '/db_connect2');

const sessionStore = new MysqlStore({}, db);
app.use(session({
    saveUninitialized: false,
    resave: false,
    secret: '你的 cookie 加密字串',
    store: sessionStore,
    cookie: {
        maxAge: 1200000
    }
}));

若使用 session 可以在資料庫看到這樣的資料：

CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `expires` int(11) unsigned NOT NULL,
  `data` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sessions` (`session_id`, `expires`, `data`) VALUES
('8CDH6O91CkkY_1DpJs7h3YmzbqgQeqrF', 1588332706, '{"cookie":{"originalMaxAge":1200000,"expires":"2020-05-01T11:31:42.263Z","httpOnly":true,"path":"/"},"hello":"shinder"}');

ALTER TABLE `sessions`
  ADD PRIMARY KEY (`session_id`);

NodeJS 使用 mysql2 連線 MySQL

Node 連線 MySQL 資料庫，常用的套件為 mysql 和 mysql2。mysql 是比較資深的套件，但缺點是沒有直接支援 Promise，所以在使用上若要使用 Promise 需要使用 bluebird 之類的套件。

mysql2 標榜更快，支援 Promise。以下為連線的 module ( db_connect2.js )：

const mysql = require('mysql2');
const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'test',
    waitForConnections: true,
    connectionLimit: 10,  // 最大連線數
    queueLimit: 0
});
module.exports =  pool.promise();  // 滙出 promise pool

在 express.js 使用上的例子：

const db = require(__dirname + '/db_connect2');

app.get('/try-db', (req, res)=>{
    const sql = "SELECT * FROM address_book LIMIT 3";
    db.query(sql).then(([results, fields])=>{
        res.json(results);
    });
});

Babel-node: 在 Node 上使用全 ES6 語法

目前 Node.js 已經可以使用絕大部份的 ES6 語法來開發，其中不支援的語法主要是 import 和 export。 Node.js 原生只支援 CommonJS 的 require() 和 module.exports 的語法。 若要使用全 ES6 語法開發可以使用 Babel-Node。Babel-node CLI 和 Node CLI 功能一樣，但多了將 ES6 編譯成 ES5 的功能。

• 1. 首先要先安裝三個 babel 套件：@babel/core, @babel/node, @babel/preset-env。

npm i @babel/core @babel/node @babel/preset-env

• 2. 在專案中建立 babel.config.json

{
    "presets": [ "@babel/preset-env" ]
}

• 3. 接著就可以使用 babel-node 執行 js 程式：

npx babel-node src/index.js

如果使用 nodemon 啟動 express.js 測試專案，可以設定 package.json 中的 scripts：

{
    "scripts": {
        "start": "nodemon --exec babel-node src/index.js"
    }
}

使用 babel-node 啟動感覺比直接使用 node 啟動要來得慢一點，這就看個人決定是否要使用 babel-node 了。

NodeJS 連線 MongoDB

假設我們有個現成的 node/express 專案，首先安裝 mongodb 套件：

npm i mongodb

官方 mongodb 套件說明 裡面有各版本的教學和 API 文件。 先撰寫可以建立連線並使用某個 DB 的模組，在此檔名為 mdb_connect.js，只要滙出 getDB 方法即可：

const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';
const dbName = 'test';
let _db;  // 存放對應 DB 的物件
const client = new MongoClient(url, {useUnifiedTopology: true});

client.connect()
    .then(c => {
        _db = client.db(dbName);  // c 同 client
    })
    .catch(error=>{
        console.log('Cannot connect the mongodb server!');
        console.log(error);
    });

const getDB = ()=>{
    if(!_db) throw new Error('No MongoDB connection!');
    return _db;
};
module.exports =  getDB;

模組被滙入之後，就開始依設定連線，並將 Db 物件存放到 _db 變數內，呼叫 getDB() 即回傳 _db 所指的 Db 物件。 以下為在 express app 下使用的情況：

const getDB = require(__dirname + '/mdb_connect');

app.get('/try-mdb', (req, res)=>{
    const mdb = getDB();
    mdb.collection('books')
        .find({})
        .toArray()  // Cursor 的 toArray()
        .then((ar)=>{
            res.json(ar);
        })
});

之前 關聯查詢 的用法，另外用了 AggregationCursor 的 forEach() 方法：

app.get('/try-mdb2', (req, res)=>{
    const mdb = getDB();
    const ar = [];
    mdb.collection('books')
        .aggregate([
            {
                $lookup: {
                    from: 'publishers',
                    foreignField: '_id',
                    localField: 'publisher_id',
                    as: 'publisher'
                }
            }
        ])
        .forEach(function(doc){
            ar.push(doc)
        }).then(()=>{
            res.json(ar);
        })
});

MongoDB Collections 之間的關聯性

MongoDB 官方關於關聯性的說明，在關聯式資料庫中，表和表之間的關聯是很平常的事情，在 MongoDB 為了方便資料的維護，當然也有易於處理關聯的設計。在此就不談一對一的情況。

官方 以文件參照建構一對多的模型 裡面的例子其實很容易了解，我們就以裡面的範例來討論。

以下是直接使用嵌入子文件的方式來處理 books 中的出版商資料，很明顯的在維護上每次要更動的資料量會很多：

{
    title: "MongoDB: The Definitive Guide",
    author: ["Kristina Chodorow", "Mike Dirolf"],
    published_date: ISODate("2010-09-24"),
    pages: 216,
    language: "English",
    publisher: {
        name: "O'Reilly Media",
        founded: 1980,
        location: "CA"
    }
}
{
    title: "50 Tips and Tricks for MongoDB Developer",
    author: "Kristina Chodorow",
    published_date: ISODate("2011-05-06"),
    pages: 68,
    language: "English",
    publisher: {
        name: "O'Reilly Media",
        founded: 1980,
        location: "CA"
    }
}

另一種是將出版商出版的書籍記錄在出版商的 collection publishers 內。缺點是，若要從書籍去找出版商，這樣子的效能會比較差：

{
    name: "O'Reilly Media",
    founded: 1980,
    location: "CA",
    books: [123456789, 234567890, ...]
}

{
    _id: 123456789,
    title: "MongoDB: The Definitive Guide",
    author: ["Kristina Chodorow", "Mike Dirolf"],
    published_date: ISODate("2010-09-24"),
    pages: 216,
    language: "English"
}
{
    _id: 234567890,
    title: "50 Tips and Tricks for MongoDB Developer",
    author: "Kristina Chodorow",
    published_date: ISODate("2011-05-06"),
    pages: 68,
    language: "English"
}

比較好的做法，就是 books 的 publisher_id 關聯到 publishers 的 _id。

db.publishers.insertOne({
    _id: "oreilly",
    name: "O'Reilly Media",
    founded: 1980,
    location: "CA"
})

db.books.insertMany([{
    _id: 123456789,
    title: "MongoDB: The Definitive Guide",
    author: ["Kristina Chodorow", "Mike Dirolf"],
    published_date: ISODate("2010-09-24"),
    pages: 216,
    language: "English",
    publisher_id: "oreilly"
}, {
    _id: 234567890,
    title: "50 Tips and Tricks for MongoDB Developer",
    author: "Kristina Chodorow",
    published_date: ISODate("2011-05-06"),
    pages: 68,
    language: "English",
    publisher_id: "oreilly"
}])

查詢時，使用 aggregate() 和 $lookup。$lookup 可以提供同關聯資料庫的 JOIN，方式如下：

db.books.aggregate([
    {
        $lookup: {
            from: 'publishers',
            foreignField: '_id',
            localField: 'publisher_id',
            as: 'publisher'
        }
    }
]).pretty()

• from: 為要合併查詢的 collection (publishers)。
• foreignField: 為 publishers 的對應欄位。
• localField: 為 books 中對應 publishers._id 的欄位。
• as: 為查詢結果將 publishers 的文件所放入的欄位（暫時，查詢呈現）。

MongoDB 資料格式驗證 (json schema validation)

MongoDB 雖然在儲存的資料上很有彈性，但在很多時候太彈性的資料反而不好處理。所以在 collections 上也提供了格式驗證的功能，當設定了 $jsonSchema 表示新增的資料或修改後的資料都必須符合設定的格式。

以 官方的格式驗證說明 的例子來說明，可以在建立 collection 時，設定格式驗證規則：

db.createCollection("students", {
    validator: {
        $jsonSchema: {
            bsonType: "object",  // 文件的類型
            required: [ "name", "year", "major", "address" ],  // 必要欄位
            properties: {
                name: {
                    bsonType: "string",  // 欄位類型：字串
                    description: "說明的文字"
                },
                year: {
                    bsonType: "int",
                    minimum: 2017,  // 最小值
                    maximum: 3017  // 最大值
                },
                major: {
                    enum: [ "Math", "English", "Computer Science", "History", null ],
                    description: "必須是上列的其中一個"
                },
                gpa: {
                    bsonType: "double",
                    description: "must be a double if the field exists"
                },
                address: {
                    bsonType: "object",
                    required: [ "city" ],  // 必要的子欄位
                    properties: {
                        street: {
                            bsonType: "string",
                            description: "must be a string if the field exists"
                        },
                        city: {
                            bsonType: "string",
                            "description": "must be a string and is required"
                        }
                    }
                }
            }
        }
    }
})

BSON Types 的官方參考

也可以使用 collMod 指令，變更 collection 的格式驗證規則：

db.runCommand({
    collMod: "contacts",
    validator: {
        $jsonSchema: {
            bsonType: "object",
            required: ["phone"],
            properties: {
                phone: {
                    bsonType: "string",
                    description: "must be a string and is required"
                },
                email: {
                    bsonType: "string",
                    pattern: "@mongodb\.com$",
                    description: "must be a string and match the regular expression pattern"
                },
                status: {
                    enum: ["Unknown", "Incomplete"],
                    description: "can only be one of the enum values"
                }
            }
        }
    },
    validationLevel: "moderate",
    validationAction: "warn"
})

使用 db.getCollectionInfos() 可以顯示各個 collections 的詳細訊息。

> db.runCommand({
    collMod: "compoundTest",
    validator: {
        $jsonSchema: {
            bsonType: "object",
            required: ['a', 'b'],
            properties: {
                a: {
                    bsonType: "int"
                },
                b: {
                    bsonType: "int"
                }
            }
        }
    },
    validationLevel: "moderate",  // 等級
    validationAction: "warn"  // 沒通過時
})
> db.getCollectionInfos({name: 'compoundTest'})

MongoDB 索引

MongoDB 和傳統關聯式資料庫概念相念，都離不開 CRUD，裡面也是讀取（查詢）資料是最多變化的，也可能是最複雜的。其他三個動作，只要讀取熟悉了之後，就相對簡單。

讀取資料，經常需要排序，為了效能也有索引的設置，其中包含單欄（single field）索引和複合索引（compound index）。

首先先使用 explain() 查看讀取的策略，如下列（省略了不相干的內容）。在 queryPlanner.winningPlan 可以看到 stage 值為 COLLSCAN，代表 collections scan，也就是逐筆讀取。

> db.inventory.find()
{ "_id" : ObjectId("5ea4715cda0c749138d46e52"), "item" : "paper", "qty" : 100 }
{ "_id" : ObjectId("5ea4715cda0c749138d46e53"), "item" : "journal", "quantity" : 25 }
{ "_id" : ObjectId("5ea4715cda0c749138d46e54"), "item" : "planner", "qty" : 75 }
{ "_id" : ObjectId("5ea4715cda0c749138d46e55"), "item" : "postcard", "qty" : 45 }
> db.inventory.find({item: 'postcard'}).explain()
{
 "queryPlanner" : {
  "winningPlan" : {
   "stage" : "COLLSCAN",
   "filter" : {
    "item" : {
     "$eq" : "postcard"
    }
   },
   "direction" : "forward"
  }
 }
}

接著我們使用 createIndex() 新增一個 item 欄位的升冪索引。numIndexesAfter 表示加入索引後的索引數量。再使用 explain() 查看讀取的策略，stage 變成 FETCH。原則上使用 item 這欄為條件讀取資料時，速度會加快許多。

> db.inventory.createIndex({item: 1})
{
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 1,
 "numIndexesAfter" : 2,
 "ok" : 1
}
> db.inventory.getIndexes()  // 查看所有索引
[
 {
  "v" : 2,
  "key" : {
   "_id" : 1
  },
  "name" : "_id_",
  "ns" : "test.inventory"
 },
 {
  "v" : 2,
  "key" : {
   "item" : 1
  },
  "name" : "item_1",
  "ns" : "test.inventory"
 }
]
> db.inventory.find({item: 'postcard'}).explain()
{
 "queryPlanner" : {
  "winningPlan" : {
   "stage" : "FETCH",
   "inputStage" : {
    "stage" : "IXSCAN",
    "keyPattern" : {
     "item" : 1
    },
    "indexName" : "item_1",
    "isMultiKey" : false,
    "multiKeyPaths" : {
     "item" : [ ]
    },
    "isUnique" : false,
    "isSparse" : false,
    "isPartial" : false,
    "indexVersion" : 2,
    "direction" : "forward",
    "indexBounds" : {
     "item" : [
      "[\"postcard\", \"postcard\"]"
     ]
    }
   }
  }
 }
}

組合索引的官方說明 先新增測試的資料，並建立複合索引，使用 {a: 1, b: 1}：

> db.compoundTest.insertMany([
...     {a: 10, b: 2}, {a: 10, b: 8}, {a: 10, b: 6},
...     {a: 70, b: 2}, {a: 70, b: 8}, {a: 70, b: 6},
...     {a: 30, b: 2}, {a: 30, b: 8}, {a: 30, b: 6},
... ])
> db.compoundTest.createIndex({a:1, b:1})
{
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 1,
 "numIndexesAfter" : 2,
 "ok" : 1
}
> db.compoundTest.getIndexes()
[
 {
  "v" : 2,
  "key" : {
   "_id" : 1
  },
  "name" : "_id_",
  "ns" : "test.compoundTest"
 },
 {
  "v" : 2,
  "key" : {
   "a" : 1,
   "b" : 1
  },
  "name" : "a_1_b_1",
  "ns" : "test.compoundTest"
 }
]

以下是測試及取得 queryPlanner.winningPlan.stage 的結果：

db.compoundTest.explain().aggregate({$sort:{a: 1}})  // FETCH
db.compoundTest.explain().aggregate({$sort:{a: -1}})  // FETCH
db.compoundTest.explain().aggregate({$sort:{a: 1, b:1}})  // FETCH
db.compoundTest.explain().aggregate({$sort:{a: 1, b:-1}})  // COLLSCAN
db.compoundTest.explain().aggregate({$sort:{a: -1, b:-1}})  // FETCH
db.compoundTest.explain().aggregate({$sort:{a: -1, b:1}})  // COLLSCAN

db.compoundTest.explain().aggregate({$sort:{b: 1}})  // COLLSCAN
db.compoundTest.explain().aggregate({$sort:{b: -1}})  // COLLSCAN

由結果可以知道，複合索引的欄位是有優先順序的。我們 a 放在前面，b 放在後面，所以用 a 去排序的時候都是有效的。a 加上 b 時，只有 {a: 1, b:1} 和 {a: -1, b:-1} 是有效的，因為 {a: -1, b:-1} 是設定的相反順序排序。 所有 b 為主要順序欄位的，索引都無法發揮省時的效果，只能逐筆排查詢。