Python操作MySQL

文章导读

阅读本文需要一定的Python基础和MySQL基础，如果阅读过程中感到吃力，可以阅读我的Python入门篇学习记录和MySQL学习记录填补知识漏洞，本文使用VS Code操作pymysql驱动，使用navicat查看数据库，实操偏多，方便大家通过例子实实在在地理解pymysql的用法。

1、Python操作MySQL的流程

1.1、初识驱动

概述：因为Python和MySQL是2套软件，所以在互相交互的时候需要一个“桥梁”。这个“桥梁”就是驱动，常用驱动如下：

①、Mysqldb又叫MySQL-python

优点：基于C开发的库，速度快。

缺点：在 Windows 平台安装不友好，经常出现失败的情况，多年不更新了，只兼容python2。

②、mysqlclient (是Mysqldb的分支)

优点：基于C开发的库，速度快，兼容python3。

缺点：编译安装可能会导致报各种错误。

③、pymysql

优点：纯 Python 实现的驱动，兼容python3，使用简单。

缺点：速度不如mysqldb。

由于pymysql上手简单，所有本文将采用pymysql库操作MySQL。

Python操作数据的流程：

1.2、操作前准备

注意：创建虚拟环境只是为了模拟真实的生产环境，如果只为学习pymysql操作数据库可以忽略前两步，直接进入第三步安装pymysql驱动。

①、首先打开vscode(pycharm也可以，pacharm创建虚拟可以参考开发环境&虚拟环境学习记录)，按快捷键Ctrl+~打开控制台的终端，首先通过如下命令创建名为mysql01_env的虚拟环境：

mkvirtualenv mysql01_env

②、接下来选择我们创建的虚拟环境：

③、最后，按快捷键Ctrl+~打开控制台的终端，通过如下命令下载pymasql驱动：

# 导入库
pip install pymysql
# 查看是否导入成功
pip list

运行结果如下：

1.3、连接数据库

概述：connect函数用于连接数据库，根据连接的数据库类型不同，该函数的参数也不同。connect函数返回Connection对象。

实操：通过pymysql库中的connect函数连接到已经创建好的mydatabase数据库(练习前一定要创建好数据库)

import  pymysql
# 获取连接
con = pymysql.connect(host="localhost",port=3306,user="root",password="root",db="mydatabase",charset="utf8");

运行结果如下(没有报错就说明我们已经连接到了mydatabase数据库)：

connect参数解析：host用于指定主机参数(可以参考我的MySQL学习记录中14.2小节中提到的主机参数选择)，port用于指定MySQL的端口号(一般都默认为3306)，user表示数据库的用户名，password表示数据库的密码，db用于指定连接到哪个数据库，charset用于设置编码格式。

1.4、获取游标

概述：cursor方法用于获取操作数据库的Cursor对象,包含了很多操作数据的方法，cursor方法属于Connection对象。

提示：从Python操作MySQL数据的流程不难发现，如果想获取游标(cursor)就要先获取连接对象(connection)，我们在上一小节的实操中已经拿到了连接对象con，接下来通过连接对象con就能获取游标。

语法：

连接对象.cursor()

实操：通过pymysql库中的connect函数连接到已经创建好的mydatabase数据库，并且获取游标。

import  pymysql
# 获取连接
con = pymysql.connect(host="localhost",port=3306,user="root",password="root",db="mydatabase",charset="utf8");
myCursor = con.cursor()

运行结果如下：

1.5、执行单条sql语句

概述：excute函数用于执行单条的sql语句，执行成功后返回受影响的行数。

语法：

execute(query,[args=None])

参数说明：

query：指要执行的sql语句，类型为字符串。

args：可选的序列或映射，用于query的参数值。如果args为序列，query中必须使用%s。

1.6、批量执行sql语句

概述：批量执行sql语句，比如批量插入数据，执行成功后返回受影响的行数。

语法：

executemany(query,args=None)

参数说明：

query：指要执行的sql语句，类型为字符串。

args：嵌套的序列或映射，用于query的参数值。

1.7、提交事务

