flask结合SQLAlchemy实现可视化

使用SQLAlchemy例子

https://www.cnblogs.com/cwp-bg/p/8876012.html

https://blog.csdn.net/weixin_39352048/article/details/80213171

掌握sqlalchemy的连接方法，掌握连接后执行sql语句

连接数据库，并创建对象，初始化数据库表

from flask import Flask,render_template
from flask_sqlalchemy import SQLAlchemy
import pymysql
app = Flask(__name__)

# 连接mysql的view库
DIALCT = "mysql"
USERNAME = "root"
DRIVER = 'pymysql'
PASSWORD = "123456"
HOST = "127.0.0.1"
PORT = "3306"
DATABASE = "view"
DB_URI = "{}+{}://{}:{}@{}:{}/{}?charset=utf8".format(DIALCT,DRIVER,USERNAME,PASSWORD,HOST,PORT,DATABASE)
# mysql+pymysql://root:123456@127.0.0.1:3306/view?charset=utf8
print(DB_URI)
app.config["SQLALCHEMY_DATABASE_URI"] = DB_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
db = SQLAlchemy(app)

class Brand(db.Model):
    __tablename__ = "brand"
    id = db.Column(db.Integer,primary_key=True,unique=True,autoincrement=True)
    sbmc = db.Column(db.TEXT)
    num = db.Column(db.Integer)

class Consumption(db.Model):
    __tablename__="consumption"
    id = db.Column(db.Integer,primary_key=True,unique=True,autoincrement=True)
    klxmc = db.Column(db.TEXT)
    xfzje = db.Column(db.Integer)

class Sales(db.Model):
    __tablename__="sales"

    id = db.Column(db.Integer,primary_key=True,unique=True,autoincrement=True)
    cplx = db.Column(db.TEXT)
    num = db.Column(db.Integer)
    # 数据库操作
    # create table sales(
    #     id int primary key auto_increment,
    #     cplx TEXT not null,
    #     num int not null);

@app.route('/')
def hello_world():
    # 实例化各类，并进行查询
    brands = Brand().query.all()
    consumptions = Consumption().query.all()
    saless =  Sales().query.all()


    return render_template('index.html',brands=brands,consumptions=consumptions,saless=saless)

@app.route('/char')
def char():
    brands = Brand().query.all()
    consumptions = Consumption().query.all()
    saless = Sales().query.all()
    return render_template("char.html",brands=brands,consumptions=consumptions,saless=saless)
if __name__ == '__main__':
    app.run()

  # 数据库操作
    '''
    根据brand表，使用echarts-wordcloud，绘制销售情况最好的前20中品牌（词云图）
			具体要求，标题为：“销售情况最好的前20种品牌”
					 标题位置为中间

    create table if not exists brand(
        id int primary key auto_increment,
        sbmc TEXT not null,
        num int not null)ENGINE=innodb DEFAULT CHARSET=utf8;

        insert into brand values(1,'NIKE',1000);
        insert into brand values(2,'Adidas',800);
        insert into brand values(3,'new balance',600);
        insert into brand values(4,'lining',400);
        insert into brand values(5,'特步',389);
        insert into brand values(6,'安踏',879);
        insert into brand values(7,'361',65);
        insert into brand values(8,'乔丹',4080);
        insert into brand values(9,'回力',34);
        insert into brand values(10,'亚科斯',45);
        insert into brand values(11,'乐凯其',650);
        insert into brand values(12,'彪马',13);
        insert into brand values(13,'美津侬',312);
        insert into brand values(14,'茵宝',546);
        insert into brand values(15,'卡帕',879);
        insert into brand values(16,'乐途',456);
        insert into brand values(17,'迪亚多纳',4132);
        insert into brand values(18,'乐途',133);
        insert into brand values(19,'乐途',314);
        insert into brand values(20,'赛琪',804);
        insert into brand values(21,'战三',468);
        

    create table if not exists consumption(
        id int primary key auto_increment,
        klxmc TEXT not null,
        xfzje int not null)ENGINE=innodb DEFAULT CHARSET=utf8;

        insert into consumption values(1,"终极会员",146355);
        insert into consumption values(2,"超级会员",46355);
        insert into consumption values(3,"黄金会员",1120);
        insert into consumption values(4,"砖石会员",4568);
        insert into consumption values(5,"铂金会员",15688);

        根据sales表，绘制各类产品的消费情况（柱状图）
			具体要求，标题为：“各类产品的消费情况”
					 副标题：“（-柱状图）”
					 标题位置为中间，
					 x轴的字体要求倾斜45度
					 将各柱状图的值显示在柱的顶端

    create table if not exists sales(
        id int primary key auto_increment,
        cplx TEXT not null,
        num int not null)ENGINE=innodb DEFAULT CHARSET=utf8;

       insert into sales values(1,'羽绒服',60);
        insert into sales values(2,'帽子',23);
        insert into sales values(3,'棉服',564);
        insert into sales values(4,'衬衫',77);
    '''

结合echarts使用，可视化

在temlates下建立index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <script src="/static/echarts.js"></script>
    <script src="/static/echarts-wordcloud.js"></script>
</head>
<body">

