Python 数据分析1：三种工具实现连接、读取MySQL数据库并处理MySQL数据为DataFrame

文章目录

• 一、前言
• 二、通过 pymysql 获取 MySQL 数据
• • 2.1 连接数据库
  • 2.2 读取数据
  • 2.3 处理数据
• 三、通过 mysqlclient 获取 MySQL 数据
• 四、通过 SQLAlchemy 获取 MySQL 数据
• 五、小结

一、前言

环境：
windows11 64位
Python3.9 （anaconda3）
MySQL8
pandas1.4.2

使用 Python 操作 MySQL 是数据科学和数据工程领域中一个重要的技能。

本文将介绍如何通过 Python 读取读取 MySQL 数据库，包括连接 MySQL 数据库、读取数据、处理数据等方面的内容，同时将介绍通过三种方法进行操作，分别通过 pymysql、MySQLdb 和 sqlalchemy 进行读取数据。

二、通过 pymysql 获取 MySQL 数据

2.1 连接数据库

在使用 Python 读取 MySQL 数据库之前，需要先连接 MySQL 数据库。使用 pymysql 连接数据库时，需要先安装 pymysql 库，在终端输入以下命令，等待安装完成即可。

pip install pymysql

安装完，可以在 Python 代码中，使用以下代码连接 MySQL 数据库：
注：把自己 MySQL 数据库的相关信息修改一下即可发起连接。

import pymysql  
db = pymysql.connect(
    host = "主机地址"
    ,post = 端口号
    ,user = "用户名"
    ,passwd = "密码"
    ,db = "数据库名"
    ,charset = "utf-8"
)  
cursor = db.cursor() 

2.2 读取数据

连接 MySQL 数据库之后，我们可以使用 Python 读取 MySQL 数据库中的数据，在 pymysql 中，查询数据的方法为execute()。

我们可以使用select语句查询 MySQL 数据库中的数据，并将数据存放在 Python 的变量中。
在 Python 中，可以使用以下代码查询 MySQL 数据库中的数据：

# sql 代码
sql = '''select xxx'''
# 执行查询
cursor.execute(sql)
# 获取所有记录并打印
results = cursor.fetchall()
print(results)
# 关闭游标和数据库连接，释放资源
cursor.close()
db.close()

2.3 处理数据

读取到 MySQL 数据之后，我们可以使用 Python 对数据进行处理。

数据赋值给 Python 变量cursor，不过他是一个 pymysql.cursors.Cursor对象，数据使用起来比较麻烦， 这里考虑将数据集转化为 Pandas 的 DataFrame 对象，方便做数据处理和分析。

前面我们通过cursor.fetchall()获取所有的行数据，返回的数据结构为((<第1行数据>),(<第2行数据>),(<第3行数据>)……)，每一行数据的每一个值通过逗号隔开。

但这只是获取了数据，没有表头，如果要获取表头可以通过 pymysql 提供的另外一个属性接口：cursor.description。打印该属性接口返回的数据，我们可以发现，它不仅仅是单纯是一个记录字段名的元组，数据结构跟cursor.fetchall()相似，除了返回字段名，还有字段的类型，字段的宽度，字段的精度，字段的标记，字段的索引位置，字段是否可为空。所以在拼接数据时，我们需要把字段名单独提取出来。

为了更加直观，下面我拿我本地的 MySQL 数据库做一个示例。
首先我使用的 SQL 代码如下

select user_id,sex,age,mobile from users limit 5;

在 MySQL 中，检索结果如下：

通过 Python 查询 MySQL 数据

import pymysql
import pandas as pd
#账户密码
db = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', passwd='xxx',     # 输入自己的账户和密码
    db ='my_data', charset='utf8'    # db 输入数据库，有用到的就行
)
sql = '''select user_id,sex,age,mobile from users limit 5;''' # SQL 代码
cursor = db.cursor()                 # 执行数据库的操作是由cursor完成的,使用cursor()方法获取操作游标
cursor.execute(sql)                  # 执行sql语句
datas = cursor.fetchall()            # 获取查询的所有记录
cols_info = cursor.description       # 获取行相关信息
cursor.close()                       # 关闭游标
db.close()                           # 关闭连接数据库

