![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
# -*- coding: utf-8 -*- """ Created on Thu Aug 27 08:46:26 2015 @author: TBKKEN """ import os; import pandas; import MySQLdb; import matplotlib; import matplotlib.pyplot as plt; connection = MySQLdb.connect( host='127.0.0.1', user='root', passwd='root', db='7.1', charset='utf8' ); #导入数据到MySQL #遍历目录,把里面的所有数据入库 rootDir="C:\\Python\\7.1\\"; def exeSQL(sql): print("exeSQL: " + sql) connection.query(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(('订购用户数', '订购次数', '人均订购金额'))