🍖pymysql模块的使用

引入

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;

image-20210207125739380

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

image-20210207145554069

image-20210207145836910

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 = "select * from user where name='%s' and passwd='%s'" %(username,userpwd)
res = cursor.execute(sql)

cursor.close()
conn.close()

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

image-20210207140054292

image-20210207140122579

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

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

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

1.注入问题一

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

image-20210207141229125

image-20210207141701234

2.注入问题二

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

image-20210207141811142

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方法中,可以是列表或元组形式,自动帮我们解决注入问题

image-20210207143910884

image-20210207144028331

image-20210207144115783

三.增、删、改操作 : (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()

image-20210208122240684

image-20210208122625176

四.查操作 (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()

image-20210208131325711

image-20210208131436171

🪐2.fetchall( )

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

image-20210208131911086

image-20210208132256489

🪐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 条记录

image-20210208132957130

五.指针移动 (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()

image-20210208140716900

六.事务处理 : 回滚 (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()

image-20210208143907490

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

image-20210208144640197

----end----

posted @ 2021-02-08 14:48  给你骨质唱疏松  阅读(217)  评论(0)  编辑  收藏  举报