Python 深入探讨pymysql与dbutils模块操作MySQL数据库的各项关键技术

需要休息的KK.

于 2024-03-03 18:30:00 发布

阅读量2.4k

点赞数 78

分类专栏： python 第三方库探索代码先拿去用不懂再来问文章标签： python mysql 数据库连接池防止SQL注入学习个人开发 pycharm

本文链接：https://blog.csdn.net/weixin_54217348/article/details/136389856

版权

python 同时被 3 个专栏收录

51 篇文章 2 订阅

订阅专栏

第三方库探索

46 篇文章 1 订阅

订阅专栏

代码先拿去用不懂再来问

6 篇文章 0 订阅

订阅专栏

文章开篇

Python的魅力，犹如星河璀璨，无尽无边；人生苦短、我用Python！

PyMySQL简介

PyMySQL模块是一个用于连接MySQL数据库的第三方模块；
PyMySQL模块允许Python程序与MySQL数据库进行交互，可以执行SQL语句、获取查询结果等；
它提供了许多高级特性，如事务管理、连接池、数据类型转换等，使得开发数据驱动的应用程序变得更加高效和便捷；

PyMySQL安装

PyMySQL是一个纯Python编写的MySQL驱动程序，让我们可以用Python语言操作MySQL数据库；
在使用之前，必须额外并下载安装“pymysql”模块，才能使用这个模块对Mysql数据库进行操作；

安装命令

pip install PyMySQL

PyMySQL使用

引入了pymysql模块后，就可以对Mysql进行操作；

import pymysql

PyMySQL模块建立连接的方式，是通过connect函数；
它有如下参数：

参数类型	参数名称	参数说明
host	字符串	MySQL 服务器的地址。默认值是 `'localhost'`
user	字符串	连接的用户名称
password	字符串	连接的用户密码
database	字符串	连接的数据库名
port	整数	MySQL 服务器的端口号。默认值是 `0`，表示使用默认端口（通常是 `3306`）
unix_socket	字符串	Unix 套接字文件的路径。当使用 Unix 套接字连接时，可以使用此参数
charset	字符串	使用的字符集。默认是 `'utf8'`
client_flag	整数	客户端标志。这是一个用于控制客户端行为的位掩码
cursorclass	类	自定义的游标类。默认是 `Cursor`
init_command	字符串	连接成功后执行的初始 SQL 命令
read_default_file	字符串	读取的默认配置文件
use_unicode	布尔值	是否使用 Unicode 编码。默认是 `True`
autocommit	布尔值	是否自动提交。默认是 `False`
ssl	字典	SSL 配置。例如：`{'ca': '/path/to/ca-cert.pem'}`
local_infile	布尔值	是否允许 `LOAD DATA LOCAL INFILE` 命令。默认是 `False`
auth_plugin	字符串	身份验证插件。例如，‘mysql_native_password’
server_public_key	字符串	服务器公钥的值
sha256_password_private_key	字符串	SHA-256 密码插件的私钥
sql_mode	字符串	SQL 模式
program_name	字符串	客户端程序的名称
ssl_ca	字符串	CA 证书的路径
ssl_cert	字符串	SSL 证书的路径
ssl_key	字符串	SSL 密钥的路径
ssl_cipher	字符串	SSL 加密套件
ssl_verify_server_cert	布尔值	是否验证服务器证书
ssl_crlf	布尔值	是否在 SSL 连接中使用 CRLF 换行符
ssl_ca_path	字符串	CA 证书目录的路径
ssl_check_hostname	布尔值	是否检查 SSL 主机名

看上去connect函数的参数如此繁多是否有些许的胆怯？请不要害怕；
通常我们只需要提供 host、port、user、password 和 database 参数来建立基本的连接；
其他的参数只会特定的场景下，获取特殊的需求配置中才会涉及；

import pymysql

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)   # True

连接建立成功后返回一个数据库连接对象；
我们需要通过连接对象再建立一个cursor游标对象，所有的数据库操作都由游标来执行；

import pymysql
from pymysql import cursors

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)   # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor) # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 创建游标时可以指定返回数据的格式，这里指定返回一个字典，key是字段名，value是对应的数据
# cursor = conn.cursor(cursors.DictCursor)
# print(type(cursor), cursor) # <class 'pymysql.cursors.DictCursor'> <pymysql.cursors.DictCursor object at 0x7f954010aee0>

查询操作

1.fetchall 查询所有数据

import pymysql
from pymysql import cursors

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)   # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
# cursor = conn.cursor()
# print(type(cursor), cursor) # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 创建游标时可以指定返回数据的格式，这里指定返回一个字典，key是字段名，value是对应的数据
cursor = conn.cursor(cursors.DictCursor)
print(type(cursor), cursor) # <class 'pymysql.cursors.DictCursor'> <pymysql.cursors.DictCursor object at 0x7f954010aee0>

# 查询sql语句
sql = "select * from student"

# 执行sql使用execute()执行单条语句或executemany()执行多条语句
select_count = cursor.execute(sql)
print(select_count)    # 15（默认返回影响条数，查询返回查询到的条数，修改返回被修改的条数）