概述：commit方法用于在修改数据库后，需要调用该方法提交对数据库的修改。

语法：

连接对象.commit()

1.8、事务回滚

概述：rollback方法用于如果修改数据库失败，一般需要调用该方法进行数据库回滚，也就是将数据库恢复成修改之前的样子。

语法：

连接对象.rollback()

2、利用Python创建数据库和表

2.1、Python创建数据库

总结：pymysql操作数据库的思路是首先通过pymysql的connect方法链接数据库，获取连接对象(Python中一切皆对象)，然后通过前面获取的连接对象获取一个能和数据库交互的游标对象，再准备要执行的sql语句，然后用游标对象调用execute(多条语句用excutemany)方法执行前面准备的sql语句，最后依次关闭游标对象和连接对象即可。

实操：通过Pymysql创建一个名为test_database的数据库。

# 导入库
import pymysql
# 链接数据库，获取连接对象con(由于是创建数据库，所以db参数就省略了)
con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8")
# 获取一个和数据库交互的工具myCursor
myCursor = con.cursor()
# 编写sql
sql = """
create database test_database default character set = 'utf8'
"""
# 执行sql
myCursor.execute(sql)
# 关闭cursor
myCursor.close()
# 关闭链接
con.close

通过navicat刷新后可以发现数据库中多了一个名为test_database的数据库：

2.2、Python创建表

概述：有了上一小节的总结使用pymysql就能信手拈来了，说白了就是用一套几乎固定的规则(pymysql的方法最多改一下不同方法的参数使用)去连接数据库达到操作数据库的效果。

实操：通过pymysql在test_database数据库中创建一个名为test_database的表，包含id，name，age，tel字段，然后为id字段设置主键自增长，name字段设置非空。

import pymysql# 获取数据库对象(由于端口默认是3306,所以这里省略了)
con = pymysql.connect(host="localhost",user="root",password="root",db="test_database",charset="utf8")
# 获取操作数据库的游标对象
cursor = con.cursor()
# 准备操作数据库的sql语句
sql = """
create table test_database(id int primary key auto_increment,name varchar(20) not null,age int,tel varchar(11)
);
"""
# 执行sql语句
cursor.execute(sql)
# 关闭游标对象
cursor.close()
# 关闭连接对象
con.close()

通过navicat发现test_database表及其字段创建成功(记得刷新一下)：

3、Python增加数据

注意：pymysql模块如果需要操作DML语句(增删改操作)，需要通过连接对象手动提交事务。

实操前说明：在前面的章节中，创建了一个名为test_database的数据库，然后在test_database数据库的下面创建了一个同名为test_database的表，下面是test_database表的字段及其数据：

实操1：根据test_database表的字段类型，通过函数实现为该表添加任意一条数据。

import pymysqldef add_oneLine(a,b,c):# 获取数据库对象con = pymysql.connect(host="localhost",port=3306,user="root",password="root",db="test_database",charset="utf8")# 获取操作数据库的游标对象cursor = con.cursor()# 准备操作数据库的sql语句(%s表示占位符,方便arg的内容依次对应)sql = "insert into test_database values(null,%s,%s,%s)"arg = (a,b,c) # 替换的数据可以用列表也可以用元组# 执行sql语句cursor.execute(sql,arg)# 由于使用了MySQL中的DML语句(增删改操作)，所以执行完操作后一定要使用连接对象.commit()提交con.commit()# 关闭游标对象cursor.close()# 关闭连接对象con.close()# 调用函数实现添加数据
add_oneLine("muxikeqi",88,"123321")

刷新后的test_database表数据如下：

实操2：根据test_database表的字段类型，通过函数实现为该表添加任意多条数据。

