flask下直接展示mysql数据库字段

最新推荐文章于 2023-06-20 19:34:50 发布

weixin_30649859

最新推荐文章于 2023-06-20 19:34:50 发布

阅读量269

点赞数

文章标签：数据库 python 后端

原文链接：http://www.cnblogs.com/libaibuaidufu/p/10152275.html

版权

本文介绍如何使用Flask框架和SQLAlchemy从MySQL数据库动态生成设计文档，包括前端页面展示和后端代码实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在工作中，会导出一份mysql的html来查看，用的是就是路过秋天大神的那个工具，所以想自己用那个样式直接在后端写一个页面做展示！

前端页面

from flask import Flask,request,render_template
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db' #这里用这个是不行的　注意修改为ｍｙｓｑｌ　才可以正常使用
db = SQLAlchemy(app)


@app.route("/listModel", methods=["GET"])
def listModel():
    dbName = request.args.get("dbname","ｘｘ")
    tableStr = """select table_name,table_comment from information_schema.tables where TABLE_TYPE='BASE TABLE' and table_schema='{}'""".format(
        dbName)
    resultList = db.engine.execute(tableStr)
    _infoList = []
    if resultList:
        for index, result in enumerate(resultList):
            tableName = result[0]
            filedStr = """select ORDINAL_POSITION,column_name,COLUMN_COMMENT,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,numeric_precision,numeric_scale,COLUMN_KEY,COLUMN_DEFAULT,IS_NULLABLE from information_schema.columns where  table_schema='{}' and table_name='{}'""".format(
                dbName, tableName)
            filedList = db.engine.execute(filedStr)
            _infolist = []
            if filedList:
                for field in filedList:
                    if field[5]:
                        CHARACTER_MAXIMUM_LENGTH = field[5]
                    elif field[4]:
                        CHARACTER_MAXIMUM_LENGTH = field[4]
                    else:
                        CHARACTER_MAXIMUM_LENGTH = 0
                    if field[9] == "YES":
                        IS_NULLABLE = "Y"
                    else:
                        IS_NULLABLE = "N"
                    _infoDict = {
                        "ORDINAL_POSITION": field[0],
                        "column_name": field[1],
                        "COLUMN_COMMENT": field[2],
                        "DATA_TYPE": field[3],
                        "CHARACTER_MAXIMUM_LENGTH": CHARACTER_MAXIMUM_LENGTH,
                        "numeric_scale": field[6] if field[6] else 0,
                        "COLUMN_KEY": "Y" if field[7] else "",
                        "COLUMN_DEFAULT": field[8] if field[8] else "",
                        "IS_NULLABLE": IS_NULLABLE,
                    }
                    _infolist.append(_infoDict)
            infoDict = {
                "index": index + 1,
                "tableName": tableName,
                "table_comment": result[1],
                "fields": _infolist
            }
            _infoList.append(infoDict)
    return render_template("demos.html", rows=_infoList,dbName=dbName)


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

然后是 html 静态页面

### html

<html>
<head><title>数据库设计文档 -- {{ dbName }}</title>
    <style type='text/css'>body, td {
        font-family: verdana;
        font-size: 12px;
        line-height: 150%;
    }

    table {
        width: 100%;
        background-color: #ccc;
        margin: 5px 0;
    }

    td {
        background-color: #fff;
        padding: 3px;
        padding-left: 10px;
    }

    thead td {
        text-align: center;
        font-weight: bold;
        background-color: #eee;
    }

    a:link, a:visited, a:active {
        color: #015FB6;
        text-decoration: none;
    }

    a:hover {
        color: #E33E06;
    }</style>
</head>
<body style='text-align:center;background-image: url("{{ url_for("static",filename='18066.jpg') }}")'>
<div style='width:800px; margin:20px auto; text-align:left;'>
    <a name='index'>
        <H2 style='text-align:center; line-height:50px;'>数据库设计文档</H2>
        <div><b>数据库名：{{ dbName }}</b></div>
        <table cellspacing='1' cellpadding='0'>
            <thead>
            <tr>
                <td style='width:40px; '>序号</td>
                <td>表名</td>
                <td>说明</td>
            </tr>
            </thead>
            {% for x in rows %}
                <tr>
                    <td style='text-align:center;'>{{ x.index }}</td>
                    <td><a href='#{{ x.tableName }}'>{{ x.tableName }}</a></td>
                    <td>{{ x.table_comment }}</td>
                </tr>
            {% endfor %}
        </table>
    </a>
    {% for x in rows %}
        <a name='{{ x.tableName }}'></a>
        <div style='margin-top:30px;'><a href='#index'
                                         style='float:right; margin-top:6px;'>返回目录</a><b>表名：{{ x.tableName }}</b>
        </div>
        <div>说明：{{ x.table_comment }}</div>
        <div>数据列：</div>
        <table cellspacing='1' cellpadding='0'>
            <thead>
            <tr>
                <td style='width:40px; '>序号</td>
                <td>名称</td>
                <td>数据类型</td>
                <td>长度</td>
                <td>小数位</td>
                <td>允许空值</td>
                <td>主键</td>
                <td>默认值</td>
                <td>说明</td>
            </tr>
            </thead>
            {% for z in x.fields %}
            <tr>
                <td style='text-align:center;'>{{ z.ORDINAL_POSITION }}</td>
                <td>{{ z.column_name }}</td>
                <td align='center'>{{ z.DATA_TYPE }}</td>
                <td align='center'>{{ z.CHARACTER_MAXIMUM_LENGTH }}</td>
                <td align='center'>{{ z.numeric_scale }}</td>
                <td align='center'>{{ z.IS_NULLABLE }}</td>
                <td align='center'>{{ z.COLUMN_KEY }}</td>
                <td>{{ z.COLUMN_DEFAULT }}</td>
                <td>{{ z.COLUMN_COMMENT }}</td>
            </tr>
                {% endfor %}
        </table>
    {% endfor %}
</div>
</body>
</html>