# 从游标中获取数据，fetchall()获取所有数据，fetchone()获取一条数据，fetchmany()获取指定数量的数据
select_data = cursor.fetchall()
for data in select_data:
    print(data)
    # cursor = conn.cursor()默认游标返回结果展示
    # ('101', '小兰', '女', datetime.datetime(2000, 11, 11, 0, 0), '95033')
    # ('102', '小明', '男', datetime.datetime(1985, 11, 11, 0, 0), '95031')
    # ('103', '小王', '女', datetime.datetime(1996, 11, 11, 0, 0), '95033')
    # ('104', '老刘', '男', datetime.datetime(1986, 11, 11, 0, 0), '95033')
    # ('105', '林达浪', '男', datetime.datetime(1987, 11, 11, 0, 0), '95031')
    # ('106', '小菜', '男', datetime.datetime(1978, 11, 11, 0, 0), '95031')
    # ('107', '叶盈盈', '女', datetime.datetime(2001, 11, 11, 0, 0), '95033')
    # ('108', '慕容雪', '女', datetime.datetime(2002, 11, 11, 0, 0), '95031')
    # ('109', '洪绿灯', '男', datetime.datetime(2003, 11, 11, 0, 0), '95031')
    # ('110', '王铁锤', '男', datetime.datetime(2004, 11, 11, 0, 0), '95033')
    # ('111', '赵德住', '男', datetime.datetime(2004, 7, 11, 0, 0), '95034')
    # ('112', '乌鱼子', '女', None, '95031')
    # ('113', '感冒的大马哈', '男', datetime.datetime(1985, 11, 10, 16, 0), '95033')
    # ('114', '小白龙', '男', datetime.datetime(2023, 11, 22, 1, 11, 19), '95033')
    # ('115', '小玲珑', '女', None, '95031')

    # cursor = conn.cursor(cursors.DictCursor)游标返回结果展示
    # {'sno': '101', 'sname': '小兰', 'ssex': '女', 'sbirthday': datetime.datetime(2000, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '102', 'sname': '小明', 'ssex': '男', 'sbirthday': datetime.datetime(1985, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '103', 'sname': '小王', 'ssex': '女', 'sbirthday': datetime.datetime(1996, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '104', 'sname': '老刘', 'ssex': '男', 'sbirthday': datetime.datetime(1986, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '105', 'sname': '林达浪', 'ssex': '男', 'sbirthday': datetime.datetime(1987, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '106', 'sname': '小菜', 'ssex': '男', 'sbirthday': datetime.datetime(1978, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '107', 'sname': '叶盈盈', 'ssex': '女', 'sbirthday': datetime.datetime(2001, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '108', 'sname': '慕容雪', 'ssex': '女', 'sbirthday': datetime.datetime(2002, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '109', 'sname': '洪绿灯', 'ssex': '男', 'sbirthday': datetime.datetime(2003, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '110', 'sname': '王铁锤', 'ssex': '男', 'sbirthday': datetime.datetime(2004, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '111', 'sname': '赵德住', 'ssex': '男', 'sbirthday': datetime.datetime(2004, 7, 11, 0, 0), 'sclass': '95034'}
    # {'sno': '112', 'sname': '乌鱼子', 'ssex': '女', 'sbirthday': None, 'sclass': '95031'}
    # {'sno': '113', 'sname': '感冒的大马哈', 'ssex': '男', 'sbirthday': datetime.datetime(1985, 11, 10, 16, 0), 'sclass': '95033'}
    # {'sno': '114', 'sname': '小白龙', 'ssex': '男', 'sbirthday': datetime.datetime(2023, 11, 22, 1, 11, 19), 'sclass': '95033'}
    # {'sno': '115', 'sname': '小玲珑', 'ssex': '女', 'sbirthday': None, 'sclass': '95031'}

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

2.fetchmany 查询指定数量数据

import pymysql
from pymysql import cursors

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)   # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
# cursor = conn.cursor()
# print(type(cursor), cursor) # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 创建游标时可以指定返回数据的格式，这里指定返回一个字典，key是字段名，value是对应的数据
cursor = conn.cursor(cursors.DictCursor)
print(type(cursor), cursor) # <class 'pymysql.cursors.DictCursor'> <pymysql.cursors.DictCursor object at 0x7f954010aee0>

# 查询sql语句
sql = "select * from student"

# 执行sql使用execute()执行单条语句或executemany()执行多条语句
select_count = cursor.execute(sql)
print(select_count)    # 15（默认返回影响条数，查询返回查询到的条数，修改返回被修改的条数）

# 从游标中获取数据，fetchall()获取所有数据，fetchone()获取一条数据，fetchmany()获取指定数量的数据
select_data = cursor.fetchmany(5)
for data in select_data:
    print(data)
    # fetchmany() 查询指定数量结果展示
    # {'sno': '101', 'sname': '小兰', 'ssex': '女', 'sbirthday': datetime.datetime(2000, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '102', 'sname': '小明', 'ssex': '男', 'sbirthday': datetime.datetime(1985, 11, 11, 0, 0), 'sclass': '95031'}
    # {'sno': '103', 'sname': '小王', 'ssex': '女', 'sbirthday': datetime.datetime(1996, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '104', 'sname': '老刘', 'ssex': '男', 'sbirthday': datetime.datetime(1986, 11, 11, 0, 0), 'sclass': '95033'}
    # {'sno': '105', 'sname': '林达浪', 'ssex': '男', 'sbirthday': datetime.datetime(1987, 11, 11, 0, 0), 'sclass': '95031'}
    
# 关闭游标
cursor.close()

# 关闭连接
conn.close()

3.fetchone 查询一条数据

import pymysql
from pymysql import cursors

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)   # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
# cursor = conn.cursor()
# print(type(cursor), cursor) # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 创建游标时可以指定返回数据的格式，这里指定返回一个字典，key是字段名，value是对应的数据
cursor = conn.cursor(cursors.DictCursor)
print(type(cursor), cursor) # <class 'pymysql.cursors.DictCursor'> <pymysql.cursors.DictCursor object at 0x7f954010aee0>

# 查询sql语句
sql = "select * from student"

# 执行sql使用execute()执行单条语句或executemany()执行多条语句
select_count = cursor.execute(sql)
print(select_count)    # 15（默认返回影响条数，查询返回查询到的条数，修改返回被修改的条数）

# 从游标中获取数据，fetchall()获取所有数据，fetchone()获取一条数据，fetchmany()获取指定数量的数据
select_data = cursor.fetchone()
print(select_data)

# fetchone() 查询一条结果展示
# {'sno': '101', 'sname': '小兰', 'ssex': '女', 'sbirthday': datetime.datetime(2000, 11, 11, 0, 0), 'sclass': '95033'}

    
# 关闭游标
cursor.close()

# 关闭连接
conn.close()

修改操作

特别强调：增、删、改操作均需要使用数据库连接对象进行commit提交，才会生效；

1.单条修改

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 修改sql语句
sql = "update student set sname = '楼兰' where sno = 101"
# 使用execute函数执行单条sql语句
update_count = cursor.execute(sql)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()
print(update_count)  # 1（返回被修改的条数，我们这个sql语句只会修改一个，成功则返回1，失败则返回0）

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

2.批量修改

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