import pymysqldef add_manyLine(a1,b1,c1,a2,b2,c2,a3,b3,c3):# 获取数据库对象con = pymysql.connect(host="localhost",port=3306,user="root",password="root",db="test_database",charset="utf8")# 获取操作数据库的游标对象cursor = con.cursor()# 准备操作数据库的sql语句(%s表示占位符,方便arg的内容依次对应)sql = "insert into test_database values(null,%s,%s,%s)"arg = [(a1,b1,c1),[a2,b2,c2],(a3,b3,c3)] # 用于替换占位符的数据可以用列表也可以用元组# 执行sql语句cursor.executemany(sql,arg)# 由于使用了MySQL中的DML语句(增删改操作)，所以执行完操作后一定要使用连接对象.commit()提交con.commit()# 关闭游标对象cursor.close()# 关闭连接对象con.close()# 如果是本程序执行才能触发后面的语句，其他文件调用就无法运行后面的语句
if __name__ == "__main__":# 调用函数实现添加数据add_manyLine("niko",77,"321123","donk",66,"10087","zywoo",55,"10000000")

刷新后的test_database表数据如下：

4、Python更新与删除数据

注意：pymysql模块如果需要操作DML语句(增删改操作)，需要手动提交事务。

实操1：通过上一小节的实操2可以查看目前test_database表的内容，接下来通过一个函数能根据姓名(name)更改对应的手机号(tel)。

import pymysqldef update_data(tel,name):# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句sql = "update test_database set tel=%s where name=%s" # 设置更新参数(填充sql语句中的占位符%s)args = [tel,name]# 执行SQL语句cursor.execute(sql,args)# 提交事务con.commit()# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用函数update_data(tel="193931913",name="donk")

刷新后的test_database表数据如下：

实操2：通过实操1可以查看目前test_database表的数据，接下来通过一个函数能根据姓名(name)删除对应整条信息。

import pymysqldef delete_data(args):# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句sql = "delete from test_database where name=%s" # 执行SQL语句cursor.execute(sql,args)# 提交事务con.commit()# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 提前设置更新参数args=["zywoo"]# 调用删除函数delete_data(args)

刷新后的test_database表数据如下：

5、Python查询数据

概述：Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

方法：

fetchone()：该方法获取下一个查询结果集，并且结果集是一个对象。

fetchall()：接收全部的返回结果行。

fetchmany(num)：查询指定条数的记录，默认查询一条数据，如果指定的数值大于表中的数据也不会报错，而是显示表中所有的数据。

rowcount：这是一个只读属性，并返回执行execute()方法后影响的行数。

实操1：通过Python查询test_database数据库下test_database表的所有数据并打印出来。

import pymysqldef query_data():# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句sql = "select * from test_database" # 执行SQL语句,execute方法会缓存查询的结果cursor.execute(sql)# 利用fetchone或者fetchall获取查询结果并打印print(cursor.fetchall())# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用删除函数query_data()

运行结果如下：

实操2：通过Python查询test_database数据库下test_database表中一共有多少条数据。

import pymysqldef query_data():# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句sql = "select * from test_database" # 执行SQL语句,execute方法会缓存查询的结果cursor. Execute(sql)# 利用rowcount属性查询表中的数据条数print(cursor.rowcount)# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用删除函数query_data()

运行结果如下：

6、SQL注入查询漏洞

概述：SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

实操前准备：在test_database数据库下创建一个名为test_table的表，SQL命令如下：

-- 创建名为test_table的表
CREATE TABLE test_table (id int PRIMARY KEY auto_increment,user_name varchar(30) NOT NULL ,password varchar(50) NOT NULL
);-- 插入三条数据
INSERT INTO test_table VALUES(null,"muxikeqi","25fgfag4123123t");
INSERT INTO test_table VALUES(null,"niko","456fag4ga654c");
INSERT INTO test_table VALUES(null,"donk","9at877ashrah89a");

字段及其数据如下：

实操1：通过本实操演示登录情况下的SQL注入。

import pymysqldef query_data(user,pwd):# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句,用字符串将传入函数的变量拼接成后面的语句 select * from test_table where user_name="user" and password="pwd";sql = f'select user_name,password from test_table where user_name="{user}" and password="{pwd}";'print("拼接的sql语句为:",sql)# 执行SQL语句,execute方法会缓存查询的结果cursor.execute(sql)print("查询到的结果为:",cursor.fetchone())# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用函数query_data(user='muxikeqi" #',pwd="")

