Python操作MySQL

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

沐曦可期

1652人浏览 · 2024-05-19 15:51:39

沐曦可期 · 2024-05-19 15:51:39 发布

文章导读

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))

运行结果如下：