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

  • Post author:
  • Post category: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 中,检索结果如下:

image.png

通过 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

的结果如下:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NKyr6O60-1677818009078)(https://cdn.nlark.com/yuque/0/2023/png/2744391/1677718083710-3dc88c60-813e-4eeb-895b-db96bd6c0f48.png#averageHue=%23f5f5f5&clientId=u9765147f-97c8-4&from=paste&height=250&id=u5a122d5b&name=image.png)]

接下来是将上面的

datas



cols_indos

处理为跟 MySQL 中查询结果类似的 DataFrame 类型,以便使用,处理逻辑如下;

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

image.png

最后的结果和直接跑 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 该大写要大写,不能直接使用小写的,因为包的名字就是大写的。

image.png

注:我在 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 –



版权声明:本文为qq_45476428原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。