<div id="main1" style="width: 800px;height: 600px"></div>
<script>
        var mychart = echarts.init(document.getElementById('main1'));
        var option = {
            color:['rgb(207,65,48)'],
            title:{
                text:'各类产品的消费情况',
                subtext: '柱状图',
                left: 'center'
            },
             tooltip:{
              trigger:'axis',
              axisPointer:{
                  type: 'shadow'
              }
            },

            xAxis:{
                type:'category',
                axisLabel:{
                    rotate:30,
                    interval:0
                },
                data:[
                    {% for a in saless %}
                        "{{ a.cplx }}",
                    {% endfor %}
                ]
            },
            yAxis:{
                type: 'value'
            },
            series:{
                type: 'bar',
                barWidth:'60%',
                 label: {
                show: true,
                position: 'top'
                },
                data:[
                    {% for b in saless %}
                        {{ b.num }},
                    {% endfor %}
                ]
            },
        };
        mychart.setOption(option);
    </script>
<div id="main2" style="width: 800px;height: 600px"></div>
<script>
{#    根据consumption表绘各类会员卡的消费总金额（饼图）
{#			具体要求，标题为：“各类会员卡的消费总金额”
{#					 副标题：“（-饼图）”
{#					 标题位置为中间，
{#					 显示图例，
{#					 设置动态显示（将鼠标放到饼图的某一块上会显示这一块的占比，类似于“（10%）”）这种效果#}
    var mychart2 = echarts.init(document.getElementById('main2'));
    var option2 = {
        title: {
        text:'各类会员卡的消费总金额',
        subtext:'（-饼图）',
        left:'center'
        },
        tooltip: {
        trigger: 'item',
        formatter: '{a} <br/>{b} : {c} ({d}%)'
         },
        legend: {
            // orient: 'vertical',
            // top: 'middle',
            bottom: 10,
            left: 'center',
        data:['超级会员','终极会员','黄金会员','砖石会员','铂金会员']

        },
        series: [
            {
                type:'pie',
                radius:'60%',
                center:['50%','60%'],
                data:[
                    {% for i in consumptions %}
                    {value: {{ i.xfzje }},name:"{{ i.klxmc }}"},
                    {% endfor %}
                ],
             emphasis: {
                itemStyle: {
                    shadowBlur: 10,
                    shadowOffsetX: 0,
                    shadowColor: 'rgba(0, 0, 0, 0.5)'
                }
            }
            }
        ]

    };
    mychart2.setOption(option2)

</script>
<br><br>
<div id="main3" style="width: 800px;height: 600px"></div>
<!-- 使用echarts.js以及echarts-wordcloud.js，对前端传来的数据遍历接收，然后进行数据可视化 -->
    <script type="text/javascript">
        var mychart3 = echarts.init(document.getElementById("main3"));

        var option3={
            tooltip: {
                show: true
            },
         backgroundColor: '#F7F7F7',
            title:{
                text:'销售情况最好的前20种品牌',
                left:'center'
            },
            series:[{
//要绘制的“云”的形状。可以是表示为//回调函数的任何极性方程式，也可以是关键字。可用的礼物是圆（默认），
// 心形（苹果或心脏形状曲线，最著名的极坐标方程），菱形（正方形的//别名），三角形进，三角形，（
// 三角形直立，五边形，和星形的别名。

        // The shape of the "cloud" to draw. Can be any polar equation represented as a
        // callback function, or a keyword present. Available presents are circle (default),
        // cardioid (apple or heart shape curve, the most known polar equation), diamond (
        // alias of square), triangle-forward, triangle, (alias of triangle-upright, pentagon, and star.
                name:'品牌词云图',
                type:'wordCloud',
                shape:'circle',
        //跟随左/上/下/宽/高/右/下的位置来定位词云
        //默认设置在中间尺寸为75％x 80％。
                left: 'center',
                top: 'center',
                width: '50%',
                height: '50%',
                right: null,
                bottom: null,

        //文本大小范围，数据中的值将被映射到该范围。
        //默认为最小12像素，最大60像素。
                sizeRange: [12, 60],

//文字旋转范围和程度步骤。文本将随机在范围[-90，90]通过rotationStep 45旋转
                 rotationRange: [-90, 90],
                 rotationStep: 45,
                //在像素网格的尺寸用于标记画布的可用性
        //网格大小越大，单词之间的间距越大。
                  gridSize: 8,
                 //设置为true以允许部分在画布外部绘制单词。
        //允许绘制 大于画布大小的单词
                 drawOutOfBound: false,
                 //如果执行布局动画。
        //注意禁用它会在有很多单词时导致UI阻塞。
                 layoutAnimation: true,
                //全局文本样式
         textStyle: {
            fontFamily: 'sans-serif',
            fontWeight: 'bold',
            //颜色可以是一个回调函数或一个颜色字符串
            color: function () {
                //随机颜色
                return 'rgb(' + [
                    Math.round(Math.random() * 160),
                    Math.round(Math.random() * 160),
                    Math.round(Math.random() * 160)
                ].join(',') + ')';
            }
        },
        emphasis: {
            focus: 'self',
            textStyle: {
                shadowBlur: 10,
                shadowColor: '#333'
            }

        },
             //数据是一个数组。每个数组项必须具有name和value属性。
               data: [
                   {% for ci in brands %}
                   {name: "{{ ci.sbmc }}", value: {{ ci.num }}},
                   {% endfor %}
               ],

 // 数据是一个数组。每个数组项必须具有name和value属性。
     /*   data: [{
            name: 'Farrah Abraham',
            value: 366,
            //单个文本的样式
            textStyle: {
            }
        }]  */

            }]
        };

        mychart3.setOption(option3);
    </script>
</body>
</html>