文章目录
一、前言
环境:
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 –