【python学习】使用Mysql数据库进行数据分析-pymysql模块

s1k9y9

已于 2022-12-09 19:53:30 修改

阅读量1.9k

点赞数 2

分类专栏：数据分析工具栏文章标签：数据库 mysql

于 2022-09-22 12:27:34 首次发布

原文链接：https://zhuanlan.zhihu.com/p/442573792

版权

工具栏同时被 2 个专栏收录

32 篇文章 2 订阅

订阅专栏

数据分析

11 篇文章 0 订阅

订阅专栏

参考：如何从Mysql8.0数据库中导入数据？四种方法帮你快速开启数据分析的第一步 - 知乎 (zhihu.com)

本文简述用python的Pymysql库对Mysql数据库进行数据分析，包括数据写入数据库存储，以及数据库中数据分析。

一、Pymysql模块介绍

二、实操步骤

三、案例

一、Pymysql模块介绍

Mysql数据库是数据挖掘任务的数据源之一，通过pymysql模块可以直接连接Mysql数据库，进行增删改查等操作。Python连接Mysql的流程如下：

python与数据库连接时并不是一次性读取了所有数据，而是根据SQL语言进行操作。cursor在这里相当于执行SQL查询的货车，在Mysql数据库和Python程序之间传递信息。

二、实操步骤

1.导入pymysql

# 导入数值运算numpy、数据分析pandas、数据库连接pymysql和时间调试模块

import pandas as pd
import numpy as np
import pymysql
import time

2.连接Mysql数据库

# 连接Mysql数据库
db = pymysql.connect(host="localhost",
                       user="root",
                       password="123456",
                       database="zgj_project",
                       charset="utf8mb4",
                       cursorclass=pymysql.cursors.DictCursor)

pymysql.connect()参数说明：

host(str): MySQL服务器地址，默认为本地主机(localhost)
port(int): MySQL服务器端口号，默认为当前用户
user(str): 用户名，没有默认值
password(str): 连接密码，没有默认值
db(str): 数据库名称
charset(str): 连接编码
cursorclass(str): cursor()使用的种类，默认值为MySQLdb.cursors.Cursor

pymysql.connect()实例对象方法说明：

db.close(): 可关闭数据库连接，并释放相关资源。
db.cursor([cursorClass]):返回一个指针对象，用于访问和操作数据库中的数据。
db.begin(): 用于开始一个事务，如果数据库的AUTOCOMMIT已经开启就关闭它，直到事务调用commit()和rollback()结束。
db.commit(): 表示事务提交
db.rollback():表示事务回退

3.创建游标

# 创建游标
cursor = db.cursor()

db.cursor()指针对象游标的方法说明：

cursor()的类型，括号中不填默认为元组，
还有DictCursor: 字典类型，SSCursor: 无缓冲元组类型，SSDictCursor: 无缓冲字典类型
适用于数据量很大，一次性返回太慢，或者服务端带宽较小；创建连接时，通过cursorclass 参数指定类型。
cursor.close(): 方法，关闭指针并释放相关资源。
cursor.execute(query[,parameters]):执行数据库查询。
cursor.fetchall(): 方法，可取出指针结果集中的所有行，返回的结果集一个元组(tuples)。
cursor.fetchmany([size=cursor.arraysize]):方法，从查询结果集中取出多行，我们可利用可选的参数指定取出的行数。
cursor.fetchone(): 方法，从查询结果集中返回下一行。
cursor.arraysize: 属性,指定由cursor.fetchmany()方法返回行的数目，影响fetchall()的性能，默认值为1。
cursor.rowcount: 属性,指出上次查询或更新所发生行数。-1表示还没开始查询或没有查询到数据。

4.示例全部代码

#连接的全流程
#导入pymysql模块
import pymysql
#创建实例化对象
db = pymysql.connect(host="localhost",
                     user="root",
                     password="*********",
                     database="orderinfo",
                     charset='utf8mb4',
                     cursorclass=pymysql.cursors.DictCursor)
