文章导读
阅读本文需要一定的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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
def 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 pymysql
class 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 pymysql
class 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 re
except 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 pymysql
class 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 re
except 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))
运行结果如下：
1912
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
