【数据挖掘】Python提取MySQL数据进行线性回归全流程

1. 摘要

我们在实际工作中，获取的数据不单单是以txt，csv，xlsx等这样的格式来呈现，经常需要将数据库(Mysql,Oracle等)中大量的数据提取出来，进行分析挖掘。本篇，将以Mysql为例，详细介绍如何用python提取数据库中的数据进行数据挖掘。基本思路是：先连接数据库，然后通过sql语句进行操作，最后对提取的数据，借助sklearn进行建模分析并进行可视化。接下来开始我们完整的数据挖掘案例的流程吧。

2. 数据库的连接

python连接数据库的API有很多，本文将借助pymysql数据库进行连接。
安装命令：
pip3 install pymysql
开始之前，先导入我们需要的包。

# 导入相应的包
import pymysql
import numpy as np
import matplotlib.pyplot as plt  # 可视化
from sklearn.linear_model import LinearRegression # 线性回归

2.1. 连接并创建数据表

python操作mysql数据库的流程如下：创建连接—获取游标—执行命令—关闭游标—关闭连接。
首先，我将在名称为"mpdata_test"的数据库底下，创建一个数据表。
创建语句为：create table 表名 (字段1 类型, 字段2 类型);
代码为：

# 创建数据表
host = 'localhost'  # 要连接的主机地址
user = 'root'       # 用于登录的数据库用户
password = '******' # 密码
database = 'mpdata_test' #要连接的数据库

try:
    db = pymysql.connect(host=host,user=user,password=password,database=database,charset="utf8")
    print("数据连接成功")
    cur = db.cursor()   # 操作之前 必走流程，创建一个游标
    # 创建数据表
    cur.execute("drop table if exists exercise_table") # 执行一条SQL语句
    sql = 'create table exercise_table(x float,y float)'
    cur.execute(sql)
    print("创建表格成功！")
except pymysql.Error as e:
    print("创建表格失败："+str(e))

2.2. 插入数据

接下来，我将给数据表中插入我将要分析的数据。
sql插入语句：insert into 表名(字段1，字段2…) values(值1，值2，…);

# 给表中插入数据
host = 'localhost'
user = 'root'
password = '******'
database = 'mpdata_test'

try:
    db = pymysql.connect(host=host,user=user,password=password,database=database,charset="utf8")
    print("数据连接成功！")
    cur = db.cursor()
    sql =  "INSERT INTO exercise_table values(%s,%s)"
    values = [("5","2.77"),("9","4.76"),("8","4.14"),("7","3.90"),("5","2.53"),("2","1.32"),("6","3.79"),("6","3.95"),("5","2.65"),("1","1.37")]
    cur.executemany(sql,values) # 对一个操作运行多个数据，如一次插入多条数据
    db.commit() # 上传完数据进行提交
    print("数据获取成功！！！")
except pymysql.Error as e:
    print("数据查询失败："+str(e))
    db.rollback()   # 操作出错时，可以用这个函数回滚到执行事务之前
    
db.close()# 关闭连接

2.3. 提取数据

导入数据成功后，我将提取数据，为后面建模分析，提供数据支撑。
sql提取数据语句：select * from 表名

# 提取数据
host = 'localhost'
user = 'root'
password = '******'
database = 'mpdata_test'

data_x,data_y = [],[]

try:
    db = pymysql.connect(host=host,user=user,password=password,database=database,charset="utf8")
    print("数据连接成功！")
    cur = db.cursor()
    sql = "select * from exercise_table"
    cur.execute(sql)
    results = cur.fetchall() # 取出操作返回的所有的行
    for row in results:
        data_x.append(row[0])
        data_y.append(row[1])
    print("数据获取成功！！！")
except pymysql.Error as e:
    print("数据查询失败："+str(e))
    db.rollback()
    
db.close()

3. 建模分析

3.1. 数据预处理

第二节，我们完成了数据表的创建，数据的插入，数据的提取。接下来，我将对提取出来的数据进行分析建模。由于得到的data_x，data_y为列表，为了后面方便导入模型，首先将他们转换为二维数据。代码如下：

# 将数据升维，方便后面处理
data_X = np.expand_dims(data_x,1)
data_Y = np.expand_dims(data_y,1)
print("data_X shape:",data_X.shape,"data_Y shape:",data_Y.shape)

3.2. 建立模型并导入数据分析

数据预处理完成后，我们可以建模并导入数据，借助sklearn建立线性回归模型y=wx+b，过程很简单。代码如下：

# 建模导入数据
LR = LinearRegression()
LR.fit(data_X,data_Y)
# 查看系数
print(LR.coef_[0],LR.intercept_)

结果如下，分别为系数w和截距b。

4. 结果可视化

我们通过以上操作完成了线性回归的建模，得到了y=wx+b中的参数w和b。得到原始数据拟合的曲线为y=0.46x+0.63。我们可以将原始数据的点位绘制在坐标轴里面，并绘制拟合曲线，看看拟合效果如何。代码如下：

# 可视化结果
plt.figure(figsize=(10,6))
plt.scatter(data_x,data_y,color='green',marker="*",linewidths=5)
plt.plot([0,10],[0.63,5.33],color="red",linewidth=2)
plt.xlabel("X_data",fontsize=12)
plt.ylabel("y_data",fontsize=12)
plt.title("result show",fontsize=18)
plt.show()

由上图，我们可以看到，简单的进行建模得到的拟合曲线，与点位基本吻合，效果不错。至此，完成了整个数据挖掘的完整流程。

5. 总结

认真看完本篇，让大家能够更好的认识实际工作中，如何使用python连接mysql数据库进行大数据分析挖掘的全流程。本篇主要讲了python如何连接数据库，如何创建数据表，如何对数据库进行增加数据和查询数据，如何对提取到的数据进行分析。希望整个过程对大家有所帮助。