06 数据备份、pymysql模块

一. IDE工具介绍

生产环境还是推荐使用mysql命令行，但为了方便我们测试，可以使用IDE工具. 下载连接 提取码: 2333

Navicat官方使用手册(超详细): https://www.navicat.com.cn/manual/online_manual/cn/navicat/win_manual/index.html

基本快捷键了解:

# 常规
新建查询: CTRL+Q
    
# Navicat 主窗口
命令列界面: F6

# ER 图表视图
刷新: F5
放大: CTRL+= 或 CTRL+滑鼠滚轮向上
缩小: CTRL+- 或 CTRL+滑鼠滚轮向下

# 表或集合设计器
查找字段: CTRL+F

# 数据查看器
查询对象（表、集合、视图、实体化视图）: CTRL+Q
    
# 查询设计器
运行或运行已选择的: CTRL+R
    
# 查询编辑器
注释或取消注释行: CTRL+/

补充知识:

二. MySQL数据备份

1. 使用mysqldump实现逻辑备份

2. 恢复逻辑备份

3. 实现自动化备份

4. 表的导出和导入

5. 数据库迁移

三. pymysql模块

前提: 配置了环境变量

使用清华源安装:

pip3 install -i http://pypi.douban.com/simple/ pymysql

1. 基本使用流程

# 为你的库创建数据
"""
drop database db1;  # 小心
create database db1 charset utf8;
use db1;
create table user(
    id int primary key auto_increment,
    username varchar(255) not null unique,
    password varchar(255)) engine=innodb;
insert into user(username, password) values('yang','123'),('egon', '123');
"""

import pymysql

# 一. 链接数据库
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1234',
    database='db1',
    charset='utf8'

    # passwd='别名密码。(为了兼容MySQLdb)'
    # db='数据库的别名。(为了兼容MySQLdb)'
)

# 二. 通过连接对象conn产生一个游标对象, 游标对象就是用来帮你执行命令的. 类始于sql中的 `mysql>`
cursor = conn.cursor()

# 三. 定义sql语句交给让游标对象执行
"""
affected_rows = cursor.execute(sql)
    execute返回的是你当前sql语句所受影响的行数(提示: 返回值一般不用)
"""
username = input("请输入账号: ").strip()
password = input("请输入账号: ").strip()

sql = 'select * from user where username="%s" and password="%s"' % (username, password)  # 注意: %s需要加引号
affected_rows = cursor.execute(sql)
print("affect_rows:", affected_rows)

if affected_rows:
    print("登录成功!")
else:
    print("登录失败!")

# 四. 关闭游标
cursor.close()

# 五. 关闭连接
conn.close()

2. 利用execute解决sql注入问题

根本原理：利用一些SQL语法的特性, 书写一些特定的语句实现固定的语法. 这里就是利用的是MySQL的注释语法。 当然还有很多种，这里只是简单的一种列举。

举例说明:

"""
web端登陆时, 在应用程序级别, 先筛选一下特殊符号或者根本不让你输入特殊符号, 从服务器下载页面代码到客户端. 客户端的web端js代码把你有一些能改变sql语句逻辑的符号, and ,or, –- 注释等全部给你过滤掉, 再往服务端发, 再拼成sql语句, 往数据库上一提交, 但是这种安全是在前端界面实现的我们可以破解.

破解: 过模拟浏览器的行为, 相当于跳过了你前端的js过滤监测, mysql服务端就嗝屁了
"""

2种sql注入介绍:

# 1、sql注入之：用户存在，绕过密码
yang" #任意字符  或者  yang"  -- 任意字符  (注意: 使用--这种注释语法后面必须跟一个空格, 后面才是任意字符. #号注释没有要求)

# 2、sql注入之：用户不存在，绕过用户与密码
xxx" or 1=1 #任意字符   或者   xxx" or 1=1 -- 任意字符  

代码示例:

# 为你的库创建数据
"""
drop database db1;  # 小心
create database db1 charset utf8;
use database;
create table user(username varchar(255) not null unique, password varchar(255)) engine=innodb;
insert into user values('yang','123'),('egon', '123');
"""

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1234',
    database='db1',
    charset='utf8'
)

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

while True:
    username = input('用户账号: ').strip()
    password = input('用户密码: ').strip()
    # 1. sql注入问题
    # sql = f'select * from user where username="{username}" and password="{password}";'  # 注意: {username}, {password}需要加引号. 分号`;`可写可不写
    # print('sql:', sql)
    # affected_rows = cursor.execute(sql)

    # 2. 让execute解决这种问题
    '''
     日常生活中很多软件在注册的时候都不能含有特殊符号. 因为怕你构造出特定的语句入侵数据库不安全. 因此敏感的数据不要自己做拼接 交给execute帮你拼接即可
    '''
    sql = 'select * from user where username=%s and password=%s;'  # 注意: %s需要去掉引号，因为pymysql会自动为我们加上. 分号`;`可写可不写
    affected_rows = cursor.execute(sql, (username, password))  # execute可以帮助我们过滤特殊符号和敏感关键字

    print('affected_rows:', affected_rows)
    if affected_rows:
        print("登录成功!")
    else:
        print('登录失败!')

3. 查询介绍(fetch系列)

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1234',
    db='db1',
    charset='utf8'
)

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