#创建游标
cursor = db.cursor()
#编写SQL语句
sql = "select * from ad limit 5000" 
#执行SQL语句
cursor.execute(sql)  
#取回查询结果集，并将字符串类型结果集转换为列表
result =  cursor.fetchall()
finalresult = list(result) 
#关闭游标
cursor.close() 
#关闭数据库连接
db.close()

过程中可能会出现程序异常情况，为了防止程序崩溃，采用以下两种方法增强代码鲁棒性。

（1）方法一：使用try...except连接防止程序崩溃

#方法一，连接过程使用try...except防止程序崩溃
import pymysql
#创建实例化对象
db = pymysql.connect(host="localhost",
                     user="root",
                     password="**********",
                     database="orderinfo",
                     charset='utf8mb4',
                     cursorclass=pymysql.cursors.DictCursor)
#创建游标
cursor = db.cursor()
try:
    sql = "select * from ad limit 5000"            #SQL查询代码，不需要分号
    t_start = time.time()                          #设置程序运行开始时间戳
    cursor.execute(sql)                            #游标执行SQL查询代码
    result =  cursor.fetchall()                    #获取结果集，此时查询过程已经完成
    finalresult = list(result)                     #取回的结果是元组，将其进行列表化
    t_mid = time.time()                            #设置程序运行截点时间戳
    print("成功获取数据！总共用时：",t_mid-t_start)#打印程序运行信息
except Exception as e:                             #程序异常判断
    cursor.rollback                                #程序运行异常进行回滚
finally: 
    cursor.close()              #一次查询结束后关闭游标，如果不关闭游标，下次还可以接着使用，但是占用资源
    db.close()                                     #关闭数据库连接
    t_end = time.time()                            #设置程序运行结束时间戳
    print("连接完毕！总共用时：",t_end-t_start)    #打印程序运行信息

每次都这么写实在太繁琐，所以，Python引入了with语句来自动帮我们调用close()方法：

（2）方法二：用with as结构连接

import pymysql
#创建实例化对象
db = pymysql.connect(host="localhost",
                     user="root",
                     password="***********",
                     database="orderinfo",
                     charset='utf8mb4',
                     cursorclass=pymysql.cursors.DictCursor)
#创建游标
cursor = db.cursor()
try:
    with db.cursor() as cursor:
        cursor.execute("drop table if exists employee")
        sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
        cursor.execute(sql)
        db.commit()
        
    with db.cursor() as cursor:
        sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
               LAST_NAME, AGE, SEX, INCOME) \
               VALUES ('%s', '%s',  %s,  '%s',  %s)" % \
              ('Mac','Mohan',20, 'M',2000)
       
        # 执行sql语句
        cursor.execute(sql)
        # 执行sql语句
        db.commit()
        
    with db.cursor() as cursor:
        # 读取单条记录
        sql = "SELECT * FROM EMPLOYEE"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)
finally:
        db.close()
# {'FIRST_NAME': 'Mac', 'LAST_NAME': 'Mohan', 'AGE': 20, 'SEX': 'M', 'INCOME': 2000.0}

注：可采用封装函数以提高代码复用

# 封装
def get_con():      #连接数据库
    """
    获取MySql连接，return：mysql connection
    """
    return pymysql.connect(host="localhost",
                     user="root",
                     password="**********",
                     database="orderinfo",
                     charset='utf8mb4',
                     cursorclass=pymysql.cursors.DictCursor)
def get_query(sql): #进行数据查询
    """
    根据SQL代码进行查询，并返回结果
    paramater SQL
    return str"""
    conn = get_con()
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()
        return list(cursor.fetchall())
    finally:
        cursor.close()
        conn.close()
        
def insert_or_update(sql):
    """
    执行插入或更新数据操作
    paramater insert SQL or update SQL
    return None"""
    conn = get_con()
    try:
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit() #注意这里只有commit才真正开始执行
    finally:
        cursor.close()
        conn.close()

# if __name__ = "__main__":
#     sql = "SELECT * FROM EMPLOYEE"
#     get_query(sql)

三、案例

步骤：

1.从Excel中读取数据，json行简单整理

#从Excel中导入数据
import pandas as pd