update_data = [
    ("小兰", "101"),
    ("小明", "102"),
    ("小王", "103"),
    ("老刘", "104")
]

# 构造 SQL 语句，使用占位符 %s，update_data中的数据会按照顺序填充
sql = "update student set sname = %s where sno = %s"
# 使用executemany函数执行多条sql语句
update_count = cursor.executemany(sql, update_data)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()

print(update_count)  # 4

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

新增操作

特别强调：增、删、改操作均需要使用数据库连接对象进行commit提交，才会生效；

1.单条新增

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 新增sql语句
sql = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('116', '小白马', '女', NULL, '95035')"
# 使用execute函数执行单条sql语句
insert_count = cursor.execute(sql)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()
print(insert_count)  # 1（返回新增条数）

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

2.批量新增


import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

insert_data = [
    ("117", '小白马1', '女', None, '95035'),
    ("118", '小白马2', '女', None, '95035'),
    ("119", '小白马3', '女', None, '95035'),
    ("120", '小白马4', '女', None, '95035')
]
# 构造 SQL 语句，使用占位符 %s，update_data中的数据会按照顺序填充
sql = "insert into student(sno, sname, ssex, sbirthday, sclass) values (%s, %s, %s, %s, %s)"
# 使用executemany函数执行多条sql语句
insert_count = cursor.executemany(sql, insert_data)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()

print(insert_count)  # 4

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

删除操作

特别强调：增、删、改操作均需要使用数据库连接对象进行commit提交，才会生效；

1.单条删除

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 删除sql语句
sql = "delete from student where sno = '116' and sname = '小白马'"
# 使用execute函数执行单条sql语句
delete_count = cursor.execute(sql)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()
print(delete_count)  # 1

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

2.批量删除

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

delete_data = [
    ("117", '小白马1'),
    ("118", '小白马2'),
    ("119", '小白马3'),
    ("120", '小白马4')
]
# 构造 SQL 语句，使用占位符 %s，update_data中的数据会按照顺序填充
sql = "delete from student where sno = %s and sname = %s"
# 使用executemany函数执行多条sql语句
delete_count = cursor.executemany(sql, delete_data)

# 增/删/改均需要使用数据库连接对象进行commit提交
conn.commit()

print(delete_count)  # 4

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

事物与回滚

前面我们说到，涉及增、删、改的操作都需要commit，这个动作就属于事物的提交；
当执行的sql语句发生意外错误时，如主键重复、插入数据不匹配时则会报错，那么使用rollback进行回滚，撤销事物中的所有sql语句的执行就属于事物的回滚；

import pymysql

host = "10.211.55.3"  # 目标数据库的ip地址
port = 3306  # 目标数据库的端口号
username = "root"  # 目标数据库的用户名称
password = "root"  # 目标数据库的用户密码
database = "select_study"  # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

try:
    # 新增sql语句
    sql1 = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('116', '小白马1', '女', NULL, '95035')"
    sql2 = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('116', '小白马2', '女', NULL, '95035')"
    sql3 = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('118', '小白马3', '女', NULL, '95035')"
    sql4 = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('119', '小白马4', '女', NULL, '95035')"
    sql5 = "insert into student(sno, sname, ssex, sbirthday, sclass) values ('120', '小白马5', '女', NULL, '95035')"

    # 执行新增sql语句到数据库
    cursor.execute(sql1)    # 使用execute函数执行sql1语句
    cursor.execute(sql2)    # 使用execute函数执行sql2语句
    cursor.execute(sql3)    # 使用execute函数执行sql3语句
    cursor.execute(sql4)    # 使用execute函数执行sql4语句
    cursor.execute(sql5)    # 使用execute函数执行sql5语句

    # 提交事务
    # 如果五条sql语句全部正确，则可以全部新增成功
    # 如果五条sql语句有任何一条错误，则全部新增失败（回滚）
    # 已知上述sql2这条sql语句中，sno是主键，但主键不可重复，现与sql1重复肯定会失败
    conn.commit()   # 提交后会导致PRIMARY异常

except Exception as e:
    # 回滚事务
    conn.rollback()
    print('事务处理失败：', e) # 事务处理失败： (1062, "Duplicate entry '116' for key 'student.PRIMARY'")
finally:
    # 关闭游标
    cursor.close()
    # 关闭连接
    conn.close()

print("新增成功条数：", cursor.rowcount)   # 0，很符合事物的一致性，要么全部成功，要么全部失败

防止SQL注入

1.SQL注入的可能

import pymysql

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 模拟用户输入
user_input1 = "赵德柱"
sql = f"select * from student where sname = '{user_input1}'"
print("sql语句：", sql)  # sql语句： select * from student where sname = '赵德柱'
print("查询结果：", cursor.execute(sql))     # 查询结果： 1

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

已知user_input1数据存在数据库中，所以查询结果为 1
如果用户传递的是**’ OR ‘1’ = ‘1’ --;**，又或是其他更危险的注入呢？

import pymysql

host = "10.211.55.3"        # 目标数据库的ip地址
port = 3306                 # 目标数据库的端口号
username = "root"           # 目标数据库的用户名称
password = "root"           # 目标数据库的用户密码
database = "select_study"   # 目标数据库的名称

# 建立数据库连接
conn = pymysql.connect(host=host, port=port, user=username, password=password, database=database)
print("检查是否连接成功：", conn.open)  # True

# 创建游标（conn是数据库连接对象，我们需要通过它创建游标cursor，后需要的数据操作都会跟它有关）
cursor = conn.cursor()
print(type(cursor), cursor)  # <class 'pymysql.cursors.Cursor'> <pymysql.cursors.Cursor object at 0x7f954010aeb0>

# 模拟用户输入
user_input2 = "' OR '1' = '1' --'"
sql = f"select * from student where sname = '{user_input2}'"
print("sql语句：", sql)  # sql语句： select * from student where sname = '' OR '1' = '1' --''
print("查询结果：", cursor.execute(sql))     # 查询结果： 15

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

查询结果为 15，可是数据库中并没有sname是"’ OR ‘1’ = ‘1’ --'"这么一串东西的呀
原因是**‘1’ = '1’始终为真**
也就是说where条件后面会变成：sname = 空或 1 = 1
那么这个条件必然是成立，因为sname = 空不存在，但是1=1是恒成立的；

