Flask大杂烩

以Python3为例

1. 安装Flask

pip install flask

2. 主程序

app.py

from flask import Flask

app = Flask(__name__)


@app.route("/")
def home():
    return "Hello World!"

3. 运行

flask run 运行当前目录下的Flask程序(程序名必须是app.py或wsgi.py)
FLASK_APP=myapp flask run 运行Flask程序myapp.py
flask run --host 0.0.0.0 监听所有网卡

4. Debug环境

Debug环境有何用处:

程序出错后，在HTML页面显示错误调用栈
代码更新后自动重新加载

flask run 默认运行的是生产环境，将环境变量FLASK_ENV设为developoment(FLASK_ENV=development flask run)即可开启开发环境(debug环境)

5. Jetbrains PyCharm 执行Flask程序报错

Pycharm运行Flask程序，可能会报一个错误(语言相关)RuntimeError: Click will abort further execution because Python 3 was configured to use ASCII as encoding for the environment.。这种情况下，添加一个环境变量LC_ALL=en_ZH.UTF-8即可解决。

5. 使用Flask创建一个App，透视数据库结构

文件结构:

.
├── app.py
└── templates
    ├── table.html
    └── tables.html

app.py

import random

import flask
import mysql.connector
import logging

app = flask.Flask(__name__)


def connect_mysql():
    return mysql.connector.connect(host="localhost", user="root", password="root")


@app.route("/")
def index():
    return flask.redirect("/tables")


def fetch_tables():
    conn = connect_mysql()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
SELECT *
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')
""")
    tables = cursor.fetchall()
    return tables


def fetch_table_info(schema: str, table: str):
    conn = connect_mysql()
    conn.database = "information_schema"
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
                   (schema, table))
    return cursor.fetchone()


def fetch_columns(schema: str, table: str):
    conn = connect_mysql()
    conn.database = "information_schema"
    cursor = conn.cursor(dictionary=True)
    cursor.execute("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=%s AND TABLE_NAME=%s", (schema, table))
    columns = cursor.fetchall()
    return columns


def fetch_sample(schema: str, table: str):
    info = fetch_table_info(schema, table)
    columns = fetch_columns(schema, table)
    primary_column = next(filter(lambda x: x["COLUMN_KEY"] == "PRI", columns), None)
    primary_key = primary_column["COLUMN_NAME"] if primary_column else None
    primary_key_is_integer = 'int' in primary_column["COLUMN_TYPE"] if primary_column else False
    conn = connect_mysql()
    conn.database = schema
    cursor = conn.cursor(dictionary=True)
    if primary_key_is_integer:
        cursor.execute("""
SELECT {0}.*
FROM {0}
       JOIN (SELECT RAND() * MAX({1}) AS {1} FROM {0}) AS random ON {0}.{1} > random.{1}
