Python与数据库交互—浅述pymysql

本文作者：陈   鼎，王   彤，中南财经政法大学统计与数学学院

本文编辑：尚晨曦

技术总编：王子一

Stata&Python云端课程来啦！

      寒雪梅中尽，春风柳上归。为了感谢大家长久以来的支持和信任，爬虫俱乐部为大家送福利啦！！！Stata&python特惠课程双双上线腾讯课堂～原价2400元的Python编程培训课程，现在仅需100元，详情请查看推文《Python云端课程福利大放送！0基础也能学~》；原价600元的正则表达式课程，现在仅需49.9元，详情请查看推文《与春天有个约会，爬虫俱乐部重磅推出正则表达式网络课程！》；原价600元的基本字符串函数课程，现在仅需49.9元，更多信息请查看推文《与春天有个约会，爬虫俱乐部重磅推出基本字符串函数网络课程》；原价600元的网络爬虫课程，现在仅需49.9元，更多信息请查看推文《与春天有个约会，爬虫俱乐部重磅推出网络爬虫专题课程》；原价600元的文本分析课程，现在仅需49.9元，更多信息请查看推文《与春天有个约会，爬虫俱乐部重磅推出文本分析网络课程》。变的是价格，不变的是课程质量和答疑服务。对报名有任何疑问欢迎在公众号后台和腾讯课堂留言哦！

pymysql库

pymysql库是一个利用Python终端操作MySQL数据库的模块。通过pymysql，我们可以利用python语言实现对数据库及表的提取、读写、删除数据等基本操作。

一

安装

pymysql库的安装与其它模块的安装一致，在anaconda或cmd终端上输入以下命令即可：

pip install pymysql

二

操作MySQL的基本步骤

在Python上操作MySQL的流程如图所示：

容易看出，在书写代码对数据库进行操作时，代码的头部和尾部是不变的，我们仅需要根据不同的需求，在中间步骤书写不同的SQL语句即可。

三代码演示

01  创建数据库连接

首先，导入我们所需要的库：

from pymysql import *

随后，创立一个数据库连接，此时我们需要调用pymysql中的connect类，并返回一个对象：

conn = connect(host,port,user,passwd,database,charset)

其中，connect中的具体参数及意义如下表所示。

参数	传入类型	参数说明
host	字符串str	MySQL的服务器地址，如果是本地则为'localhost'
port	整型int	MySQL的服务器端口号，默认为3306
user	字符串str	用户名
passwd	字符串str	密码
database	字符串str	需要连接的数据库名称
charset	字符串str	使用的字符集，如'utf8','gbk'等

需要注意，上述代码能正常运行的前提是：

1.您的电脑上已经安装了MySQL数据库，并且服务已经启动。

2.您有可以连接该数据库的用户名和密码。

3.您有权限对指定database进行操作。

为便于演示，笔者在本地导入了名为myemployees的数据库，数据库文件回复“myemployees数据库”即可获取。该数据库中存放有departments、employees、jobs、locations四张表。若要使用Python连接该数据库并获取表信息，执行以下代码即可：

conn = connect(host='localhost',port=3306,user='root',passwd='ab1234',database='myemployees',charset='utf8') 

02 创建游标对象

关闭游标对象，断

该步骤十分简单，调用cursor方法即可返回一个游标对象：

cs1 = conn.cursor()

03 执行数据库操作

要对数据库中执行增删改查等基本操作，只需调用以下方法即可：

cs1.execute('SQL语句')

上述Python代码将返回符合括号内SQL语句的条数。

(1) 查询操作

首先，MySQL数据库中的查询语句为：

select
from 表1
[join type] join 表2
on 连接条件
where 筛选条件
group by 分组条件
having 分组后筛选条件
order by 字段排序
limit 限制显示条目数;

比如，我们想要获取每个部门的信息与部门的平均工资，那么：

# 1.查询部门的平均工资与部门编号
SELECT AVG(salary) AS 平均工资,department_id
FROM employees
GROUP BY department_id;
# 2.连接上表，并查询出部门相关信息
SELECT d.*,平均工资
FROM departments AS d 
INNER JOIN (
SELECT AVG(salary) AS 平均工资,department_id
FROM employees
GROUP BY department_id
) AS avg_dep
ON avg_dep.department_id = d.department_id;

我们将上述代码复制到execute方法的括号中，就可以通过Python终端执行SQL语句。值得一提的是，在提取所需数据时，可以根据实际需要，使用以下三种方法：