因此可以说明使用字符串格式化的方式拼接用户输入的数据存在sql注入的可能；
应该避免使用字符串的直接拼接，并采用参数化查询或预处理等安全措践。

数据库连接池

虽然使用参数化查询可以防止SQL注入，但在多线程环境下频繁地创建和释放数据库连接可能会导致性能问题。
为了解决这个问题，我们可以使用数据库连接池来管理和复用数据库连接，从而提高程序的性能和效率。
连接池可以确保在多线程环境下，每个线程都能够快速地获取到可用的数据库连接，并且避免了频繁创建和释放连接的开销。

DBUtils模块简介

DBUtils是Python的一个用于实现数据库连接池的模块；
此连接池有两种连接模式：

为每个线程创建一个连接，线程即使调用了close方法，也不会关闭，只是把连接重新放到连接池，供自己线程再次使用。当线程终止时，连接才会自动关闭；
创建一批连接到连接池，供所有线程共享使用**（推荐使用）**

1.模式1

from DBUtils.PersistentDB import PersistentDB
import pymysql

POOL = PersistentDB(
    creator=pymysql,    # 使用连接数据库的模块
    maxusage=None,      # 一个连接最多被重复使用的次数，None表示无限制
    setsession=[],      # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never,
    # 1 = default = whenever it is requested,
    # 2 = when a cursor is created,
    # 4 = when a query is executed,
    # 7 = always
    ping=0,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。
    # 如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，
    # 因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    closeable=False,
    threadlocal=None,   # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    host="10.211.55.3", # 目标数据库的ip地址
    port=3306,          # 目标数据库的端口号
    username="root",    # 目标数据库的用户名称
    password="root",    # 目标数据库的用户密码
    database="select_study",  # 目标数据库的名称
    charset='utf8'
)


def func():
    # 不共享线程
    conn = POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select * from student')
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result


result = func()
print(result)

2.模式2

import pymysql
from DBUtils.PooledDB import PooledDB, SharedDBConnection

POOL = PooledDB(
    creator=pymysql,    # 使用链接数据库的模块
    maxconnections=6,   # 连接池允许的最大连接数，0和None表示不限制连接数
    mincached=2,        # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,        # 链接池中最多闲置的链接，0和None不限制
    # 链接池中最多共享的链接数量，
    # 0和None表示全部共享。
    # PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，
    # 所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。
    maxshared=3,
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    # ping MySQL服务端，检查是否服务可用。
    # 如：0 = None = never,
    # 1 = default = whenever it is requested,
    # 2 = when a cursor is created,
    # 4 = when a query is executed,
    # 7 = always
    ping=0,
    host="10.211.55.3", # 目标数据库的ip地址
    port=3306,          # 目标数据库的端口号
    username="root",    # 目标数据库的用户名称
    password="root",    # 目标数据库的用户密码
    database="select_study",  # 目标数据库的名称
    charset='utf8'
)


def func():
    # 检测当前正在运行连接数的是否小于最大连接数，
    # 如果不小于则：等待或报raise TooManyConnections异常
    # 否则，则优先去初始化时创建的连接中获取连接对象 SteadyDBConnection。
    # 然后将SteadyDBConnection对象封装到PooledDedicatedDBConnection中并返回。
    # 如果最开始创建的连接对象没有使用，则去创建一个SteadyDBConnection对象，
    # 再封装到PooledDedicatedDBConnection中并返回。
    # 一旦关闭连接对象后，连接就返回到连接池让后续线程继续使用。
    conn = POOL.connection()
    
    # print('连接被拿走了', conn._con)
    # print('池子里目前有', POOL._idle_cache, '\r\n')

    cursor = conn.cursor()
    cursor.execute('select * from student')
    result = cursor.fetchall()
    conn.close()
    return result


result = func()
print(result)

拓展知识-锁

锁的概念

在Python中，锁（Lock）是一种同步机制，用于控制多个线程对共享资源的访问；
锁可以确保在任何时候只有一个线程可以访问特定的资源或代码块，从而防止数据的不一致性和竞态条件；

threading模块下的常用锁：
互斥锁（Look）

概念：它保证在任何时候只有一个线程可以持有锁，从而访问被保护的资源；
用途：当需要确保在任何时候只有一个线程可以访问某个资源或执行某段代码时使用；

可重入锁（RLook）

概念：允许在同一线程中多次获取同一个锁，这对于需要递归访问共享资源的场景非常有用；
用途：当一个线程需要多次获取同一个锁时，使用RLock可以防止死锁；

信号量（Semaphore）

概念：是一个计数器，它允许一定数量的线程同时访问某个资源。常用于控制对有限资源的访问；
用途：当需要限制对共享资源的并发访问数量时使用；

有界信号量（oundedSemaphore）

概念：是有最大计数限制的信号量，当计数达到最大值时，新的线程将无法获取信号量；
用途：当需要限制对资源的并发访问数量，并且这个数量是固定的时使用；

条件变量（Condition）

概念：通常与一个锁关联，允许线程等待某个条件成立，或者通知其他线程某个条件已经成立；
用途：当需要在多个线程之间协调或同步状态时使用，例如生产者-消费者问题；

使用锁的注意事项：
死锁

当两个或多个线程无限期地等待一个资源时，会发生死锁；
这通常是因为线程间存在循环等待条件；
为了避免死锁，应确保线程以固定的顺序请求锁，或者使用超时机制来释放锁；

性能开销

锁可能会导致线程阻塞和上下文切换，从而降低程序的性能；
因此，应尽量避免不必要的锁操作，只在真正需要时使用锁；

锁粒度

锁的粒度指的是锁所保护的数据范围；
过细的粒度可能导致过多的锁竞争；
过粗的粒度可能无法提供足够的并发性；

锁的顺序

在多线程程序中，确保所有线程都按照相同的顺序请求锁，这样可以避免死锁的发生；

锁释放

务必确保在代码块的末尾释放锁，即使发生异常也要确保锁被释放；
可以使用try…finally结构或者with的特性来确保这一点；

避免嵌套锁

尽量避免在一个线程中持有多个锁，因为这可能导致死锁**
如果确实需要这样做，请确保以相同的顺序请求锁**

