import os;
import pandas;

import pymysql;

import matplotlib;
import matplotlib.pyplot as plt;

connection = pymysql.connect(
    host=‘127.0.0.1‘,
    user=‘root‘,
    passwd=‘Bqq18968969680‘,
    db=‘seven‘,
    charset=‘utf8‘
);

#导入数据到MySQL
#遍历目录，把里面的所有数据入库

rootDir="/Volumes/CHASESKY/python/03-数据分析与数据挖掘篇/1-数据分析数据可视化实战-(Python3.5)/章节7连接MySQL/7.1/";
def exeSQL(sql):
    print("exeSQL: " + sql)
    connection.query(sql) #这句是执行sql
    #%%
for fileName in os.listdir(rootDir): 
    path = os.path.join(rootDir, fileName)     
    if ".txt" in fileName:
        print(path)
        path = path.replace("\\", "\\\\");
        #拿到文件名中的时间字段
        datetime = fileName[0:8];
        tableName = "订购明细" + datetime;
        #建表、导入数据到表中
        exeSQL("drop table if exists " + tableName)
        
     
        exeSQL("create table " + tableName + "(订单编号 int, 订购日期 datetime, 用户ID int, 产品 char(8), `单价(元)` int, 数量 int, 订购金额 int);");
    
        exeSQL("LOAD DATA LOCAL INFILE ‘" + path + "‘ INTO TABLE `" + tableName + "` CHARACTER SET ‘UTF8‘ COLUMNS TERMINATED BY ‘,‘ LINES TERMINATED BY ‘\\n‘ IGNORE 1 ROWS;")# 该句无法运行
        
        #%%
        #建立日订购统计表
        exeSQL("create table if not exists 日订购统计表(统计日期 date, 订购用户数 int, 订购次数 int, 人均订购金额 double, 订购总额 double);");
        exeSQL("delete from 日订购统计表 where 统计日期=‘" + datetime + "‘;");
        exeSQL("insert into 日订购统计表 select ‘" + datetime + "‘, count(distinct 用户ID), count(用户ID), sum(订购金额)/count(distinct 用户ID), sum(订购金额) from `" + tableName + "`;");
#%%
statDay = pandas.read_sql(‘select * from 日订购统计表;‘, con=connection);
#记得用完要关闭连接
connection.close();
#%%
print(‘\n日订购统计表数据如下:‘);
print(statDay);

#绘图
font = {
    ‘family‘ : ‘SimHei‘
}

matplotlib.rc(‘font‘, **font);

plot1 = plt.plot(statDay[‘统计日期‘], statDay[‘订购用户数‘]);
plot2 = plt.plot(statDay[‘统计日期‘], statDay[‘订购次数‘]);
plot3 = plt.plot(statDay[‘统计日期‘], statDay[‘人均订购金额‘]);
plt.show();
plt.legend((‘订购用户数‘, ‘订购次数‘, ‘人均订购金额‘))

#%%