mysql 取最大值_Python3与Mysql的交互之旅:pymsql学习

python和mysql都是很好的数据分析工具，那么如何通过python来调用和执行mysql的查询语言呢？下面就以python3操作mysql的一个实际的案例来进行演示。

• 注：我们事先已经通过Navicat for MySQL创建好了一个名为“lsc_database”的mysql数据库，该库里面有一个数据表《Online Retail.xlsx》，该数据表如下。

1 任务目标

编写python用sql语句对数据库中的数据表《Online Retail.xlsx》做一个数据探索，查看每一个数据列的最大值、最小值、平均值，以熟悉python3和mysql的交互操作。

2 环境配置

python3和mysql交互需要用到pymysql这样一个模块，而原生的python环境没有这个模块，因此我们不妨在这里安装一下。

pip install pymysql

• 安装好了以后，会有如上提示，你需要重启一下你的python。

简单来说，利用pymysql执行sql语句有如下几个步骤：（1）创建连接对象，（2）获取游标对象，（3）执行sql语句，（4）关闭游标对象和连接对象，（5）将查询结果(一个列表)转换成DataFrame数据格式。

• 接下来我们就可以正式开启python和mysql的交互之旅啦。

首先，导入接下来需要用到的一些python包：

import pymysql
import datetime
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt

因为要多次执行sql查询语句，为了避免重复写代码，这里简单写了两个函数：

def SelRet_to_df(ret_sel):
    """
        由于pymysql查询得到的数据是一个列表，不方便结果查看，因此
        本函数用于将查询结果列表转换成DataFrame格式数据，以方便查看
        调用格式：Ret_DF = SelRet_to_df(ret_sel)
    """
    row = len(ret_sel)                    # 查询结果的数量
    col = len(ret_sel[0])                 # 查询结果中字段的数量
    cf_index = np.arange(len(ret_sel))    # dataframe的索引
    col_names = list(ret_sel[0].keys())   # 字段名称
    sel_values = []                       # 用于存放查询结果的值
    
    for rr in range(row):
        the_vals_rr = list(ret_sel[rr].values())  # 字段名称对应的值
        sel_values.append(the_vals_rr)
        
    Ret_DF = pd.DataFrame(sel_values, index = cf_index, columns=col_names)
    return Ret_DF

def Python_SQL(the_sql,parms_conn):
    """
        函数功能：本函数用于通过pymysql模块根据自定义的SQL语句，和mysql交互
        调用格式：Ret_DF = Python_SQL(the_sql)
        参数说明：the_sql->你要执行的SQL语句，parms_conn->你的连接对象参数；Ret_DF->查询结果
        
    """
    ## 创建连接对象
    my_conn = pymysql.connect(host=parms_conn['host'],                   # 访问的服务器
                              port=parms_conn['port'],                   # mysql端口号
                              user=parms_conn['user'],                   # mysql用户名
                              password=parms_conn['password'],           # mysql密码
                              db=parms_conn['db'],                       # 你要操作的数据库名
                              charset=parms_conn['charset'],             # 编码方式
                              cursorclass=parms_conn['cursorclass'])

    ## 获取Cursor对象(游标对象)
    my_cursor = my_conn.cursor()

    ## 执行sql语句
    ret_row = my_cursor.execute(the_sql)    #返回生效的行数，游标对象取到哪里了，还有多少没取

    ## 取出execute语句执行后的查询结果，
    ret_sel = my_cursor.fetchall()          #取出查询结果的所有数据记录，ret_sel是一个列表，
                                            #每个元素为一个字典（存放各列元素）
    # my_cursor.fetchone()                  #只取一条，如果这条语句多次执行，则从前往后依次取
    # my_cursor.fetchmany(3)                #取出查询结果中的3条数据记录

    ## 关闭游标和连接对象
    my_cursor.close()
    my_conn.close()
    
    ## 将查询结果转换成DataFrame格式
    Ret_DF = SelRet_to_df(ret_sel)
    return Ret_DF

设置连接对象参数：

parms_conn = {
    'host':'localhost',   # 访问本地服务器
    'port':3306,          # mysql端口号
    'user':'root',        # mysql用户名
    'password':'111111',  # mysql密码
    'db':'lsc_database',  # 你要操作的数据库名
    'charset':'utf8',     # 编码方式
    'cursorclass':pymysql.cursors.DictCursor
}  

3 统计分析