1.加锁

由于pymysql的threadsafety值为1；
所以该模式连接池中的线程会被所有线程共享，因此是线程安全的；
如果没有连接池，使用pymysql来连接数据库时，单线程应用完全没有问题；
但如果涉及到多线程应用那么就需要加锁；
一旦加锁那么连接势必就会排队等待；
当请求比较多时，性能就会降低了；

import pymysql
import threading
from threading import RLock

# 实例化一个“可重入锁对象”
# 它是Lock的一个变体，支持嵌套调用。在多线程编程中，如果一个线程已经获得了某个锁，那么在该线程没有释放锁之前，其他线程是无法获得该锁的。
# 这种情况下，如果该线程在持有锁的状态下需要再次获得该锁，通常会出现死锁的问题。
# 而可重入锁就是为了解决这种问题而设计的。
LOCK = RLock()

CONN = pymysql.connect(
    host="10.211.55.3",  # 目标数据库的ip地址
    port=3306,           # 目标数据库的端口号
    user="root",         # 目标数据库的用户名称
    password="root",     # 目标数据库的用户密码
    database="select_study",  # 目标数据库的名称
    charset='utf8')


def task(arg):
    # 利用with的特性管理上下文，从而管理上锁和释放锁
    with LOCK:
        cursor = CONN.cursor()
        cursor.execute('select * from student ')
        result = cursor.fetchall()
        cursor.close()

        print(result)


for i in range(10):
    t = threading.Thread(target=task, args=(i,))
    t.start()

2.无锁（报错）

import pymysql
import threading
from threading import RLock

# LOCK = RLock()
CONN = pymysql.connect(
    host="10.211.55.3",  # 目标数据库的ip地址
    port=3306,           # 目标数据库的端口号
    user="root",         # 目标数据库的用户名称
    password="root",     # 目标数据库的用户密码
    database="select_study",  # 目标数据库的名称
    charset='utf8')


def task(arg):
    cursor = CONN.cursor()
    cursor.execute('select * from student ')
    result = cursor.fetchall()
    cursor.close()

    print(result)


for i in range(10):
    t = threading.Thread(target=task, args=(i,))
    t.start()

真实项目应用代码封装

关于代码中调用的工具类，请移步其他推文
如日志记录工具类，请移步Python 3 日志处理logging模块
如配置文件工具类，请移步Python 3 拿捏configparser和pyyaml配置文件解析库

线程池封装

代码设计思想：
1.利用DBUtils模块构建数据库连接池
2.利用Configparser模块读取数据库连接配置信息
3.利用魔术方法__enter__和__exit__构建上下文管理，完成连接、游标对象的使用和清理
4.利用装饰器完成单例模式，保证连接池不会重复实例化

import configparser

import dbutils
import pymysql
from dbutils.pooled_db import PooledDB

from common.CfgFileConfig import CFGDismantle
from common.ConstantsCfg import DATABASE_FILE_PATH

# 通过装饰器完成单例模式
def singleton(cls):
    # 使用字典存储类对象的实例
    instances = {}

    def _singleton(*args, **kwargs):
        if cls not in instances:
            # 如果类没有被创建过，那就new个新对象并存储到字典中
            instances[cls] = cls(*args, **kwargs)
        return instances[cls]

    return _singleton