LIMIT 1;
    """.format(table, primary_key))
    elif info["TABLE_ROWS"] < 10000:
        cursor.execute("SELECT * FROM {} ORDER BY RAND() LIMIT 1".format(table))
    elif primary_key:
        cursor.execute("SELECT * FROM {} ORDER BY {} DESC LIMIT 100".format(table, primary_key))
    else:
        cursor.execute("SELECT * FROM {} LIMIT 100".format(table))
    samples = cursor.fetchall()
    return random.choice(samples) if samples else None


def fetch_samples(schema: str, table: str):
    samples = [fetch_sample(schema, table) for _ in range(5)]
    samples = [dict(x) for x in {tuple(y.items()) for y in samples if y}]
    return samples


@app.route("/tables")
def query_tables():
    tables = fetch_tables()
    return flask.render_template("tables.html", tables=tables)


@app.route("/schemas/<string:schema>/tables/<string:table>")
def show_table(schema: str, table: str):
    tables = fetch_tables()
    info = next(filter(lambda x: x["TABLE_SCHEMA"] == schema and x["TABLE_NAME"] == table, tables))
    columns = fetch_columns(schema=schema, table=table)
    column_dict = dict((x["COLUMN_NAME"], x) for x in columns)
    samples = fetch_samples(schema=schema, table=table)
    return flask.render_template("table.html", columns=columns, tables=tables, info=info, samples=samples,
                                 column_dict=column_dict)

tables.html

<!DOCTYPE html>
<html lang="en" style="width: 100%; height: 100%;">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body style="width: 100%; height: 100%; margin: 0; display: flex; justify-content: center">
<table border="1" style="flex-grow: 1;border-collapse: collapse">
    <thead></thead>
    <tbody>
    {% for table in tables %}
        <tr>
            <td>{{ table["TABLE_SCHEMA"] }}</td>
            <td>{{ table["TABLE_NAME"] }}</td>
            <td><a href="{{ url_for("show_table", schema=table["TABLE_SCHEMA"], table=table["TABLE_NAME"]) }}">{{ table["TABLE_NAME"] }}</a></td>
            <td>{{ table["TABLE_COMMENT"] }}</td>
            <td>{{ table["TABLE_ROWS"] }}</td>
            <td>{{ table["CREATE_TIME"] }}</td>
        </tr>
    {% endfor %}
    </tbody>
</table>

</body>
</html>

table.html

<!DOCTYPE html>
<html lang="en" style="width: 100%; height: 100%;">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <style>
        a, a:visited, a:hover, a:active {
            color: inherit;
        }
    </style>
</head>
<body style="width: 100%; height: 100%; box-sizing: border-box; margin: 0; padding: 0.5em; display: flex; justify-content: center">

<table border="1" style="flex-grow: 0;border-collapse: collapse">
    <thead></thead>
    <tbody>
    {% for table in tables %}
        <tr>
            <td>
                <a href="{{ url_for("show_table", schema=table["TABLE_SCHEMA"], table=table["TABLE_NAME"]) }}">[{{ table["TABLE_SCHEMA"] }}] {{ table["TABLE_NAME"] }}</a>
            </td>
            <td>{{ table["TABLE_COMMENT"] }}</td>
        </tr>
    {% endfor %}
    </tbody>
</table>
<div style="margin-left: 0.5em ;flex-grow: 1; display: flex;flex-direction: column; align-items: stretch">
    <h2>Table Information</h2>
    <table border="1" style="border-collapse: collapse">
        <tbody>
        <tr>
            <td>Name</td>
            <td>{{ info["TABLE_NAME"] }}</td>
        </tr>
        <tr>
            <td>Comment</td>
            <td>{{ info["TABLE_COMMENT"] }}</td>
        </tr>
        <tr>
            <td>Rows</td>
            <td>{{ info["TABLE_ROWS"] }}</td>
        </tr>
        <tr>
            <td>Created At</td>
            <td>{{ info["CREATE_TIME"] }}</td>
        </tr>
        </tbody>
    </table>
    <h2>Table Columns</h2>
    <table border="1" style="border-collapse: collapse">
        <thead></thead>
        <tbody>
        {% for column in column_dict.values() %}
            <tr>
                <td>{{ column["COLUMN_NAME"] }}</td>
                <td>{{ column["COLUMN_COMMENT"] }}</td>
                <td>{{ column["COLUMN_TYPE"] }}</td>
            </tr>
        {% endfor %}
        </tbody>
    </table>
    <h2>Table Samples</h2>
    {% for sample in samples %}
        <h3>Sample {{ loop.index }}</h3>
        <table border="1" style="border-collapse: collapse">
            <tbody>
            {% for k,v in sample.items() %}
                <tr>
                    <td>{{ k }}</td>
                    <td>{{ column_dict[k]["COLUMN_COMMENT"] }}</td>
                    <td style="word-break: break-all">{{ v }}</td>
                </tr>
            {% endfor %}
            </tbody>
        </table>
    {% endfor %}
</div>
</body>
</html>