有了以上准备，现在，你可以在python里对《Online Retail.xlsx》大概浏览一下了。

• 注：C_ID字段是我设计数据库表格的时候添加的一个自增列，作为表格的主键。

the_sql = """
            select * from online_retail;
          """                           #编写你要执行的sql语句，这里我查出表中的全部数据
Ret_DF = Python_SQL(the_sql)            #调用函数执行你的SQL语句并返回查询结果

Ret_DF                                  #显示查询结果

(1) 我们来统计一下原始数据表中的Quantity、UnitPrice和CustomerID两个字段的最大值和最小值。

the_sql = """
            select min(Quantity) as 'Quantity_Min', max(Quantity) as 'Quantity_Max',
            avg(Quantity) as 'Quantity_Avg',
            min(UnitPrice) as 'UnitPrice_Min', max(UnitPrice) as 'UnitPrice_Max',
            avg(UnitPrice) as 'UnitPrice_Avg',
            min(CustomerID) as 'CustomerID_Min', max(CustomerID) as 'CustomerID_Max'
            from online_retail where UnitPrice>0 and Quantity>=0;
          """                           #你要执行的sql语句
Ret_DF = Python_SQL(the_sql)            #调用函数执行你的SQL语句并返回查询结果

Ret_DF                                  #显示查询结果

以上数据说明：

• 一个订单里面一种商品的销售量最大为80995件，至少会有1件，而平均为9件，说明大多数订单中，各商品的销售量相对较低；
• 商品单价的统计值中，最小为0￥，说明该商品可能是赠品，最贵的商品价格为13541.0￥。

按照InvoiceNo合并数据，因为同一个发票编号对应一笔订单：

the_sql = """
            select Country as "国家" , InvoiceNo as '发票编号', 
            InvoiceDate as '订购日期',  CustomerID as '顾客编号', 
            sum(Quantity*UnitPrice) as '订单金额'
            from online_retail 
            group by InvoiceNo;
          """                           #你要执行的sql语句
Ret_DF = Python_SQL(the_sql)

Ret_DF

有了这个数据，我们就可以统计出一些其他衍生变量的最值和均值了。

例如：我们可以从订单金额的角度出发，找出订单金额的最大值、最小值、均值。

the_sql = """
            select max(InvoiceNo_Price.订单金额) as '最大订单金额', 
            min(InvoiceNo_Price.订单金额) as '最小订单金额', 
            avg(InvoiceNo_Price.订单金额) as '订单金额均值'
            from ( select Country as "国家" , InvoiceNo as '发票编号', InvoiceDate as '订购日期', 
             CustomerID as '顾客编号', sum( abs( Quantity ) * UnitPrice) as '订单金额' 
            from online_retail where UnitPrice>=0 group by InvoiceNo) as InvoiceNo_Price;

          """ 
Ret_DF = Python_SQL(the_sql)

Ret_DF

• 显然，最大的一笔订单的销售金额为161990.0￥；当然最小订单的销售额为0￥，初步猜测该商品可能是活动赠品。

接下来，我们还可以看看各个国家的最大订单，最小订单分别是多少。

the_sql = """
            select InvoiceNo_Price.国家 , max(InvoiceNo_Price.订单金额) as '最大订单金额', 
            min(InvoiceNo_Price.订单金额) as '最小订单金额', avg(InvoiceNo_Price.订单金额) as '订单金额均值',
            count(InvoiceNo_Price.发票编号) as '订单总量'  
            from ( select Country as "国家" , InvoiceNo as '发票编号', InvoiceDate as '订购日期', 
             CustomerID as '顾客编号', sum( abs( Quantity ) * UnitPrice) as '订单金额' 
            from online_retail where UnitPrice>=0 group by InvoiceNo) as InvoiceNo_Price 
            group by InvoiceNo_Price.国家 order by 最大订单金额 desc;

          """ 
Ret_DF = Python_SQL(the_sql)

Ret_DF

4 案例小结

本次案例，我们利用pymysql模块实现python3对sql语句的调用，实现了在python里面写sql语句。首先，针对《Online Retail.xlsx》表格的数值型字段进行了最大值、最小值以及均值的统计分析，完整的走了一遍pymysql的使用流程。需要注意：(1) 调用了SQL语句以后，一定要关闭游标对象和连接对象；(2) pymysql执行sql语句查询的结果为一个列表，为了便于后续处理，你需要将列表数据转换成DataFrame格式的数据。