[Python] MySQL연동 및 차트 그리기

김예은·2023년 12월 21일

1. 문제 상황

MySQL에서 분석한 내용을 시각화하기 위해 Python과 연동이 필요한 상황

2. 해결 방법

1) 연동하는데 필요한 모듈들을 import

import pymysql
import pandas as pd
import matplotlib.pyplot as plt

2) MySQL 접속

## MySQL 접속
myMyConn = pymysql.connect(user='root', password='0000', host='localhost', port=3306, charset='utf8', database='olist ecommerce analysis')
myMyCursor = myMyConn.cursor()

3) 실행할 SQL쿼리 기입
: 연도 순서대로 차트를 보여주기 위해 SQL쿼리에 정렬쿼리(ORDER BY month ASC) 추가

## 실행할 SQL 생성
sql_yearly_sales = """
        SELECT SUBSTR(order_purchase_timestamp,1,4) AS year, round(sum(payment_value)) AS sales
        from olist_sales
        GROUP BY year
        ORDER BY year ASC
        """

sql_monthly_sales = """
        SELECT SUBSTR(order_purchase_timestamp,1,7) AS month, round(sum(payment_value)) AS sales
        from olist_sales
        GROUP BY month
        ORDER BY month ASC
        """

sql_daily_sales = """
        SELECT SUBSTR(order_purchase_timestamp,1,10) AS day, round(sum(payment_value)) AS sales
        from olist_sales
        GROUP BY day
        ORDER BY day ASC
        """

sql_days_sales = """
        SELECT DAYNAME(order_purchase_timestamp) AS dayName, round(SUM(payment_value)) AS sales
        FROM olist_sales
        GROUP BY dayName
        ORDER BY 
             CASE
                  WHEN dayName = 'Monday' THEN 1
                  WHEN dayName = 'Tuesday' THEN 2
                  WHEN dayName = 'Wednesday' THEN 3
                  WHEN dayName = 'Thursday' THEN 4
                  WHEN dayName = 'Friday' THEN 5
                  WHEN dayName = 'Saturday' THEN 6
                  WHEN dayName = 'Sunday' THEN 7
             END ASC
     ;
        """

4) 쿼리를 담을 dataframe 생성 & matplot을 이용해 그래프 생성

plt.grid(False) => 그리드 제거

## DataFrame에 SQL쿼리 결과 저장
# yearly Sales
df1 = pd.read_sql(sql_yearly_sales, myMyConn)

# 결과 출력
print(df1)

# 차트로 처리할 항목을 Series에 별도로 담는다.
c_sales = df1['sales']
c_sales.index = df1['year']
# .sort_values(ascending = True)

plt.figure(figsize=(11,9))
c_sales.plot(label='Sales', title= "Olist Yearly Sales (2016~2018)")
plt.legend(loc='lower left')
plt.grid(False)
plt.savefig('olist ecommerce_yearly sales.png')
plt.show()

#----------------------------------------------------------------------

# Monthly Sales
df2 = pd.read_sql(sql_monthly_sales, myMyConn)

# 결과 출력
print(df2)

# 차트로 처리할 항목을 Series에 별도로 담는다.
c_sales = df2['sales']
c_sales.index = df2['month']
# .sort_values(ascending = True)

plt.figure(figsize=(11,9))
c_sales.plot(label='Sales', title= "Olist Monthly Sales (2016~2018)")
plt.legend(loc='lower left')
plt.grid(False)
plt.savefig('olist ecommerce_monthly sales.png')
plt.show()

#----------------------------------------------------------------------

# Daily Sales
df3 = pd.read_sql(sql_daily_sales, myMyConn)

# 결과 출력
print(df3)

# 차트로 처리할 항목을 Series에 별도로 담는다.
c_sales = df3['sales']
c_sales.index = df3['day']
# .sort_values(ascending = True)

plt.figure(figsize=(11,9))
c_sales.plot(label='Sales', title= "Olist Daily Sales (2016~2018)")
plt.legend(loc='lower left')
plt.grid(False)
plt.savefig('olist ecommerce_daily sales.png')
plt.show()

#----------------------------------------------------------------------

# Days Sales_line graph ver
df4 = pd.read_sql(sql_days_sales, myMyConn)

# 결과 출력
print(df4)

# 차트로 처리할 항목을 Series에 별도로 담는다.
c_sales = df4['sales']
c_sales.index = df4['dayName']
# .sort_values(ascending = True)

plt.figure(figsize=(11,9))
c_sales.plot(label='Sales', title= "Olist Days of Week Sales (2016~2018)")
plt.legend(loc='lower left')
plt.grid(False)
plt.savefig('olist ecommerce_days of week sales.png')
plt.show()

# Days Sales_plot graph ver
# 데이터
labels = df4['dayName']
values = df4['sales']

# 그래프 그리기
plt.bar(labels, values)

# 제목과 레이블 추가
plt.title('Olist Days of Week Sales (2016~2018)')
plt.xlabel('Labels')
plt.ylabel('Values')

# 그래프 보이기
plt.show()