# -*- coding: utf-8 -*-
import pymysql
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif'] = ['SimHei']   #顯示中文
plt.rcParams['axes.unicode_minus']=False       #正常顯示負號
 
#連接Mysql數據庫
db = pymysql.connect(host='localhost',  # 主機名
                    user='root',        # 用戶名
                    password='root',    # 密碼
                    database='mydb')  # 默認數據庫
 
 
# 獲取游標對象
cursor = db.cursor()
 
# 構造一個sql語句來查詢統計數據
sql_str = "SELECT PROD_CATEGORY as 產品分類, ROUND(SUM(PRICE),2) as 總銷售額, ROUND(SUM(PROFIT),2) as 總利潤額  FROM orders WHERE FY=2019 GROUP BY PROD_CATEGORY"
 
# 執行sql語句
cursor.execute(sql_str)
 
# 獲取查詢結果
result = cursor.fetchall()
df = pd.DataFrame(result, columns=[cursor.description[i][0] for i in range(len(cursor.description)) ])
print(df)
df.set_index('產品分類').plot(kind='pie',y='總利潤額',
                          #subplots=True, # 不使用y，畫出所有列的餅圖
                          figsize=(8,8),
                          
                         )

import matplotlib.pyplot as plt
 
sizes = df['總利潤額']
labels = df['產品分類']
plt.figure(figsize=(5,5),dpi=120)
plt.pie(sizes,         # 每個扇區大小
        labels=labels, # 每個扇區標簽
        autopct='%.2f%%', # 計算百分比格式 %格式%   %d%% 整數百分比  %.2f%% 小數點后保留2位的浮點數百分比
        
       )

# 扇區分離
explode = [0 for i in range(len(sizes))]
explode[1]=0.1
plt.pie(sizes,labels=labels,explode=explode )
 
plt.title("餅圖示例1")
plt.show()

# 扇區顏色
colors=['r','g','b','#EEEEEE']
plt.pie(sizes,labels=labels,explode=explode,colors=colors )
 
plt.title("餅圖示例2")
plt.show()

# 添加陰影
shadow = True
plt.pie(sizes,explode=explode,labels=labels,colors=colors,shadow=shadow)
plt.show()

# 顯示百分比
autopct='%.0f%%'
pctdistance=1.25
plt.pie(sizes,explode=explode,labels=labels,colors=colors,shadow=shadow,autopct=autopct,pctdistance=pctdistance) 
plt.show()

# 起始角度
startangle=36 # (->0, <-180, 逆時針)
 
explode = [0 for i in range(len(sizes))]
explode[3]=0.1
shadow=False
plt.pie(sizes,explode=explode,labels=labels,colors=colors,shadow=shadow,autopct=autopct,startangle=startangle) 
plt.show()

import matplotlib.pyplot as plt
plt.rcParams['font.sans-serif']=['SimHei'] #用來正常顯示中文標簽
plt.rcParams['figure.dpi'] = 120
 
 
sizes = [10,10,10,70]
labels = ['A','B','C','D']
 
figure = plt.figure(figsize=(5,5),facecolor='#CCCCCC')
 
center=(5,4)  # 
 
plt.title("餅圖詳解示例")
pieces,labelses,pcts = plt.pie(sizes,labels=labels,center=center,frame=True,labeldistance=0.88,autopct='%.1f%%')
pieces[3].set(color='grey')
labelses[3].set(color='red')
pcts[3].set(color='green')

plt.rcParams['figure.dpi'] = 120
plt.figure(figsize=(8,4),facecolor='#EEEEEE')
 
center1=(1,0)
center2=(2.5,0)
 
plt.pie(sizes,explode=explode,labels=labels,colors=colors,shadow=False,autopct=autopct,radius=0.5,
        center=center1,frame=True,labeldistance=0.9,rotatelabels=True) 
plt.pie(sizes,explode=explode,labels=labels,colors=colors,shadow=shadow,autopct=autopct,radius=0.5,
        center=center2,frame=True,labeldistance=0.9,rotatelabels=False) 
plt.show()

import matplotlib.pyplot as plt
# 添加數據
sizes = [15, 30, 45, 10]
labels = 'Frogs', 'Hogs', 'Dogs', 'Logs'
# 配置顏色
colors = ['yellowgreen', 'gold', 'lightskyblue', 'lightcoral']
# 配置
explode = (0, 0.1, 0, 0)
plt.pie(sizes, explode=explode, labels=labels, colors=colors,autopct='%1.1f%%', shadow=True, startangle=90)
plt.axis('equal')
# 設置全局中文字體
plt.rcParams['font.sans-serif'] = 'KaiTi' # 設置全局字體為中文 楷體
plt.rcParams['axes.unicode_minus'] = False # 不使用中文減號
# 設置圖形上的各類主題值
# plt.suptitle('餅圖',size=15,y=1.0)
plt.title("餅圖", loc='center',size=15,y=1)
 
plt.title("王小王-123于2022年3月繪制", loc='right',size=10,y=1)
 
plt.show()