pythonmysql数据分析_python数据分析-连接mysql

#-*- coding: utf-8 -*-

"""Created on Thu Aug 27 08:46:26 2015

@author: TBKKEN"""

importos;importpandas;importMySQLdb;importmatplotlib;importmatplotlib.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\\";defexeSQL(sql):print("exeSQL:" +sql)

connection.query(sql);for fileName inos.listdir(rootDir):

path=os.path.join(rootDir, fileName)if ".txt" infileName: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(('订购用户数', '订购次数', '人均订购金额'))