14 Pymysql模块的使用

共计 5993 个字符，预计需要花费 15 分钟才能阅读完成。

引入

1. 什么是 pymysql

• pymysql 是 Python 中用来操作 mysql 的第三方模块(库)(就是一个 mysql 的客户端)
• pymysql 是 Python3 之后出来的模块, 而 Python2 中使用 mysqldb 操作数据库
• Django 中也可以使用 pymysql 连接 mysql 数据库

2.pymysql 的安装

• 终端命令行

pip3 install pymysql  # 或者使用下面的方式
pip3 install -i http://pypi.douban.com/simple/ pymysql # 豆瓣源
pip3 install -i https://pypi.tuna.tsinghua.edu.cn/simple pymysql # 清华源

一.Pymysql 的基本使用

1. 前提条件

• 有可用的数据库, 并且已经启动
• 有连接该数据库的用户名和密码
• 有操作数据库的权限

2. 创建一个库和一个表做演示

create database test02 charset utf8;
use tast02;
create table user(
    id int primary key auto_increment,
    name varchar(16) not null,
    passwd varchar(20) not null
);
insert user(name,passwd) value
    ("shawn","123456"),
    ("song","111111"),
    ("xing","222222");
select * from user;

2. 基本使用(链接、执行 sql、关闭游标和链接)

🥑首先导入 pymysql 模块
import pymysql

🥑连接 dtabase
conn = pymysql.connect(host="127.0.0.1",  # 你的数据库地址(我这里是本地的, 所以直接用回环地址)
    port=3306,         # 端口, 默认 3306
    user="root",       # 你登入数据库的用户名
    password="123456", # 你的密码
    database="test02", # 你的数据库名
    charset="utf8"     # 数据库编码格式
)

🥑获取一个光标, 类似于终端命令行提示符 : "mysql>"
cursor = connect.cursor()  #得到的结果是元组形式的数据(对应下面的 cursor.fetchall())
# cursor = connect.cursor(cursor=pymysql.cursors.DictCursor)  # 得到的是字典格式的数据

🥑定义一条 SQL 语句, 然后执行 sql 语句
sql = "select * from user"
res1 = cursor.execute(sql)  # 返回当前 SQL 语句所受影响的行数
res2 = cursor.fetchall()    # 以元组 (或者字典) 的形式返回 sql 语句查询到的记录 

🥑关闭光标
cursor.close()

🥑关闭数据库连接
conn.close()

3. 数据库登入验证

• 用户登入, 从数据库中取信息进行校验

import pymysql

username = input(" 请输入用户名 >>").strip()
userpwd = input(" 请输入密码 >>").strip()

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    passwd="123456",
    database="test02",
    charset="utf8"
    )

cursor = conn.cursor()
# sql = f"select * from user where name='{username}' and passwd='{userpwd}'"
sql = f"select * from user where name='%s' and passwd='%s'" %(username,userpwd)
res = cursor.execute(sql)

cursor.close()
conn.close()

if res:
    print(" 登入成功 ")
else:
    print(" 登入失败 ")

二.execute()的 SQL 语句注入问题

上面的数据库登入验证示例中, 用户输入的东西将会传入 SQL 语句中, 然后执行, 也就是说用户输入的字符就是 SQL 语句的一部分, 利用这一特性我们就可以绕过密码或非用户登入:

• 提示 : SQL 的注释语句 : --[空格][任意字符] 或者 # [任意字符]

1. 注入问题一

• 将 name 之后的条件注释, 可绕过密码登入

2. 注入问题二

• 添加一个永远为真的条件, 那么无论填什么都为真, 非用户可登入

3. 解决方法

• 原本我们是自己完成 SQL 语句字符串的拼接

sql = "select * from user where name='%s' and passwd='%s'" %(username,userpwd)

• 其实我们可以使用 execute() 的功能自动帮我们完成字符串拼接

sql = "select * from user where name=%s and passwd=%s"  # %s 不再需要加引号,pymysql 会自动帮我们加上
res = cursor.execute(sql,[username,userpwd])  # 将变量放入 execute 方法中, 可以是列表或元组形式, 自动帮我们解决注入问题

三. 增、删、改操作 : (conn.commit())

commit() : 提交, 在数据路增、删、改的时候, 必须交进行提交, 否则插入和修改的数据不生效

execute() : 指定插入单条数据

executemany() : 可以同时插入多条数据

import pymysql

🍪# 建立连接
conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="test02",
    charset="utf8")

🍪# 创建游标
# cursor = conn.cursor()  # 返回元组数据格式结果
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 返回字典数据格式结果

🍪# 增操作 insert
sql = "insert user(name,passwd) value(%s,%s)"
res0 = cursor.execute(sql,("Chirs","000000"))  # 指定一个进行添加
res1 = cursor.executemany(sql,(("Tom","333333"),("Bob","444444")))  # 指定多个进行增加(元组或列表都可以)
print("insert one row : ",res0)  # 影响记录条数
print("insert many row : ",res1)  # 影响记录条数