查看datas和cols_info的结果如下：

接下来是将上面的datas和cols_indos处理为跟 MySQL 中查询结果类似的 DataFrame 类型，以便使用，处理逻辑如下；

cols = [col[0] for col in cols_info] # 处理保留列名
df = pd.DataFrame(datas,columns=cols)

最后的结果和直接跑 SQL 代码一致。
小结一下，最终的代码如下：

import pymysql
import pandas as pd
#账户密码
db = pymysql.connect(
    host='127.0.0.1', port=3306,
    user='root', passwd='xxx',     # 输入自己的账户和密码
    db ='my_data', charset='utf8'    # db 输入数据库，有用到的就行
)
sql = '''select user_id,sex,age,mobile from users limit 5;''' # SQL 代码
cursor = db.cursor()                 # 执行数据库的操作是由cursor完成的,使用cursor()方法获取操作游标
cursor.execute(sql)                  # 执行sql语句
datas = cursor.fetchall()            # 获取查询的所有记录
cols_info = cursor.description       # 获取行相关信息
cursor.close()                       # 关闭游标
db.close()                           # 关闭连接数据库

cols = [col[0] for col in cols_info] # 处理保留列名
df = pd.DataFrame(datas,columns=cols)

为了方便复用，我我把封装成一个函数：

import pymysql
import pandas as pd

def get_datas(sql,host,post,user,passwd,db):
    #账户密码
    db = pymysql.connect(host=host, port=post,user=user, passwd=passwd,db =db, charset='utf8')
    try:
        #获取数据并初步处理
        cursor = db.cursor()                 # 执行数据库的操作是由cursor完成的,使用cursor()方法获取操作游标
        cursor.execute(sql)                  # 执行sql语句
        datas = cursor.fetchall()            # 获取查询的所有记录
        cols_info = cursor.description       # 获取行相关信息
        cols = [col[0] for col in cols_info] # 处理保留列名
        cursor.close()                       # 关闭游标
        db.close()                           # 关闭连接数据库
    except:
        print('有bug！！！结束程序')
        return None
    df = pd.DataFrame(datas,columns=cols)
    return df


host,post,user,passwd,db = ('127.0.0.1',3306,'root','xxx','my_data')
sql = '''select user_id,sex,age,mobile from my_data.users limit 5;'''
df = get_datas(sql,host,post,user,passswd,db)
df

三、通过 mysqlclient 获取 MySQL 数据

使用 mysqlclient 获取 SQL 数据的时候，也要先安装 mysqlclient 库，使用以下命令：

pip install mysqlclient

安装完，调用的时候，需要特别注意一点，需要使用 MySQLdb ，即：

import MySQLdb

可能是因为 mysqlclient 是 MySQLdb 的分支，MySQLdb 更新到 Python2 就没有再更新，而 mysqlclient 就是补足 MySQLdb 的不足，兼容了 Python3 。在 mysqlclient 中保留了 MySQLdb 的一些信息。特别注意，MySQLdb 该大写要大写，不能直接使用小写的，因为包的名字就是大写的。

注：我在 Python 3.9 的环境下需要这么使用，其他环境暂未测试（欢迎留言补充）。

mysqlclient 在连接、读取和处理 MySQL 数据和 pymysql 几乎一模一样，只要将语法中的 pymysql 修改为 MySQLdb 即可，最后符一份封装好的代码：

import MySQLdb  
import pandas as pd

def get_datas(sql,host,post,user,passwd,db):
    #账户密码
    db = MySQLdb.connect(host=host, port=post,user=user, passwd=passwd,db =db, charset='utf8')
    try:
        #获取数据并初步处理
        cursor = db.cursor()                 # 执行数据库的操作是由cursor完成的,使用cursor()方法获取操作游标
        cursor.execute(sql)                  # 执行sql语句
        datas = cursor.fetchall()            # 获取查询的所有记录
        cols_info = cursor.description       # 获取行相关信息
        cols = [col[0] for col in cols_info] # 处理保留列名
        cursor.close()                       # 关闭游标
        db.close()                           # 关闭连接数据库
    except:
        print('有bug！！！结束程序')
        return None
    df = pd.DataFrame(datas,columns=cols)
    return df