运行结果如下：

总结1：SQL注入就是通过程序利用拼接字符串实现账号密码判断的漏洞，使得用户在输入用户名时只要知道用户名，输入时用户名后添加#号屏蔽后面理应生效的代码实现SQL注入。

实操2：根据实操1中了解到的SQL注入原理，尝试为该函数解决SQL注入的风险。

import pymysqldef query_data(user,pwd):# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句,用字符串将传入函数的变量拼接成后面的语句 select * from test_table where user_name="user" and password="pwd";sql = 'select * from test_table where user_name=%s and password=%s'# 执行SQL语句,execute方法会缓存查询的结果cursor.execute(sql,[user,pwd])print("查询到的结果为:",cursor.fetchone())# 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用函数query_data(user='muxikeqi',pwd="")

运行结果如下：

总结2：其实解决SQL注入的办法也很简单，就是不要用字符串拼接来判断用户名和密码，让用户没有办法通过#号来屏蔽理应生效的代码。

7、SQL异常处理

概述：当利用pymysql操作数据库时，如果产生报错程序就无法正常关闭与数据库的链接，因此需要用到try...except...finally...结构对程序进行控制保证程序正常关闭与数据库的链接。

实操：通过pymysql模块对test_mydatabase数据库下test_table表进行查询(保证查询语句中包含错误)，然后通过try...except...finally...结构对程序进行控制保证程序正常关闭与数据库的链接。

import pymysqldef query_data():# 链接数据库con = pymysql.connect(host="localhost",port=3306,user="root",password="root",charset="utf8",db="test_database")# 获取游标 cursor = con.cursor()# 编写SQL语句sql = "select * from test_table where" try:# 执行SQL语句,execute方法会缓存查询的结果cursor.execute(sql)# 利用fetchone或者fetchall获取查询结果并打印print(cursor.fetchall())except Exception as e:print("报错信息为:",e)finally:print("即将关闭游标和链接")    # 关闭游标cursor.close()# 关闭链接con.close()if __name__ == "__main__":# 调用删除函数query_data()

运行结果如下：

从运行结果可知，即使程序产生了报错，通过try...except...finally结构的控制任然能正常关闭链接。

8、开发工具类

8.1、封装链接的建立与关闭

概述：pymysql操作mysql虽然简单，但每次都要链接数据库，获取游标，关闭游标，关闭链接。要如何避免这些重复的操作，提高开发效率呢？此时我们就可以考虑编写工具类，将公共的内容封装起来。

实操：将获取链接，关闭链接，获取游标，关闭游标的操作封装到一个名为DBUtil的工具类中，为了灵活链接数据库，将链接数据库的参数单独保存，方便修改。

import pymysqlclass DBUtil:# 单独保存链接的参数,可以将其保存到特定文件中，直接调用文件获取也可config={'host':"localhost",'user':"root",'passwd':"root",'db':"test_database",'charset':"utf8",'port':3306}def __init__(self) -> None:"""构造函数用于获取链接和游标"""# **指字典对象self.con = pymysql.connect(**DBUtil.config)self.cursor = self.con.cursor()def close(self) -> None: """该函数用于关闭链接和游标(关闭前需要判断是否存在)"""# 存在游标就关闭if self.cursor:self.cursor.close()# 存在链接就关闭if self.con:self.con.close()if __name__ == "__main__":# 创建链接db = DBUtil();# 关闭链接db.close();

运行后不报错就说明成功了。

8.2、封装DML操作

实操：基于上一小节的实操继续封装DML操作。