方法名	传入参数	返回结果
fetchone()	不需传参	返回结果集的下一行，当多次执行时会依次返回结果集中的数据。使用fetchone将返回一个元组，遍历该元组将得到该条数据的每一个字段。
fetchmany()	int	返回指定个数的结果集中的数据。使用fetchmany将返回一个嵌套的二维元组。遍历该元组会得到表中每一行的数据。
fetchall()	不需传参	返回所有符合条件的结果集。

使用fetchone的效果：

cs1.execute("""SELECT d.*,平均工资
FROM departments AS d 
INNER JOIN (
  SELECT AVG(salary) AS 平均工资,department_id
  FROM employees
  GROUP BY department_id
) AS avg_dep
ON avg_dep.department_id = d.department_id;""")

for message in cs1.fetchone():
    print(message)

得到的结果为第一行数据的每个字段。

使用fetchmany()的效果：

for message in cs1.fetchmany(5):
    print(message)

得到的结果为结果集的前五行数据。

使用fetchall()的效果：

for message in cs1.fetchall():
    print(message)

得到的结果为结果集的所有数据。

(2) 添加操作

在mysql语句中，向表中插入数据的SQL语句为：

insert into 表名(字段名)
values(数据);

比如，我们要在jobs表中添加"D_A"职位，job_title为"Data_Analyst"，min_salary为6000，max_salary为10000。那么：

insert into jobs(job_id,job_title,min_salary,max_salary)
values("D_A","Data_Analyst",6000,10000);

在Python中，涉及对表的增删改时，需要在execute的基础上再对数据库对象进行commit()操作，若想撤销该操作，可以调用回滚方法rollback()。

cs1.execute("""insert into jobs(job_id,job_title,min_salary,max_salary)
values("D_A","Data_Analyst",6000,10000);""")
conn.commit() # 执行语句
conn.rollback() # 回滚

(3) 删除操作

在mysql语句中，删除表中某一行的操作为：

delete from 表名
where 筛选条件;

若要把刚才的D_A职位删除，则：

delete from jobs
where job_id = 'D_A';

同理，在Python中操作如下：

cs1.execute("""delete from jobs
where job_id = 'D_A';""")
conn.commit() # 执行语句
conn.rollback() # 回滚

(4) 修改操作

在mysql中，修改字段的操作如下：

update 表名
set 字段名 = 值
where 筛选条件;

若要将D_A职位的最低工资改成8000，那么

update jobs
set min_salary = 8000
where job_id = 'D_A';

在Python中也是一样：

cs1.execute("""update jobs
set min_salary = 8000
where job_id = 'D_A';""")
conn.commit() # 执行语句
conn.rollback() # 回滚

04 关闭游标对象，断开数据库连接

调用close()方法即可断开Python与数据库的连接。需注意的是，我们需先关闭游标对象，再断开数据库连接。代码如下：

cs1.close()
conn.close()

四总结

pymsql的步骤分为以下三步：

第一步，连接数据库，获取游标对象；

第二步，根据实际需要对数据库进行操作；

第三步，关闭游标对象，断开数据库连接。

你学会了吗？

最后，我们为大家揭秘雪球网(https://xueqiu.com/)最新所展示的沪深证券和港股关注人数增长Top10。

欢迎大家点赞、评论、转发呦~

对我们的推文累计打赏超过1000元，我们即可给您开具发票，发票类别为“咨询费”。用心做事，不负您的支持！

往期推文推荐

关于我们 

微信公众号“Stata and Python数据分析”分享实用的Stata、Python等软件的数据处理知识，欢迎转载、打赏。我们是由李春涛教授领导下的研究生及本科生组成的大数据处理和分析团队。

此外，欢迎大家踊跃投稿，介绍一些关于Stata和Python的数据处理和分析技巧。

投稿邮箱：[e-mail]投稿要求：
1）必须原创，禁止抄袭；
2）必须准确，详细，有例子，有截图；
注意事项：
1）所有投稿都会经过本公众号运营团队成员的审核，审核通过才可录用，一经录用，会在该推文里为作者署名，并有赏金分成。
2）邮件请注明投稿，邮件名称为“投稿+推文名称”。
3）应广大读者要求，现开通有偿问答服务，如果大家遇到有关数据处理、分析等问题，可以在公众号中提出，只需支付少量赏金，我们会在后期的推文里给予解答。