host,post,user,passwd,db = ('127.0.0.1',3306,'root','xxx','my_data')
sql = '''select user_id,sex,age,mobile from my_data.users limit 5;'''
df = get_datas(sql,host,post,user,passswd,db)
df

四、通过 SQLAlchemy 获取 MySQL 数据

由于我安装的是 anaconda3 已经把 SQLAlchemy 库帮我配置好，所以不需要进行安装，如果你本地没有该库，可以通过以下命令进行安装：

pip install sqlalchemy

前面介绍的两种方法，都需要通过几个步骤的处理才能转化为 pandas 的 DataFrame 类型，如果通过 SQLAlchemy 工具，结合 pandas 可以更加友好地实现这样的效果。

SQLAlchemy 的 create_engine()方法可以创建一个引擎，连接上 MySQL 数据库；然后将sql 代码和sql 引擎参数传递给 pandas 中的read_sql()的方法，便可直接获取到一个处理后的 DataFrame 对象 。
具体代码如下：

import pandas as pd
from sqlalchemy import create_engine
connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'\
                .format("root", "xxx", "127.0.0.1", "3306","my_data")
engine = create_engine(connect_info)
df = pd.read_sql(sql, engine)
df

为了方便复用，我我把封装成一个函数，如下：

import pandas as pd
from sqlalchemy import create_engine

# 法1：
def get_datas(sql,host,post,user,passwd,db):
    connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'\
                .format(user, passwd, host, post, db)
    engine = create_engine(connect_info)
    df = pd.read_sql(sql, engine)
    return df
    
host,post,user,passwd,db = ('127.0.0.1',3306,'root','xxx','my_data')
sql = '''select user_id,sex,age,mobile from my_data.users limit 5;'''
df = get_datas(sql,host,post,user,passswd,db)
df

补充：sqlalchemy 还有另外一种执行方式，通过引擎对象的execute()方法直接执行 SQL 代码，参考代码如下：

import pandas as pd
from sqlalchemy import create_engine
def get_datas(sql,host,post,user,passwd,db):
    connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'\
                .format(user, passwd, host, post, db)
    engine = create_engine(connect_info)
    # 执行SQL语句
    cursor = engine.execute(sql)
    
    datas = list()
    for data in cursor:
        dic = dict()
        for k, v in data._mapping.items(): # 不用 _mapping 也可以，后续会被弃用而已
            dic[k] = v
        datas.append(dic)
    df = pd.DataFrame(datas)
    return df
host,post,user,passwd,db = ('127.0.0.1',3306,'root','xxx','my_data')
sql = '''select user_id,sex,age,mobile from my_data.users limit 5;'''
df = get_datas(sql,host,post,user,passswd,db)
df

返回的对象的数据结果比较复杂，通过遍历执行结果，对每一次遍历的sqlalchemy.engine.row.LegacyRow对象，通过data._mapping.items()获取到字段名和值的键值对数据，如：ROMappingView({'user_id': 7, 'sex': 0, 'age': 25, 'mobile': '16345678901'})，这时可以遍历将所有数据取出整理为字典，然后作为元素传递给列表datas。

五、小结

本文介绍了 pymysql、mysqlclient 和 SQLAlchemy 三种工具如何连接、读取和处理数据。 pymysql 和 mysqlclient 的语法比较相似，处理成 DataFrame 过程相对复杂一些，而 SQLAlchemy 则可以借用 pandas 的read_sql()方法更加便捷处理 MySQL 数据。

读者可以通过每一小节末尾我封装好的函数，改一改传递的参数，拿来即用！
如果觉得有用可以点个赞，如果还觉得不够给力，可以留下您宝贵的意见。

– End –