flask下直接展示mysql数据库 字段
在工作中,会导出一份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' #这里用这个是不行的 注意修改为mysql 才可以正常使用 db = SQLAlchemy(app) @app.route("/listModel", methods=["GET"]) def listModel(): dbName = request.args.get("dbname","xx") 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>