干货 | 利用Python操作mysql数据库

作者 | Tao

来源 | 知乎

 

---

本文主要讲解如何利用python中的pymysql库来对mysql数据库进行操作。

先看一下最常见的操作：

• 从数据库中select需要的字段（对数据简单聚合处理）

• 将查找的数据导出为本地文件（csv、txt、xlsx等）

• 通过pandas的read_excel(csv、txt)将本地文件转化成python中的变量，并对数据进行相应的处理和分析

• 将处理好的数据通过pandas的to_excel(csv、txt)导出为本地文件

但是大家不觉得第二步很多余吗?为什么还要先导出再导入,这个中间步骤纯属浪费时间啊,理想中的步骤应该是这样的

• 将mysql中的数据导入到python中

• 利用python处理分析数据

• 导出成excel报表

这么一看是不是感觉就舒服多了？那么问题来了,怎么实现直接把mysql中的数据直接导入python中呢?

这就要讲到今天的重点了:

• 第一种方法:read_sql

• 第二种方法:pymysql

先看一下我们今天的数据库信息：

host：192.168.0.***

port：3306

user：root

密码：********

数据库：test

表名：weather_test

字段及数据:

1

read_sql()

read_sql(sql,con,index_col='None',coerce_float='True',params='None',parse_dates='None',columns='None',chunksize:None='None')

read_sql方法是pandas中用来在数据库中执行指定的SQL语句查询或对指定的整张表进行查询,以DataFrame 的类型返回查询结果.

其中各参数意义如下:

• sql:需要执行的sql语句

• con：连接数据库所需的engine，用其他数据库连接的包建立,例如SQLalchemy和pymysql

• index_col: 选择哪列作为index

• coerce_float:将数字形字符串转为float

• parse_dates:将某列日期型字符串转换为datetime型数据

• columns:选择想要保留的列

• chunksize：每次输出多少行数据

1.首先导入pandas和sqlalchemy

2.创建连接

3.编写sql代码,执行sql代码,获取返回的值

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:******@192.168.0.***:3306/test')

sql='''
select * from weather_test where
create_time between '2020-09-21' and '2020-09-22'
and city in ('杭州','上海')
'''
df = pd.read_sql(sql,engine)
df

利用pymysql建立连接并查询也是可以的

至此一次简单地利用pandas中read_sql方法从数据库获取数据就完成了

2

PyMySQL

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，可以方便的连接数据库并操作数据库

1.安装

首先打开cmd，输入 pip install pymysql 来安装pymysql这个库

2.利用pymysql操作数据库

接下来打开jupyter notebook，开始尝试操作数据库

2.1 首先导入pandas，pymysql

import pandas as pd
import pymysql

2.2 接下来创建于数据库的连接

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )

使用connect()方法可以建立与数据库的连接，其中需要的主要参数已经标注在图片上，charset建议选utf8，防止中文乱码，将建立好的连接对象赋值给db这个变量名

2.3 使用cursor()方法获取操作游标

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理，通俗来说就是，操作数据和获取数据库结果都要通过游标来操作。如果不获取游标，我们就没法获得查询出来的数据。

 

最常用的也是默认的游标就是cursor，返回的数据格式为tuple，其余的游标类型还有DictCursor，SSCursor，SSDictCursor等，SS开头的游标称为流式游标，Cursor和DictCursor游标可以一次性返回所有的数据，流式游标智能一条一条得返回查询数据，所以这类游标适用于内存低、网络带宽小、数据量大的应用场景中。

DictCursor：返回字典（Dict）格式的数据

SSCursor：流式游标返回元组（Tuple）格式数据

SSDictCursor：流式游标返回字典（Dict）格式数据

使用其他游标时，只用在cursor()方法中加入相应的参数即可

cursor = db.cursor(pymysql.cursors.SSDictCursor)

2.4 编写sql代码,执行sql代码

写一句简单地sql语句，目的是查上海和杭州在2020-09-21~2020-09-22这两天的天气，将写好的sql语句改为字符串格式并赋值给sql这个变量名，使用excute()这个方法可以通过定义好的游标来执行写好的sql语句，可以看到输出了一个数字4，代表查询出的数据集共包含4条数据。

2.5 获取返回的查询结果

使用fetchall()方法可以通过定义好的游标来获取查询出的完整数据集，并赋值给变量名cds

打印一下cds这个变量，可以看到数据已经获取到了，现在要将其变成我们常用的DataFrame格式

除了fetchall()这个方法,还有fetchone()和fetchmany(size)这两种方法可以获取返回的数据

fetchall():返回所有数据

fetchone():返回下一条数据

fetchmany(size):返回下size个数据

2.6 将获取到的数据转换成DataFrame格式

将tuple格式的cds变量转换为list，再通过pandas中的DataFrame()方法,将cds转化为DataFrame格式,并改好列名,赋值给weather变量名

输出weather看一下数据

2.7 关闭游标,关闭数据库连接

import pandas as pd
import pymysql

# 打开数据库连接
db = pymysql.connect("192.168.0.***", "root", "******", "test", charset='utf8' )
# 使用cursor()方法获取操作游标 
cursor = db.cursor()

sql = """
select * from weather_test
where create_time between '2020-09-21' and '2020-09-22'
and city in ('上海','杭州')
"""

cursor.execute(sql)
cds = cursor.fetchall()
weather = pd.DataFrame(list(cds),columns=['ID','时间','省份','城市','最高温度','最低温度','白天天气','夜间天气','风力','风向'])
cursor.close()  # 关闭游标
db.close()  # 关闭数据库连接

使用pymysql创建一个connect对象的时候，就已经和mysql之间创建了一个tcp的长连接，只要不调用这个对象的close方法，这个长连接就不会断开,就会一直占用资源，所以执行完之后别忘了关闭游标和数据库连接

以上只是最简单的使用python查询数据库的办法,其他增删改操作与此类似,大家可以自行发挥

- END -

本文为转载分享&推荐阅读，若侵权请联系后台删除

●这几个数据源网站你都知道吗！？

●10大Python数据可视化库！

后台回复“入群”即可加入小z数据干货交流群