class MySqlConnectionPool(object):

    __pool: PooledDB = None
    __config: CFGDismantle = CFGDismantle(DATABASE_FILE_PATH)
    __config: configparser.RawConfigParser = __config.__get__()
    __host: str = __config["mysql@imp"]['host']
    __port: str = __config["mysql@imp"]['port']
    __user: str = __config["mysql@imp"]['username']
    __password: str = __config["mysql@imp"]['password']
    __database: str = __config["mysql@imp"]['database']

    def __init__(self):
        self.to_mysql: dict = {
            'host': str(MySqlConnectionPool.__host),
            'user': str(MySqlConnectionPool.__user),
            'port': int(MySqlConnectionPool.__port),
            'password': str(MySqlConnectionPool.__password),
            'database': str(MySqlConnectionPool.__database)
        }

    def __enter__(self):
        """
        上下文管理（开始），使用本类实例时自动执行本方法；获取数据库连接对象、游标
        :return:
        """
        self.conn: dbutils.pooled_db.PooledDedicatedDBConnection = self.__acquire_connection_object()
        self.cursor: dbutils.steady_db.SteadyDBCursor = self.conn.cursor()

    def __acquire_connection_object(self):
        """
        创建数据库连接池（私有方法）
        :return: 数据库连接对象
        """
        if self.__pool is None:
            try:
                self.__pool = PooledDB(
                    creator=pymysql,  # 连接数据库模块
                    mincached=20,  # 启动时开启的闲置连接量（默认值0，默认不创建）
                    maxcached=30,  # 连接池中允许的闲置连接量（默认值0，默认不闲置）
                    maxshared=30,  # 共享连接量允许的最大数量
                    maxconnections=100,  # 创建连接池中最大数量
                    blocking=True,  # 设置在连接池达到最大数量时的行为（阻塞）
                    maxusage=100,  # 单个连接的最大允许复用次数
                    setsession=[],  # 一个可选的SQL命令列表用于每个会话前的准备操作
                    ping=0,  # ping MySQL服务端，检查是否服务可用
                    use_unicode=True,  # 使用编码
                    charset="utf8",  # 设定编码
                    **self.to_mysql  # 密钥信息
                )
            except pymysql.err.OperationalError:
                raise ConnectionError(f"请检查数据库连接信息：{self.to_mysql}")
            except Exception as e:
                raise ConnectionError(f"数据库连接出现意外错误：{e}")
        return self.__pool.connection()

    # 释放连接池资源
    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        上下文管理（结束），本类实例使用完毕或一定时间内没有活跃时执行；关闭游标、数据库连接对象
        :param exc_type:
        :param exc_val:
        :param exc_tb:
        :return:
        """
        try:
            self.cursor.close()
            self.conn.close()
        except Exception as e:
            pass

    @singleton
    def acquire_connection_object(self):
        """
        从数据库连接池中取出一个闲置数据库连接对象
        :return: 游标、数据库连接对象
        """
        __conn = self.__acquire_connection_object()
        return __conn


if __name__ == '__main__':
    import os, sys

    base_path: str = os.path.dirname(os.path.realpath(sys.argv[0]))
    path = os.path.join(os.path.dirname(os.path.dirname(base_path)), "config", "database.ini")
    print("数据库配置文件路径：", path)

    mysql1 = MySqlConnectionPool()
    cursor1, conn1 = mysql1.acquire_connection_object()
    print("是否连接成功1：", conn1)
    print("对象唯一标识1：", id(conn1))
    mysql2 = MySqlConnectionPool()
    cursor2, conn2 = mysql2.acquire_connection_object()
    print("是否连接成功2：", conn2)
    print("对象唯一标识2：", id(conn2))

数据库操作防SQL注入封装

代码设计思想：
1.利用类属性，实例化连接池
2.使用构造器，初始化连接池，获取连接对象和游标对象
3.利用装饰器，完成上锁和释放锁
4.利用参数化，完成防止SQL注入
5.利用封装化，完成增删改查等功能

import functools
import os
import sys
import threading
import traceback

from common.LoggerCfg import log
from common.MysqlDataBase.MysqlConnectionPool import MySqlConnectionPool

# 设计一个锁状态装饰器
def ensure_lock_ok(func):
    lock = threading.Lock()

    @functools.wraps(func)
    def wrapper(*args, **kwargs):
        # 利用上下文管理器，保证上锁和释放锁
        with lock:
            return func(*args, **kwargs)

    return wrapper


class MysqlHandlerSQL(object):
    __connect_pool = MySqlConnectionPool()

    def __init__(self):
        self.conn = MysqlHandlerSQL.__connect_pool.acquire_connection_object()
        self.cursor = self.conn.cursor()

    @ensure_lock_ok
    def execute_sql(self, sql: str, params: str or list = None, exe_many: bool = False,
                    is_query_one: bool = False, is_query_all: bool = False):
        """
        数据库连接对象执行SQL语句的基础方法
        :param sql:         必填参数；字符类型；SQL语句；select %s from table where column = %s 其中%s就是形参
        :param params:      非必填项；列表类型；SQL语句携带参数；单条SQL语句参数：(1,2,3...)，多条SQL语句参数：[[1,2,3...],[2,3,4...]]
        :param exe_many:    必填参数；布尔类型；默认False，是否批量执行SQL语句；True多条SQL语句，False单条SQL语句
        :param exe_many:    必填参数；布尔类型；默认False，是否批量执行SQL语句；True多条SQL语句，False单条SQL语句
        :param is_query_one:必填参数；布尔类型；默认False，执行SQL语句是否为查询语句；True返回fetchone，False返回受影响行数
        :param is_query_all:必填参数；布尔类型；默认False，执行SQL语句是否为查询语句；True返回fetchall，False返回受影响行数
        :return:    返回数据库连接对象conn 和数据游标cursor 及SQL语句影响行数count
        """
        execute_result = 0  # 执行结果/受影响行数
        try:
            # 是否一次执行多条sql语句
            if exe_many:
                execute_result = self.cursor.executemany(sql, params) if params else self.cursor.executemany(sql)
            else:
                execute_result = self.cursor.execute(sql, params) if params else self.cursor.execute(sql)

            # 查询单条？返回fetchone
            if is_query_one:
                execute_result = self.cursor.fetchone()
                self.cursor.nextset()  # 每次执行sql语句后将结果集全部倒出保证游标内是空的
                log.info(f"执行查询单条SQL语句成功：{self.cursor.mogrify(query=sql, args=params)}")

            # 查询所有？返回fetchall
            elif is_query_all:
                execute_result = self.cursor.fetchall()
                self.cursor.nextset()  # 每次执行sql语句后将结果集全部倒出保证游标内是空的
                log.info(f"执行查询所有SQL语句成功：{self.cursor.mogrify(query=sql, args=params)}")

            else:
                log.info(
                    f"执行SQL语句成功：受影响行数：{execute_result}，SQL语句：{self.cursor.mogrify(query=sql, args=params)}")

            self.conn.commit()
            return execute_result
        except Exception as e:
            log.info(f"执行SQL语句失败：{e}")
            log.info(f"执行SQL语句失败：{traceback.format_exc()}")
            self.conn.rollback()
            return execute_result

    def insert_data(self, **kwargs):
        """
        根据条件自产新增SQL语句，执行后返回影响行
        table：必填，插入表名，字符类型
        data ：必填，更新数据，字典类型
        """
        # 将插入表名以table作为键值设为必填参数
        table = kwargs["table"]
        # 将更新数据以data作为键值设为必填参数
        data = kwargs["data"]
        # 设置基础SQL语句
        sql = "insert into %s (" % table
        # 记录表字段
        fields = ""
        flag = ""
        # 记录插入的数据
        params = []
        for k, v in data.items():
            fields += "%s," % k
            params.append(str(v))
            flag += "%s,"
        fields = fields.rstrip(",")
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        flag = flag.rstrip(",")
        sql += fields + ") values (" + flag + ")"
        return self.execute_sql(sql=sql, params=params)

    def delete_data(self, **kwargs):
        """
        根据条件自产删除SQL语句，执行后返回影响行
        table： 必填项，查询表名，字符串类型，如：table="test_table"
        where： 非必填，查询条件，分两种类型，如：1.字典类型用于=，如：where={"aaa": 333, "bbb": 2}；2.字符串类型用于非等于判断，如：where="aaa>=333"
        """
        # 将查询表名以table作为键值设为必填参数
        table = kwargs["table"]
        # 将查询条件以where作为键值设为必填参数
        where = kwargs["where"]
        # 基础SQL语句
        sql = "delete from %s where 1=1" % (table)
        # where条件携带参数
        params = []
        # 如果where参数是以字典形式携带，则表示K值=V值
        if isinstance(where, dict):
            for k, v in where.items():
                sql += " and {} in (%s)".format(k)
                params.append(str(v))
        # 如果where参数是以字符串形式携带，则直接将字符串and到SQL语句上
        elif isinstance(where, str):
            sql += " and %s" % where
        # sql += ";"
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        return self.execute_sql(sql=sql, params=params)

    def update_data(self, **kwargs):
        """
        根据条件自产修改SQL语句，执行返回影响行
        table：必填项，查询表名，字符串类型，如：table="test_table"
        data ：必填项，更新数据，字典类型，如：data={"aaa": "6666", "bbb": "888"}
        where：非必填，查询条件，分两种类型，如：1.字典类型用于=，如：where={"aaa": 333, "bbb": 2}；2.字符串类型用于非等于判断，如：where="aaa>=333"
        """
        # 将查询表名以table作为键值设为必填参数
        table = kwargs["table"]
        # 将更新数据以data作为键值设为必填参数
        data = kwargs["data"]
        # 将查询条件以where作为键值设为必填参数
        where = kwargs["where"]
        # 基础SQL语句
        sql = "update %s set " % table
        # where条件携带参数
        params = []
        for k, v in data.items():
            sql += "{}=%s,".format(k)
            params.append(str(v))
        sql = sql.rstrip(",")
        sql += " where 1=1 "
        # 如果where参数是以字典形式携带，则表示K值=V值
        if isinstance(where, dict):
            for k, v in where.items():
                sql += " and {} in (%s)".format(k)
                params.append(str(v))
        # 如果where参数是以字符串形式携带，则直接将字符串and到SQL语句上
        elif isinstance(where, str):
            sql += " and %s" % where
        # sql += ";"
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        return self.execute_sql(sql=sql, params=params)

    def query_one(self, **kwargs):
        """
        根据条件自产查询SQL语句，执行后返回第一条数据
        table：必填项，查询表名，字符串类型，如：table="test_table"
        where：非必填，查询条件，如果是字典类型表示等于，如：where={"a列": 111, "b列": 2222}；如果是字符串类型表示非等于判断，如：where="c列>=333"
        field：非必填，查询列名，字符串类型，如：field="aaa, bbb"，不填默认*
        order：非必填，排序字段，字符串类型，如：order="a列"
        sort： 非必填，排序方式，字符串类型，如：sort="asc" 或者 "desc"，不填默认asc
        """
        # 将查询表名以table作为键值设为必填参数
        table = kwargs['table']
        # 判断是否携带查询列值，如果携带则查询传递字段，否则查询所有字段
        field = "field" in kwargs and kwargs["field"] or "*"
        # 将查询条件以where作为键值设为必填参数
        where = kwargs["where"]
        # 判断是否传递order关键字参数，如果没有传递则不拼接，否则拼接order by字段
        order = "order" in kwargs and "order by " + kwargs["order"] or ""
        # 将排序方式以sort作为键值设为必填参数，默认asc
        sort = kwargs.get("sort", "asc")
        # 如果order为空则sort也设置为空
        if order == "":
            sort = ""
        # 基础SQL语句
        sql = f"select {field} from {table} where 1=1"
        # where条件携带参数
        params = []
        # 如果where参数是以字典形式携带，则表示K值=V值
        if isinstance(where, dict):
            for k, v in where.items():
                sql += " and {} in (%s)".format(k)
                params.append(str(v))
        # 如果where参数是以字符串形式携带，则直接将字符串and到SQL语句上
        elif isinstance(where, str):
            sql += f" and {where}"
        # 排序处理后筛选第1条数据
        sql += f" {order} {sort} limit 1"
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        result = self.execute_sql(sql=sql, params=params, is_query_one=True)
        return result

    def query_all(self, **kwargs):
        """
        根据条件自产查询SQL语句，执行后返回所有数据
        table： 必填项，查询表名，字符串类型，如：table="test_table"
        where： 非必填，查询条件，分两种类型，如：1.字典类型用于=，如：where={"aaa": 333, "bbb": 2}；2.字符串类型用于非等于判断，如：where="aaa>=333"
        field： 非必填，查询列名，字符串类型，如：field="aaa, bbb"，不填默认*
        order： 非必填，排序字段，字符串类型，如：order="a列"
        sort：  非必填，排序方式，字符串类型，如：sort="asc" 或者 "desc"，不填默认asc
        offset：非必填，偏移数量，如翻页，不填默认0
        limit： 非必填，查询条数，不填默认100
        """
        # 将查询表名以table作为键值设为必填参数
        table = kwargs["table"]
        # 判断是否携带查询列值，如果携带则查询传递字段，否则查询所有字段
        field = "field" in kwargs and kwargs["field"] or "*"
        # 判断是否传递order关键字参数，如果没有传递则不拼接，否则拼接order by字段
        order = "order" in kwargs and "order by " + kwargs["order"] or ""
        # 将排序方式以sort作为键值设为必填参数，默认asc
        sort = kwargs.get("sort", "asc")
        # 如果order为空则sort也设置为空
        if order == "":
            sort = ""
        # 将偏移数量以offset作为键值设为必填参数，默认0，如果查询需要分页则以offset和limit来计算
        offset = kwargs.get("offset", 0)
        # 将查询条数以limit作为键值设为必填参数，默认100
        limit = kwargs.get("limit", 100)
        # 基础SQL语句
        sql = "select %s from %s where 1=1 " % (field, table)
        # where条件携带参数
        params = []
        # 将查询条件设置为非必填项，如果使用该方法时，传递了where关键字，则根据传入的数据类型进行SQL语句处理
        if 'where' in kwargs.keys():
            where = kwargs["where"]
            # 如果where参数是以字典形式携带，则表示K值=V值
            if isinstance(where, dict):
                for k, v in where.items():
                    sql += " and {} in (%s)".format(k)
                    params.append(str(v))
            # 如果where参数是以字符串形式携带，则直接将字符串and到SQL语句上
            elif isinstance(where, str):
                sql += " and %s" % where
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        # 处理SQL语句，拼接排序字段和方式以及或分页计算
        sql += " %s %s limit %s, %s" % (order, sort, offset, limit)
        return self.execute_sql(sql=sql, params=params, is_query_all=True)

    def query_count(self, **kwargs):
        """
        根据条件自产查询SQL语句，执行后返回结果行数
        :param kwargs：
                table：必填项，查询表名，字符串类型，如：table="test_table"
                where：非必填，查询条件，如果是字典类型表示等于，如：where={"a列": 111, "b列": 2222}；
                               如果是字符串类型表示非等于判断，如：where="c列>=333"
        :return:
        """
        # 将查询表名以table作为键值设为必填参数
        table = kwargs["table"]
        # 基础SQL语句
        sql = f"select count(1) as count from {table} where 1=1 "
        # 使用params列表记录where携带的条件参数
        params = []
        # 将查询条件设置为非必填项，如果传递了where关键字，则根据传入的数据类型处理
        if 'where' in kwargs.keys():
            where = kwargs["where"]
            # 如果where参数是以字典形式携带，则表示K值=V值
            if isinstance(where, dict):
                for k, v in where.items():
                    sql += " and {} in (%s)".format(k)
                    params.append(str(v))
            # 如果where参数是以字符串形式携带，则直接将字符串and到SQL语句上
            elif isinstance(where, str):
                sql += " and %s" % where
        # 如果是字典则会产生对应字典的数据，如果是字符串则没有
        params = tuple(params)
        result = self.execute_sql(sql=sql, params=params, is_query_one=True)
        return int(result[0])


if __name__ == '__main__':
    __base_path: str = os.path.dirname(os.path.realpath(sys.argv[0]))
    __path = os.path.join(os.path.dirname(os.path.dirname(__base_path)), "config", "database.ini")
    print("配置文件路径：", __path)

    __mysql = MysqlHandlerSQL()
    # __sql = "select * from risk_blaze_req_res  where id = 1"
    __sql = 'select * from imp_new_core_rules'
    __count = __mysql.execute_sql(__sql)
    print('查询数量：', __count)
    # print('执行语句：', __mysql.cursor.mogrify(query=__sql, args=None))
    # print('查询结果：', __mysql.cursor.fetchone())

    # __mysql = MySqlCRUDWrapper(__path, "mysql@decisiondb")
    # __sql = "select id, decision_scenario,application_status,ruleset_res_mesg, pass_flag  from risk_blaze_req_res where decision_serial_no = '202305806' order by id "
    # __count = __mysql.execute_sql(__sql)
    # print('查询数量：', __count)
    # print('执行语句：', __mysql.cursor.mogrify(query=__sql, args=None))
    # print('查询结果：', __mysql.cursor.fetchone())
    # print('查询结果：', __mysql.cursor.fetchall())

    # __query_one = __mysql.query_one(table="risk_blaze_req_res",
    #                                 field="ruleset_res_mesg",
    #                                 where={"decision_serial_no": 202305806,
    #                                        "application_status": 0})
    # print(__query_one)

    # __query_count = __mysql.query_count(table="risk_blaze_req_res",where={"decision_serial_no": 202305806})
    # print(__query_count)

    # __query_all = __mysql.query_all(table="risk_blaze_req_res",
    #                 field="id, decision_scenario,application_status,ruleset_res_mesg, pass_flag",
    #                 order="id",
    #                 sort="asc")
    # print(__query_all)

    # risk_mysql = MySqlCRUDWrapper(path=__path, section="mysql@decisiondb")
    # __result = risk_mysql.query_all(table="risk_blaze_req_res", field="application_status",
    #           where={"decision_serial_no": 202305806})
    # print(__result)

    # imp_mysql = MySqlCRUDWrapper(path=__path, section="mysql@imp")
    # __result = imp_mysql.query_one(table="imp_risk_control_ser",field="excel_name,  sheet_name", where={"record_id":1231231})
    # print(__result)

    # result = imp_mysql.query_count(table="imp_risk_control_ser", where={"sheet_name": "抖音月付123（douyin_risk)"})

    # result = imp_mysql.update_data(table="imp_risk_control_ser",
    #                                data={"modify_flag": "1"},
    #                                where={"sheet_name": "抖音月付（douyin_risk)"})
    # print(result)

    # imp_mysql.delete_data(table="imp_risk_control_ser",where={"sheet_name": "抖音月付（douyin_risk)"})

    # table_data = {
    #     "excel_name": '0',
    #     "sheet_name": '抖音月付（douyin_risk)',
    #     "sheet_data": {'douyin_risk': {'@Attribute': ['related_role'], 'biz_info': ['account_id', 'name', 'id_type',
    #             'id_number', 'phone', 'gender', 'nation', 'credit_amount', 'old_credit_amount', 'idcard_address',
    #             'idcard_start_date', 'idcard_end_date', 'idcard_ethnicity', 'idcard_authority', 'company_industry',
    #             'cardid', 'bank_name', 'bank_phone'], 'reserve_info': ['R101', 'R102', 'R201', 'R203', 'R204', 'R205',
    #             'R301', 'R302', 'R303', 'R401', 'R402', 'R501']}},
    #     "update_time": "2023-09-04 22:21:02"
    # }
    # result = imp_mysql.insert_data(table="imp_risk_control_ser", data=table_data)
    # print(result)

总结

在本文中，我们深入探讨了使用pymysql模块操作MySQL数据库的各项关键技术；
通过了解如何建立和管理数据库连接、执行基础的CRUD操作、处理事务与回滚、执行单条和批量SQL语句，我们获得了在Python中操作数据库的全面能力。

同时，我们也讨论了如何引入锁机制来确保数据的一致性；
并学习了如何防止SQL注入攻击，增强应用程序的安全性；
另外，利用数据库连接池的技术为高效利用数据库资源提供了解决方案，它可以有效减少建立和关闭连接的开销，提升应用的性能；

然而，数据库操作是一个深度和广度都非常大的领域，本文所介绍的只是其中的一部分。
在实际应用中，我们还需要考虑更多的因素，如性能优化、错误处理、日志记录等。
希望这篇文章能够为你提供一个良好的起点，帮助你更好地理解和使用pymysql模块，并在实际项目中灵活应用所学知识。

需要休息的KK.

关注

78
点赞
踩
40

收藏

觉得还不错? 一键收藏
打赏
1
评论
Python 深入探讨pymysql与dbutils模块操作MySQL数据库的各项关键技术

一文精通pymysql与dbutils模块，通过数据库连接池高效管理数据库连接，掌握CRUD操作技巧，处理事务与回滚确保数据一致性。同时，我们将深入了解锁的概念，保护数据免受并发访问影响，并探讨SQL注入的防范措施，提升数据库安全性。
复制链接

扫一扫