Python基础学习（42） PyMySQL模块 增删改和sql注入 数据备份和事务

Python基础学习（42） PyMySQL模块 增删改和sql注入 数据备份和事务

一、今日内容

• PyMySQL模块
• 增删改和sql注入
• 数据备份和事务

二、PyMySQL模块

PyMySQL模块主要是利用Python实现对MySQL数据库的操作，首先利用pip下载PyMySQL模块，打开cmd命令提示符，输入pip install pymysql，等待模块下载完成。这次我们的数据库仍然使用之前已建立的homework数据库；

1. PyMySQL模块的读取操作

   import pymysql
   
   conn = pymysql.connect(host='127.0.0.1', user='root', password='123', database='homework')
   cur = conn.cursor()  # 获取cursor游标
   
   cur.excute('select * from student')  # 可以不加分号
   """
   fetch操作
   """
   
   cur.close()  # 关闭游标，及时释放操作系统资源
   conn.close()  # 关闭连接，及时释放操作系统资源
   

2. fetch操作

   • fetchone()：获取一条结果

     for i in range(cur.rowcount):  # cur.rowcount是cursor的属性，为获取游标的行数
     	ret = cur.fetchone()
     	print(ret)
         
         
     # 获取结果
     # (1, '男', 1, '理解')
     # (2, '女', 1, '钢蛋')
     # (3, '男', 1, '张三')
     # ......
     

   • fetchmany()：获取多条结果

     ret = cur.fetchmany(3)
     print(ret)
     
     
     # 获取结果
     # ((1, '男', 1, '理解'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'))
     

   • fetchall()：获取全部结果

     ret = cur.fetchall()
     print(ret)
     
     
     # 获取结果
     # ((1, '男', 1, '理解'), (2, '女', 1, '钢蛋'), (3, '男', 1, '张三'), (4, '男', 1, '张一'), (5, '女', 1, '张二'), (6, '男', 1, '张四'), (7, '女', 2, '铁锤'), (8, '男', 2, '李三'), (9, '男', 2, '李一'), (10, '女', 2, '李二'), (11, '男', 2, '李四'), (12, '女', 3, '如花'), (13, '男', 3, '刘三'), (14, '男', 3, '刘一'), (15, '女', 3, '刘二'), (16, '男', 3, '刘四'))
     

   • 异常处理

     try:
         cur.execute('select * from students')
         ret = cur.fetchone()
         print(ret)
     except pymysql.err.ProgrammingError as e:
         print(e)
     
     
     # 获取结果
     (1146, "Table 'homework.students' doesn't exist")
     

三、增删改和sql注入

1. 增删改

   # 增删改
   try:
       cur.execute('insert into student values(17, "男", 1, "大壮")')
       cur.execute('update student set gender = "女" where sid = 1')
       cur.execute('delete from student where sid = 2')
       conn.commit()  # 提交
   except Exception as e:
       print(e)
       conn.rollback()  # 回滚，不执行了
   

2. sql注入

   加入我们利用数据库和python写一个登陆系统：

   # 结合数据库和python写一个登录
   user = input('username:')
   pwd = input('password:')
   conn = pymysql.connect(host='127.0.0.1', user='root', password='123', database='day42')
   cur = conn.cursor()
   
   cur.execute('select * from userinfo where user = "%s" and password = "%s"'%(user, pwd))
   

   假如利用这种方式进行登录可能会导致安全问题，如：

   cur.execute('select * from userinfo where user = "%s";-- and password = "%s"'%(user, pwd))
   

   ;--为注释功能，把之后的语句全部都屏蔽，这样的话如果我们在user项输入name;--即可屏蔽下面的语句获取该用户的用户名和密码；那么，我们该如何防止sql注入呢？

   sql = 'select * from userinfo where user = %s and password = %s'
   cur.execute(sql, (user, pwd))
   

四、事务和数据备份

1. 事务和行级锁（仅适用于InnoDB）

   假如两个客户端同时查询和修改数据库时，会需要开启事务：

   begin;
   select * from emp where id = 1 for update;  # 查询id值，for update添加行级锁
   update emp set salary = 1000000 where id = 1;  # 完成更新
   commit;  # 提交事务
   

2. 数据的备份和恢复

   • 备份数据

     mysqldump -uroot -p123 <Database> > <Absolute Path>
     

     会在该绝对路径生成一个.sql文件，里面包含<Database>数据库中的所有表；

   • 恢复数据

     source <Absolute Path>  # 首先要切换到需要恢复的数据库中
     

   • 备份库

     mysqldump -uroot -p123 --databases <Database1> <Database2> > <Absolute Path>
     

   • 恢复库

     source <Absolute Path>  # 无需切换到数据库中