python操作mysql的库--Pymysql
安装
pip install pymysql
安装json，用于加载json配置文件参数
pip install simplejson
创建实验用表
MariaDB [test]> create table student (
id int not null auto_increment primary key,
name varchar(20) not null,
age int default null
);
Query OK, 0 rows affected (0.007 sec)
-- 查看表结构
MariaDB [test]> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)
连接Connect
首先，必须建立一个传输数据通道——连接。
pymysql.connect()方法返回的是Connections模块下的Connection类实例。connect方法传参就是给Connection类的__init__提供参数
| Connection初始化常用参数 | 说明 |
|---|---|
| user | 用户名 |
| password | 密码 |
| host | 主机 |
| database | 数据库 |
| port | 端口 |
| autocommit | 自动提交事务 |
游标Cursor
操作数据库，必须使用游标，需要先获取一个游标对象。
Connection.cursor(cursor=None) 方法返回一个新的游标对象。
连接没有关闭前，游标对象可以反复使用。
cursor参数，可以指定一个Cursor类。如果为None，则使用默认Cursor类。
操作数据库
数据库操作需要使用Cursor类的实例，提供execute() 方法，执行SQL语句，成功返回影响的行数。
配置文件
config.json
{
"user": "root",
"password": "111111",
"host": "10.0.0.4",
"database": "test",
"port": 3306
}
新增记录
使用insert into语句插入数据。
import pymysql
import simplejson
from pathlib import Path
p = Path().absolute() / 'config.json' # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
# print(f.read())
conf = simplejson.load(f) # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor() # 新建游标，类似指针
sql = """insert into student (name,age) values('abc','18')"""
x = curses.execute(sql) # 执行sql语句
print(x) # 返回的影响行数
conn.commit() # 提交事务，因为默认autocommit=False
except Exception as e:
conn.rollback() # 一旦发生异常，回滚事务
print(e)
finally:
if curses:
curses.close()#关闭游标
if conn:
conn.close()#关闭连接
MariaDB [test]> select * from student;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | abc | 18 |
+----+------+------+
1 row in set (0.000 sec)
事务管理
Connection类有三个方法：
begin 开始事务
commit 将变更提交
rollback 回滚事务
批量添加数据
import pymysql
import simplejson
from pathlib import Path
p = Path().absolute() / 'config.json' # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
# print(f.read())
conf = simplejson.load(f) # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor() # 新建游标，类似指针
for i in range(2, 6):
sql = """insert into student (name,age) values('abc{0}','{0}')""".format(i)
x = curses.execute(sql) # 执行sql语句
print(x) # 返回的影响行数
conn.commit() # 提交事务，因为默认autocommit=False
except Exception as e:
conn.rollback() # 一旦发生异常，回滚事务
print(e)
finally:
if curses:
curses.close()
if conn:
conn.close()
MariaDB [test]> select * from student;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | abc | 18 |
| 2 | abc | 18 |
| 3 | abc2 | 2 |
| 4 | abc3 | 3 |
| 5 | abc4 | 4 |
| 6 | abc5 | 5 |
+----+------+------+
6 rows in set (0.000 sec)
executemany()方法,也可以批量添加数据
executemany(str, list) ----> int
第一个参数时sql语句
第二个参数是列表，由数据组成的列表
import pymysql
import simplejson
from pathlib import Path
p = Path().absolute() / 'config.json' # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
# print(f.read())
conf = simplejson.load(f) # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor() # 新建游标，类似指针
data = []
for i in range(7, 10):
data.append((f'abc{i}', i))
sql = """insert into student (name,age) values(%s,%s)"""
#values中的值都应该是%s，为字符串
print(data)
y = curses.executemany(sql, data)
print(y)
conn.commit() # 提交事务，因为默认autocommit=False
except Exception as e:
conn.rollback() # 一旦发生异常，回滚事务
print(e)
finally:
if curses:
curses.close()
if conn:
conn.close()
"""
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
[('abc7', 7), ('abc8', 8), ('abc9', 9)]
3
"""
一般流程
建立连接
获取游标
执行SQL
提交事务
释放资源
查询
Cursor类的获取查询结果集的方法有fetchone()、fetchmany(size=None)、fetchall()。
| 方法 | 说明 |
|---|---|
| fetchone() | 获取游标所指的下一行 |
| fetchmany(size=None) | 获取游标所致的下size行，没有size则返回空元组 |
| fetchall() | 获取剩余所有行 |
| rownumber | 返回当前游标所处行号，可以修改，支持负数 |
| rowcount | 返回总行数 |
import pymysql
import simplejson
from pathlib import Path
p = Path().absolute() / 'config.json' # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
# print(f.read())
conf = simplejson.load(f) # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor() # 新建游标，类似指针
sql = """select * from student where id>2"""
x = curses.execute(sql) # 执行sql语句
print(x) # 返回的影响行数
print(curses.rownumber, curses.rowcount)
print('----------拿一个')
print(curses.fetchone())
print(curses.rownumber, curses.rowcount)
print('----------再拿一个')
print(curses.fetchone())
print(curses.rownumber, curses.rowcount)
print('----------再拿三个个')
print(curses.fetchmany(3))
print(curses.rownumber, curses.rowcount)
print(curses.fetchall())
print('---------全拿完了-----')
print(curses.fetchone())
print('-' * 20)
print('-------游标调回1')
curses.rownumber = 1
print(curses.fetchall())
conn.commit() # 提交事务，因为默认autocommit=False
finally:
if curses:
curses.close()
if conn:
conn.close()
"""
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
7
0 7
----------拿一个
(3, 'abc3', 3)
1 7
----------再拿一个
(4, 'abc4', 4)
2 7
----------再拿三个个
((5, 'abc5', 5), (6, 'abc6', 6), (7, 'abc7', 7))
5 7
((8, 'abc8', 8), (9, 'abc9', 9))
---------全拿完了-----
None
--------------------
-------游标调回1
((4, 'abc4', 4), (5, 'abc5', 5), (6, 'abc6', 6), (7, 'abc7', 7), (8, 'abc8', 8), (9, 'abc9', 9))
"""
带列名查询
缺省的cursor返回的都是元组，不带列名
pymysql.cursors类有一个Mixin的子类DictCursor。
可以返回列名和value组成的字典之后组成的列表
只需要cursor = conn.cursor(DictCursor)就可以了
import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor #导入DictCursor
p = Path().absolute() / 'config.json' # 拼凑配置文件路径
with open(p, encoding='utf8') as f:
# print(f.read())
conf = simplejson.load(f) # json文件反序列化，生成字典
print(conf)
conn = None
curses = None
try:
conn = pymysql.connect(**conf)
curses = conn.cursor(DictCursor) # 使用DictCursor
sql = """select * from student where id>2"""
x = curses.execute(sql) # 执行sql语句
print(x) # 返回的影响行数
print(curses.fetchone())
print(curses.fetchall())
conn.commit() # 提交事务
finally:
if curses:
curses.close()
if conn:
conn.close()
"""
{'user': 'root', 'password': '111111', 'host': '10.0.0.4', 'database': 'test', 'port': 3306}
7
{'id': 3, 'name': 'abc3', 'age': 3}
[{'id': 4, 'name': 'abc4', 'age': 4}, {'id': 5, 'name': 'abc5', 'age': 5}, {'id': 6, 'name': 'abc6', 'age': 6}, {'id': 7, 'name': 'abc7', 'age': 7}, {'id': 8, 'name': 'abc8', 'age': 8}, {'id': 9, 'name': 'abc9', 'age': 9}]
"""
返回一行，是一个字典。
返回多行，放在列表中，元素是字典，代表一行。
SQL注入攻击
import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor
p = Path().absolute() / 'config.json'
with open(p, encoding='utf8') as f:
conf = simplejson.load(f)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor(DictCursor) # 新建游标，类似指针
sql = """select * from student where id={}""".format('2 or 1=1')
x = curses.execute(sql)
print(curses.fetchall())
conn.commit() # 提交事务
finally:
if curses:
curses.close()
if conn:
conn.close()
"""
[{'id': 1, 'name': 'abc1', 'age': 1}, {'id': 2, 'name': 'abc2', 'age': 2}, {'id': 3, 'name': 'abc3', 'age': 3}, {'id': 4, 'name': 'abc4', 'age': 4}, {'id': 5, 'name': 'abc5', 'age': 5}, {'id': 6, 'name': 'abc6', 'age': 6}, {'id': 7, 'name': 'abc7', 'age': 7}, {'id': 8, 'name': 'abc8', 'age': 8}, {'id': 9, 'name': 'abc9', 'age': 9}]
"""
sql = """select * from student where id={}""".format('2 or 1=1')
期望是来一个字符，但是sql注入了一个拼接后保证查询条件为True的字符串，导致全部数据被暴露
SQL注入攻击
猜测后台数据库的查询语句使用拼接字符串等方式，从而经过设计为服务端传参，令其拼接出特殊字符串的SQL语句，返回攻击者想要的结果。
参数化查询，可以有效防止注入攻击，并提高查询的效率。
Cursor.execute(query, args=None) args，必须是元组、列表或字典。如果查询字符串使用%(name)s，就必须使用字典。
import pymysql
import simplejson
from pathlib import Path
from pymysql.cursors import DictCursor
p = Path().absolute() / 'config.json'
with open(p, encoding='utf8') as f:
conf = simplejson.load(f)
conn = None
curses = None
try:
conn = pymysql.connect(**conf) # 新建连接
curses = conn.cursor(DictCursor) # 新建游标，类似指针
sql = """select * from student where id=%s"""
x = curses.execute(sql, ('2 or 1=1',))
print(curses.fetchall())
conn.commit() # 提交事务
finally:
if curses:
curses.close()
if conn:
conn.close()
[{'id': 2, 'name': 'abc2', 'age': 2}]
本文来自博客园，作者：厚礼蝎，转载请注明原文链接：https://www.cnblogs.com/guangdelw/p/17058787.html
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· winform 绘制太阳，地球，月球 运作规律
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· AI 智能体引爆开源社区「GitHub 热点速览」
· 写一个简单的SQL生成工具