🍪# 改操作 update
sql2 = "update user set name=%s where name=%s"
res2 = cursor.execute(sql2,("BigTom","Tom"))
print("update row : ",res2)

🍪# 删操作 delete
sql3 = "delete from user where id=%s"
res3 = cursor.execute(sql3,2)
print("delete row : ",res3)

🍪# 提交 : commit (必要, 否则无以上操作效)
conn.commit()

🍪# 关闭游标
cursor.close()

🍪# 关闭数据库连接
conn.close()

四. 查操作 (fetchone、fetchmany、fetchall)

fetchone() : 获取一行记录, 每次获取下一行, 第一行为行首 (记录取完返回 None)

fetchall() : 获取所有记录 (取完返回空元组或空列表)

fetmany(n) : 获取指定几行的记录 (取完返回空元组或空列表)

🪐1.fetchone()

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="test02",
    charset="utf8")

# cursor = conn.cursor()  # 返回元组格式数据
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 返回字典格式的数据

sql = "select * from user"
res = cursor.execute(sql)
print("row : ",res)
print(cursor.fetchone())  # 获取一行
print(cursor.fetchone())  # 接着获取一行
print(cursor.fetchone())  # 又获取一行
print(cursor.fetchone())  # 又获取一行
print(cursor.fetchone())  # 又获取一行
print(cursor.fetchone())  # 又获取一行 (取完返回 None)

cursor.close()
conn.close()

🪐2.fetchall()

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from user"
res = cursor.execute(sql)
print("row : ",res)
print(cursor.fetchall())  # 获取全部的记录

🪐3.fetchmany(n)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from user"
res = cursor.execute(sql)
print("row : ",res)
print(cursor.fetchmany(1))  # 获取 1 条记录
print(cursor.fetchmany(2))  # 再获取 2 条记录
print(cursor.fetchmany(2))  # 再获取 2 条记录, 总共五条, 这里超过五条也只能取到 5 条
print(cursor.fetchmany(1))  # 再获取 1 条记录

五. 指针移动 (scroll)

我们使用 fetchone() 每次获取一行, 并且首次默认从第一行获取, 第二次接着上一次的位置继续获取, 这相当于一个指针, 当指针到最后时就没有内容了, 所以我们可以操作指针来自定义获取的记录 :

1. 指针的两种模式

• relative : 相对当前位置移动 (默认相对移动, 可以不用写)
• absolute : 相对绝对位置移动

sursor.scroll(1,mode="relative")  
sursor.scroll(3,mode="absolute")  
# 第一个参数是移动的行数, 正数是向下移动, 负数是向上移动, mode 是自定移动模式

2. 示例

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="test02",
    charset="utf8")

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql="select * from user"
res = cursor.execute(sql)
print("row : ",res)

print("1 : ",cursor.fetchone())  # 获取一行, 拿到四一行记录, 指针移动到第二行前面
print("2 : ",cursor.fetchone())  # 再获取一行, 拿到第二行记录, 指针移动到第三行前面
cursor.scroll(-1,mode='relative')  # 让指针相对当前行向上移动了一行, 指针跑到第二行前面
print("2 : ",cursor.fetchone())  # 获取一行, 现在拿到的也是第二行记录

cursor.scroll(0,mode='absolute')  # 设置绝对, 相对首行移动 0 个位置, 指针在第一行前面
print("1 : ",cursor.fetchone())  # 获取一行, 这一行应该是第一行
cursor.scroll(4,mode='absolute')  # 相对于首行移动 4 个位置, 指针在第五行上面
print("5 : ",cursor.fetchone())  # 获取一行, 这一行应该是最后一行

cursor.close()
conn.close()

六. 事务处理 : 回滚 (rollback())

rollback() : 回滚到初始状态

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="test02",
    charset="utf8")

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

user_info = [("aaa","666"),("bbb","777"),("ccc")]  # 三个用户, 其中 "ccc" 信息缺少密码参数
sql = "insert user(name,passwd) value(%s,%s)"

try:  # 检测异常
    for info in user_info:
        cursor.execute(sql,info)
    conn.commit()
except Exception as E:
    print(E)  # 打印异常信息
    conn.rollback()  # 出现异常回滚到初始状态

# 查看回滚后的记录有没有改变
sql2 = "select * from user"
cursor.execute(sql2)
for line in cursor.fetchall():
    print(line)

cursor.close()
cursor.close()

7. 获取插入的最后一条记录的自增 ID (lastrowid())

lastrowid : 字面意思, 最后行的 id (自增的 id)

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="test02",
    charset="utf8")

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'insert user(name,passwd) value(%s,%s)'
cursor.execute(sql,("Summer","999999"))
conn.commit()
print(cursor.lastrowid)  # 如果上一条记录的自增 id 是 15, 那么得出来的结果就是 16

cursor.close()
conn.close()

----end----