SQL连接Python

对于运营部门的Yoyo来说，她想要知道夜曲优选的订单都来自哪些省份，每个省份的总订单数以及总订单金额分别是多少。
这时小鹿就会通过SQL对连接的数据库进行查询，再将结果传递给Python处理，并帮助Yoyo生成可视化图表。

我们先来快速的体验一下这个过程。

Step1.1:订单表orders

订单表orders是夜曲优选数据库中用于记录订单数据的表。
小鹿通过SQL语句，可以快速获取订单表的每一行信息。

SELECT *
FROM orders; 

Step1.2:地址表address

地址表address是夜曲优选数据库中用于记录收件地址数据的表。
小鹿通过SQL语句，可以快速获取地址表的每一行信息。

SELECT *
FROM address; 

Step2:两表连接

为了统计每笔订单发往的省份信息，小鹿使用SQL语句将订单表与地址表关联起来。
按照省份对数据进行分组，并计算每个省份的订单量与订单总价。

SELECT a.province, COUNT(*), SUM(total_price)
FROM orders o 
JOIN address a 
ON o.addr_id=a.id
GROUP BY a.province;

Step3:Sql连接Pyhton

最后小鹿将刚才的查询语句整合到Python程序中。
利用Python连接数据库，并通过刚才的SQL语句获取每个省份下订单的数据，绘制成可以交互的图表信息。

import pymysql 
from pyecharts.charts import Bar
from pyecharts import options as opts
from pyecharts.globals import ThemeType
from pyecharts.charts import Page
from pyecharts.charts import Geo
from pyecharts.globals import ChartType, SymbolType

mysql_conn = pymysql.connect(
    host='localhost', 
    port=3306, 
    user='yequ', 
    password='yequ123', 
    database='nocturneshop',
    charset='utf8')

cur = mysql_conn.cursor()

def get_elems():
    sql = """
     SELECT a.province,COUNT(*),SUM(total_price)
     FROM orders o 
     JOIN address a 
     ON o.addr_id=a.id
     GROUP BY a.province;
    """

    cur.execute(sql)
    elems = cur.fetchall()
    return elems

def create_map():
    data=get_elems()
    map_data=[]
    line_map=[]
    y_data=[]
    for i in range (0,len(data)):
        
        if data[i][0][:3] not in ["黑龙江","内蒙古"]:
            map_data.append([data[i][0][:2],data[i][1]])
            line_map.append(("四川",data[i][0][:2]))
        else:
            map_data.append([data[i][0][:3],data[i][1]])
            line_map.append(("四川",data[i][0][:3]))
        

    map = (
    Geo(init_opts=opts.InitOpts(theme=ThemeType.DARK))
    .add_schema(
        maptype="china",
        is_roam=False,
        itemstyle_opts=opts.ItemStyleOpts(color="#364A66",border_color="#58667a")
    )
    .add(
        "各省订单数量",
        map_data,
        type_=ChartType.EFFECT_SCATTER,
        
    )
    .add(
        "订单分布",
        line_map,
        type_=ChartType.LINES,
        effect_opts=opts.EffectOpts(
            symbol=SymbolType.ARROW, symbol_size=6, color="red"
        ),
        linestyle_opts=opts.LineStyleOpts(curve=0.2),
    )
    
    .set_series_opts(label_opts=opts.LabelOpts(is_show=False))
    .set_global_opts(
        visualmap_opts=opts.VisualMapOpts(),
        title_opts=opts.TitleOpts(title="订单分布情况"),
    )
    )
    return map

def create_bar():
    data=get_elems()
    x_data=[]
    y_data=[]
    for i in range (0,len(data)):
        
        if data[i][0][:3] not in ["黑龙江","内蒙古"]:
            x_data.append(data[i][0][:2])
            y_data.append(data[i][2])
        else:
            x_data.append(data[i][0][:3])
            y_data.append(data[i][2])
            
    bar= (
        Bar(init_opts=opts.InitOpts(theme=ThemeType.DARK))
        .add_xaxis(x_data)
        .add_yaxis("总订单金额", y_data)
        .set_series_opts(
            label_opts=opts.LabelOpts(position="right",font_size=12),
            
        )
        .set_global_opts(title_opts=opts.TitleOpts(title="各省总订单金额"),
            xaxis_opts=opts.AxisOpts(),
            yaxis_opts=opts.AxisOpts(),
            datazoom_opts=opts.DataZoomOpts(orient= "vertical",range_start=50,is_zoom_lock=True),
            
        )
        .reversal_axis()
    )
    
    return bar

def page_layout():
    page = Page()
    page.add(
        create_map(),
        create_bar()       
    )
    page.render("/Users/geo.html")

if __name__ == "__main__":
    page_layout()