dt1 = pd.read_excel(r"E:\test.xlsx")
dt1.info(memory_usage="deep")  # 查看数据集的基本信息，183 entries, total 20 columns


#获取数据的列名，以存入数据库
col = dt1.columns
#确定各列的数据类型
varchar = "varchar(60)"
int_ = "int"
float_ = "float"
datatype = [int_,varchar,varchar,float_,int_,int_,int_,int_,int_,varchar,varchar,varchar,varchar,varchar,varchar,varchar,int_,varchar,varchar,varchar]
#将列名和数据类型进行拼接
ncol = pd.DataFrame(list(zip(col,datatype)))
ncol["new"] = ncol[0]+" "+ncol[1]+","
print(ncol["new"])

输出结果：

0 item_id int,

1 item_name varchar(60),

2 TradeName varchar(60),

3 price float,

4 total_sale int,

5 month_sale int,

6 accum_comm int,

7 TM_points int,

8 CollectCount int,

9 Tastes varchar(60),

10 BodyType varchar(60),

11 ApplicablePhase varchar(60),

12 Brand varchar(60),

13 Classification varchar(60),

14 Breed varchar(60),

15 Manufacturer varchar(60),

16 Weight int,

17 Origin varchar(60),

18 ManufacturerAddress varchar(60),

19 RecipeTastePrescription varchar(60),

Name: new, dtype: object

2.Python连接Mysql数据库创建一张新表

# 连接数据库，创建数据表Product_details_rawdata
sql1 = """create table Product_details_rawdata(
            item_id bigint,
            item_name varchar(120),
            TradeName varchar(60),
            price float,
            total_sale int,
            month_sale int,
            accum_comm int,
            TM_points int,
            CollectCount int,
            Tastes varchar(60),
            BodyType varchar(60),
            ApplicablePhase varchar(60),
            Brand varchar(60),
            Classification varchar(60),
            Breed varchar(60),
            Manufacturer varchar(60),
            Weight int,
            Origin varchar(60),
            ManufacturerAddress varchar(60),
            RecipeTastePrescription varchar(60))"""
#成功调用向数据库orderinfo中创建一张新的空表
insert_or_update(sql1)

3. 将数据导入Mysql数据库存储

（1）方法一：通过命令行进行文件导入

连接数据库后，解除安全模式命令：set global local_infile = 'ON'，

导入csv文件命令：

->load data local infile "E:\\CBdata\\1、orderdata\\output.csv"
->into table orderdata
->fields terminated by ","
->ignore 1 lines;

注：mysql只能导入CSV文件，要想导入Excel文件，需要转换，然后在记事本打开，选择utf_8编码。

（2）方法二：通过mysqlworkbench图形界面导入文件

（3）方法三:通过for循环一句一句插入到表中

for i in range(10):
    sql2 = "INSERT INTO Product_details_rawdata(item_id, item_name, TradeName, price, total_sale,\
       month_sale, accum_comm, TM_points, CollectCount, Tastes,\
       BodyType, ApplicablePhase, Brand, Classification, Breed,\
       Manufacturer, Weight, Origin, ManufacturerAddress,\
       RecipeTastePrescription) VALUES (%s,'%s','%s',%s,%s,%s,%s,%s,%s,'%s','%s','%s','%s','%s','%s','%s',%s,'%s','%s','%s')" % \
       tuple(dt1.iloc[i,:].values)"
    insert_or_update(sql2)

（4）方法四:通过pandas自带的导入数据库的功能

from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://root:password@localhost:3306/orderinfo')
dt1.to_sql(name='Product_details_rawdata', con=engine, chunksize=1000, if_exists='append', index=None)

4.Mysql数据库中取数据进行数据分析

# 从Mysql库取数据，并进行数据分析
#取出orderinfo库中的Product_details_rawdata表中的全部记录
#SQL语句
sql3 = "select * from Product_details_rawdata"     
#调用函数取出数据 
orginal_data = get_query(sql3)            
 #转换为DataFrame          
dt2 = pd.DataFrame(orginal_data,columns=col)