1 概述
pymysql是Python中的一个类库, 主要是通过python代码来操作SQL语句的。
1.1 pymysql模块的作用
问题: 我们如何将10万行数据插入到数据库?
回答: 我们发现如果用之前客户端几乎是不可能完全这个任务的, 因为我们不可能去构造出那个插入10万行数据的SQL. 可是，如果我们有一个功能能够插入一行数据，借助程序强大的特点：重复，就可以轻而易举的就将10w行数据收入麾下. 我们可以使用pymysql模块完成在Python中实现这样的操作。
1.2 Python中操作MySQL步骤
操作步骤:
- 导入模块 # import pymysql
- 创建连接对象 # connect,即python连接mysql数据库的对象
- 创建游标 # curdor，可以执行SQL语句的对象
- 执行sql语句，获取结果集
- 操作结果集
- 释放资源
2 pymysql查询数据
# 1.导包
import pymysql # 若没有安装这个库，安装此库的命令：pip install pymysql
# 2.创建连接对象
conn = pymysql.connect(
host='数据库所在机器的IP地址', # 本机就写：localhost
port=3306, # 要连接到的数据库端口号，MySQL是3306
user='账号', # 数据库的用户名
password='密码', # 数据库的密码
database='数据库名称', # 要操作的数据库
charset='utf8' # 码表
)
# 3.创建游标--可执行SQL语句的对象
cursor = conn.cursor()
# 4.执行SQL语句，获取结果集---假如需求是：查询order表中所有的订单信息
sql = 'select * from order;' #order是要操作的数据库中的表名
row_count = cursor.execute(sql) # 执行SQL语句，返回 受影响的行数
print(f'首影响的行数为：{row_count}')
# 5.操作结果集
data1 = cursor.fetchone() # 获取一条数据，元组封装
data2 = cursor.fetchmany(6) # 获取6条数据，元组嵌套的方式 ((第一条),(第二条)...)
data3 = cursor.fetchall() # 获取所有数据，元组嵌套的方式 ((第一条),(第二条)...)
#查看data3
for line in data3:
print(line)
# 6.释放资源
cursor.close()
conn.close()3. 数据提交和数据回滚
3.1 数据提交：commit()
数据库中的基本操作: 增删改查. 上一个章节中我们知道如何完成对数据库中数据的获取其实也就是查, 比如获取一条数据或者获取所有数据等. 接下来我们学习下如何增删改。
查数据, 并不会对原有数据库中的数据造成影响. 而增删改这三个操作都会对原有数据库中的数据造成影响，也就说查数据不会修改原有数据空中的数据, 而增删改会修改原有数据库中的数据。
当对原有数据库中数据有修改时需要使用commit()提交数据到数据库：conn.commit()
这里可以理解为：数据库询问是否确定修改数据，然后commit()就是确定修改的意思。
3.2 数据回滚：rollback()
当我们在使用pymysql对数据进行相应的操作时, 会有可能会有一些错误操作, 这时如果想要数据返回到最原始的状态可以使用数据回滚操作。
数据回滚是需要在 commit() 之前才有效, 也就是说数据还没有确定修改这时候使用数据回滚才是有效的。
回滚数据到什么都没做的原始状态 即撤销刚刚所有的修改操作：conn.rollback()
4 pymysql的增删改查操作
案例: 演示pymysql的curd操作, 增删改操作(更新语句), 记得要提交事务, 否则结果不会被写到数据表中。
首先在要操作的数据库中创建一个数据表
# 新建poets表 包括：诗人-出生年份-代表作品
create table poets(
id int primary key auto_increment,
name varchar(10), # 姓名
birthyear int, # 出生年份
masterpiece varchar(10) # 代表作
);
# 查看数据表
select * from poets;4.1增加数据的操作
import pymysql
# 增
def add_data():
# 1 获取连接对象
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集---在poets表中添加：王维，693年出生，代表作品《山居秋暝》
sql = 'insert into poets values(null,"李白",701,"将进酒"),(null,"王维",693,"山居秋暝"),(null,"白居易",772,"钱塘湖春行");'
row_count = cursor.execute(sql)
ptint(f'受影响的行数：{row_count}')
# 4 操作结果集---提交事务
conn.commit()
# 5 释放资源
cursor.close()
conn.close()
# main函数, 作为程序的主入口.
if __name__ == '__main__':
# 测试: 添加数据.
add_data()安全方式插入
插入操作中参数可以以元组、列表和字典形式传入，需要使用到占位符 “%s”，注意这只是个占位符，不同于Python 中字符串格式化中的转换说明符。
# 元组、列表形式传参
sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
try:
cursor.execute(sql, ("999999", "kenny", 28))
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()
# 字典形式传参
sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%(person_id)s, %(person_name)s, %(age)s)'
try:
cursor.execute(sql, {"person_id": "999998", "person_name": "kenny", "age": 28})
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()批量插入操作
sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
param = [("999999", "kenny", 28), (["999998", "liu", 28)] # 元组列表作为传入参数
try:
cursor.executemany(sql, param)
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()4.2 修改数据的操作
# 改
def update_data():
# 1 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集
sql = 'update poets set name="杜甫",birthyear=712,masterpiece="望岳" where id = 2;'
row_count=cursor.execute(sql)
ptint(f'受影响的行数：{row_count}')
# 4 操作结果集---提交事务
conn.commit()
# 5 释放资源
cursor.close()
conn.close()
# main函数, 作为程序的主入口.
if __name__ == '__main__':
# 测试: 修改数据.
update_data()占位符方式操作
sql = 'update player_info_test set PERSON_NAME=%s where PERSON_ID = %s'
try:
cursor.execute(sql, ['liu', '999998'])
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()4.3 删除数据的操作
# 删
def delete_data():
# 1. 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集
sql = 'delete from poets where id = 3;'
row_count = cursor.execute(sql)
print(f'受影响的行数为: {row_count}')
# 4 操作结果集.
conn.commit()
# 5 释放资源.
cursor.close()
conn.close()
# main函数, 作为程序的主入口.
if __name__ == '__main__':
# 测试: 删除数据.
delete_data()占位符方式操作
sql = 'delete from player_info_test where PERSON_ID = %s'
try:
cursor.execute(sql, ['999999'])
conn.commit()
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()4.4 查询数据的操作
# 查
def query_data():
# 1 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集
sql = 'select * from poets;'
row_count = cursor.execute(sql)
print(f'受影响的行数为: {row_count}')
# 4 操作结果集.
datas = cursor.fetchall()
for line in datas:
print(line)
# 5 释放资源.
cursor.close()
conn.close()
# main函数, 作为程序的主入口.
if __name__ == '__main__':
# 测试: 查询数据.
query_data()例子2：
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
user='python_link',
password='python_link',
port=3306,
database='nba_data'
)
# 创建游标，查询数据以元组形式返回
# cursor = conn.cursor()
# 创建游标，查询数据以字典形式返回
cursor = conn.cursor(pymysql.cursors.DictCursor)
sql = 'select * from player_info_test limit 10'
try:
cursor.execute(sql)
# result = cursor.fetchall() # 返回所有数据
# result = cursor.fetchone() # 返回一行数据
result = cursor.fetchmany(2) # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
print(result)
except Exception as e:
conn.rollback()
print(e)
finally:
cursor.close()
conn.close()5 pymysql演示注入攻击及解决方法
5.1 注入攻击
import pymysql
# 0. 提示用户录入他/她的账号 或 密码.
uname = input('请录入您的账号: ')
pwd = input('请录入您的密码: ')
# 1. 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集
# SQL写法1: 插值表达式.
# sql = f"select * from users where username='{uname}' and password='{pwd}';"
# SQL写法2：占位符
sql = "select * from user where username='%s' and password='%s';" %(uname, pwd)
row_count = cursor.execute(sql)
# 4. 操作结果集.
print("登陆成功" if row_count > 0 else "登陆失败")
# 5. 释放资源.
cursor.close()
conn.close()5.2 解决注入攻击
解决SQL注入攻击问题的思路: 预编译 + 占位符思想, 提前先对SQL语句进行预编译, 这一步已经确定SQL语句的格式了, 之后无论传入什么内容, 都只会当做普通字符来处理。
import pymysql
# 0. 提示用户录入他/她的账号 或 密码.
uname = input('请录入您的账号: ')
pwd = input('请录入您的密码: ')
# 1. 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
# 3 执行SQL语句，获取结果集
# 细节1: %s代表要添加的内容, 在这一步已经确定好SQL语句的格式了, 之后无论传入什么内容, 都只会当做普通的字符来处理.
sql = "select * from users where username=%s and password=%s;"
# 细节2: 给占位符填充值, 即: 定义1个列表, 记录: 一会儿要填充到SQL语句中的值.
params = [uname, pwd]
# 细节3: 执行SQL语句时, 传入参数(列表)即可.
row_count = cursor.execute(sql, params)
# 4. 操作结果集.
print("登陆成功" if row_count > 0 else "登陆失败")
# 5. 释放资源.
cursor.close()
conn.close()6 pymysql操作事务
MySQL默认开启了事务的自动提交功能, 每个语句都是1个单独的事务, 执行之后, 就会提交.
import pymysql
# 1. 获取连接对象.
conn = pymysql.connect(host='IP地址',port=3306,
user='用户名',password='密码',
database='数据库名称',charset='utf8')
# 2 获取游标对象
cursor = conn.cursor()
try:
# 这里写的是, 可能出问题的代码, 即: 具体的转账逻辑.
# 开启事务, 标记着: 转账从这里开始.
conn.begin()
# 3. 执行SQL语句, 获取结果集.
# 3.1 张三 - 1000元
sql1 = 'update account set money = money - 1000 where name="张三";'
row_count1 = cursor.execute(sql1)
# 模拟程序出问题了.
# print(1 / 0)
# 3.2 李四 + 1000元
sql2 = 'update account set money = money + 1000 where name="李四";'
row_count2 = cursor.execute(sql2)
except Exception as e:
# 走这里, 说明程序出问题了, 提示, 然后回滚即可.
conn.rollback() # 事务回滚
print('程序出问题了, 请稍后再试!')
else:
# 走到这里, 说明try没有问题, 提交事务即可.
conn.commit() # 提交事务
print('转账成功' if row_count1 == 1 and row_count2 == 1 else '转账失败')
finally:
# 释放资源, 无论try内容成功与否, 都会走这里.
cursor.close()
conn.close()
print('释放资源完毕!')SQL防注入
SQL注入是一种常见的网络攻击手法，它利用sql的语法特性和程序员编写程序时产生的漏洞，用一些特殊符号的组合产生特殊的含义，使得正常的sql语句失效，从而逃脱正常的业务逻辑，完成一些如跳过密码验证等的非法操作。
产生原因：SQL语句使用了动态拼接的方式。
比如，登录时，使用以下SQL查询验证用户信息
'SELECT username FROM user WHERE username = %s AND password = %s' % (username ,password)并且，没有对用户的输入做任何处理，直接放到了SQL语句中。那么，当黑客输入了’jjyang’ OR 1=1 – jjyang 作为用户名时，原来的SQL语句就会变成下面的样子：
SELECT username FROM user WHERE username = 'jjyang' OR 1=1 -- jjyang AND password=''WHERE username = ‘jjyang’ OR 1=1 是一个恒成立的条件，所以无论输入什么用户名都会返回True；而–后面的语句被当作注释忽略掉了，密码验证也被跳过。最终，绕过验证，成功登录。
针对参数不要采用拼接处理，交给pymysql中的方法（execute）自动处理，并对输入数据进行检查校验
# 元组,列表形式
param = ('jjyangs','123445')
sql = 'SELECT username FROM user WHERE username = %s AND password = %s'
cursor.execute(sql, param)
# 字典形式
param = {'name':'jjyangs','pwd':'123445'}
sql = 'SELECT username FROM user WHERE username = %(name)s AND password = %(pwd)s'
cursor.execute(sql, param)需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用格式化转换说明符。同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。
connect() 函数常用参数：
| 参数 | 说明 |
|---|---|
| dsn | 数据源名称，给出该参数表示数据库依赖 |
| host=None | 数据库连接地址 |
| user=None | 数据库用户名 |
| password=‘’ | 数据库用户密码 |
| database=None | 要连接的数据库名称 |
| port=3306 | 端口号，默认为3306 |
| charset=‘’ | 要连接的数据库的字符编码（可以在终端登陆mysql后使用 \s 查看，如下图） |
| connect_timeout=10 | 连接数据库的超时时间，默认为10 |
| port=3306 | 端口号，默认为3306 |
connect() 函数返回的连接对象的方法总结：
| 方法名 | 说明 |
|---|---|
| close() | 关闭数据库的连接 |
| commit() | 提交事务 |
| rollback() | 回滚事务 |
| cursor() | 获取游标对象，操作数据库，如执行DML操作，调用存储过程等 |
游标对象的方法：
| 方法名 | 说明 |
|---|---|
| callproc(procname,[,parameters]) | 调用存储过程，需要数据库支持 |
| close() | 关闭当前游标 |
| execute(operation,[,parameters]) | 执行数据库操作，sql语句或者数据库命令 |
| executemany(operation, seq_of_params) | 用于批量操作 |
| fetchone() | 获取查询结果集合中的下一条记录 |
| fetchmany(size) | 获取指定数量的记录 |
| fetchall() | 获取查询结果集合所有记录 |
| nextset() | 跳至下一个可用的数据集 |
| arraysize | 指定使用fetchmany()获取的行数，默认为1 |
| setinputsizes(size) | 设置调用execute*()方法时分配的内存区域大小 |
| setoutputsizes(size) | 设置列缓冲区大小，对大数据列尤其有用 |