while True:
    sql = input('mysql> ').strip()
    print('sql:', sql)
    affected_rows = cursor.execute(sql)
    print('affected_rows:', affected_rows)

    # 一. cursor.fetchone(): 只拿一条数据. 有值返回字典, 没有查询结果或者值被取空都返回None
    print(cursor.fetchone())  # fetch  /fetʃ/ 拿来 中取 取来
    print(cursor.fetchone()) 
    # 执行结果:
    '''
    {'cid': 1, 'caption': '三年二班'}
    '''

    # 二. cursor.fetchall(): 拿所有数据. 有值列表套返回字典, 没有查询结果返回(), 值被取空返回[].
    # print(cursor.fetchall())
    # print(cursor.fetchall())
    # 执行结果:
    '''
    [{'cid': 1, 'caption': '三年二班'}, {'cid': 2, 'caption': '三年三班'}, {'cid': 3, 'caption': '一年二班'}, {'cid': 4, 'caption': '二年九班'}]
    '''

    # 三. cursor.fetchmany(取几行): 指定拿几条数据. 有值列表套返回字典, 没有查询结果返回(), 值被取空返回[].
    # print(cursor.fetchmany(3))
    # print(cursor.fetchmany(3))
    # 执行结果
    '''
    [{'cid': 1, 'caption': '三年二班'}, {'cid': 2, 'caption': '三年三班'}, {'cid': 3, 'caption': '一年二班'}]
    '''

4. 指针的移动介绍(scroll)

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1234',
    db='db1',
    charset='utf8'
)

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


# 一. 相对移动(第二个参数默认relative): 相对于光标所在的位置继续往后移动1位
# cursor.scroll(1, 'relative')
cursor.scroll(1)
print(cursor.fetchone())  # 注意: 读取数据类似于文件指针的移动
cursor.scroll(1)
print(cursor.fetchmany(1))  
'''
执行结果:
{'cid': 2, 'caption': '三年三班'}
[{'cid': 4, 'caption': '二年九班'}]
'''

# 二. 绝对移动: 相对于数据的开头往后继续移动1位
cursor.scroll(1, 'absolute')
print(cursor.fetchone()) 
'''
执行结果:
{'cid': 2, 'caption': '三年三班'}

5. 增删改介绍(execute系列)

提示: 针对增删改 pymysql需要二次确认才能真正的操作数据

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='1234',
    db='db1',
    charset='utf8',
    # autocommit=True  # 指定自动提交. 下面conn.commit()可以不写
)

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

"""修改前查"""
cursor.execute('select * from user;')
print(cursor.fetchall())

# """增"""
# 一. cursor.execute: 指定单个
sql = 'insert into user(username, password) values("tank", "123")'
cursor.execute(sql)

# 二. cursor.execute: 指定单个+过滤特殊符号和敏感关键字
sql = 'insert into user(username, password) values(%s, %s)'
cursor.execute(sql, ('alex', '123'))
cursor.execute(sql, ['alex1', '123'])  # 元组,列表形式都可以.

# 三. cursor.executemany: 指定多个+过滤特殊符号和敏感关键字
sql = 'insert into user(username, password) values(%s, %s)'
cursor.executemany(sql, [('aaa', '123'), ('bbb', '123'), ('ccc', '123')])

"""删"""
sql = 'delete from user where id=1'
cursor.execute(sql)


"""改"""
sql = 'update user set username="yang_NB" where id=1'
cursor.execute(sql)


# 补充:  execute之for循环实现executemany的功能
user_info = [
    ('132', '1231313'),
    ('123123', '23131'),
    ('li1231323ua', '1231421')
]
for user in user_info:
    sql = "insert into t1(name, password) values (%s, %s);"
    cursor.execute(sql, user)


# 提交
conn.commit()  # 注意: 增删改操作必须指定commit才会生效


"""修改后查"""
cursor.execute('select * from user;')
print(cursor.fetchall())

cursor.close()
conn.close()

6. 事务实现

import pymysql

# 1. 建立连接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='db1',
    charset='utf8'
)

# 2. 通过连接对象拿到游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 3. 指定sql语句
user_info = [
    ('我', '123'),
    ('扣脚少年', '4179'),
    ('啪嗒星', '5438')
]

sql = "insert into t1(name, password) values (%s, %s);"
try:
    # 4. 提交sql语句给游标, 让游标帮你执行
    affected_rows = cursor.executemany(sql, user_info)
    print(affected_rows)
    # 5. 提交执行sql语句
    conn.commit()
except Exception as e:
    print(e)
    # 6. 语句出现异常回滚到初始状态
    conn.rollback()

cursor.close()
conn.close()

7. 获取插入的最后一条数据的自增ID

import pymysql

conn = pymysql.connect(
    host='localhost',
    user='root',
    password='1234',
    database='db1'
)
cursor = conn.cursor()

sql = 'insert into user(username,password) values("xxxx","123");'
rows = cursor.execute(sql)
print(cursor.lastrowid)  # 在插入语句后查看. 获取插入的最后一条数据的自增ID, 如果插入数据之前id是10, 执行execute以后返回的值是11. 返回结果是int类型.

conn.commit()

cursor.close()
conn.close()