import pymysqlclass DBUtil:# 单独保存链接的参数,可以将其保存到特定文件中，直接调用文件获取也可config={'host':"localhost",'user':"root",'passwd':"root",'db':"test_database",'charset':"utf8",'port':3306}def __init__(self) -> None:"""构造函数用于获取链接和游标"""# **指字典对象self.con = pymysql.connect(**DBUtil.config)self.cursor = self.con.cursor()def close(self) -> None: """该函数用于关闭链接和游标(关闭前需要判断是否存在)"""# 存在游标就关闭if self.cursor:self.cursor.close()# 存在链接就关闭if self.con:self.con.close()def dml(self,sql,args):"""本函数用于封装Mysql的DML语句,用于实现数据的增删改"""# 进行DML语句操作时一定要注意报错导致无法正常关闭链接try:# 执行sql中的DML语句self.cursor.execute(sql,args)# DML操作一定要通过链接对象提交事务self.con.commit()except Exception as e:print("存在错误,错误信息如下:",e)# 回滚前先判断链接还是否存在if self.con:# 回滚到原状态self.con.rollback()# 使用try...except...finally...最重要的作用就是保证链接正常关闭finally:print("即将关闭链接")self. Close()if __name__ == "__main__":# 获取链接对象db = DBUtil()# 准备sql语句及其对应的参数sql = "insert into test_table values(0,%s,%s)"args = ('shiro','jfioangioang')# 运行sql语句并关闭链接db.dml(sql,args)

运行后的test_database数据库下test_table表的数据如下：

通过实操测试可以发现我们的DML语句封装较为成功，大家可以自己尝试错误的sql语句是否还能正确的关闭链接。

8.3、封装DQL操作

概述：前面的两个小节中封装了数据库的开启和关闭，游标的开启和关闭，对数据进行增删改的DML操作，本小节将封装用于查询的DQL操作，即查询操作，查询分为单条查询和多条查询。

实操1：在上一小节的基础上继续封装DQL的单条数据查询操作。

# 该函数放在DBUtil类中
def query_one(self,sql,args):"""本函数用于查询单条数据"""try:self.cursor.execute(sql,args)re = self.cursor.fetchone()return reexcept Exception as e:print("存在错误,错误信息如下:",e)finally:print("即将关闭链接")self.close()

测试代码如下：

if __name__ == "__main__":# 获取链接对象db = DBUtil()# 准备sql语句及其对应的参数sql = "select * from test_table where id=%s;"# 运行sql语句并关闭链接print(db.query_one(sql,2))

运行结果如下：

实操2：在实操1的基础上添加多行查询操作的封装。

# 完整工具类代码如下：
import pymysqlclass DBUtil:# 单独保存链接的参数,可以将其保存到特定文件中，直接调用文件获取也可config={'host':"localhost",'user':"root",'passwd':"root",'db':"test_database",'charset':"utf8",'port':3306}def __init__(self) -> None:"""构造函数用于获取链接和游标"""# **指字典对象self.con = pymysql.connect(**DBUtil.config)self.cursor = self.con.cursor()def close(self) -> None: """该函数用于关闭链接和游标(关闭前需要判断是否存在)"""# 存在游标就关闭if self.cursor:self.cursor.close()# 存在链接就关闭if self.con:self.con.close()def dml(self,sql,args):"""本函数用于封装Mysql的DML语句,用于实现数据的增删改"""# 进行DML语句操作时一定要注意报错导致无法正常关闭链接try:# 执行sql中的DML语句self.cursor.execute(sql,args)# DML操作一定要通过链接对象提交事务self.con.commit()except Exception as e:print("存在错误,错误信息如下:",e)# 回滚前先判断链接还是否存在if self.con:# 回滚到原状态self.con.rollback()# 使用try...except...finally...最重要的作用就是保证链接正常关闭finally:print("即将关闭链接")self.close()def query_one(self,sql,args):"""本函数用于查询单条数据"""try:self.cursor.execute(sql,args)re = self.cursor.fetchone()return reexcept Exception as e:print("存在错误,错误信息如下:",e)finally:print("即将关闭链接")self.close()def query_many(self,sql):"""本函数用于查询所有数据"""try:self.cursor.execute(sql)# 获取结果并返回数据return self.cursor.fetchall()except Exception as e:print("存在错误,错误信息如下:",e)finally:print("即将关闭链接")self.close()if __name__ == "__main__":# 获取链接对象db = DBUtil()# 准备sql语句及其对应的参数sql = "select * from test_table;"# 运行sql语句并关闭链接print(db.query_many(sql))

运行结果如下：