2022年 11月 5日

Python与MySQL交互

每日分享：

每一个想努力的念头，都是未来的你在向现在的你求救。

目录

一、PyMySQL的使用

安装pymysql第三方包：

pymysql的使用：

1. 导入pymysql包

2. 创建连接对象

3. 获取游标对象

4. pymysql完成数据的查询操作

5. pymysql完成对数据的增删改

6. 防止SQL注入

一、PyMySQL的使用

安装pymysql第三方包：

pip install pymysql

说明：

  • 安装命令使用 pip install 第三方包名
  • 卸载命令使用 pip uninstall 第三方包
  • 可以使用pip show pymysql命令查看第三方包的信息
  • 可以使用pip list 查看使用pip命令安装的第三方包列表

pymysql的使用：

1. 导入pymysql包

import pymysql

2. 创建连接对象

调用pymysql模块中的connect()函数来创建连接对象

conn = connect(参数列表)

  • 参数host：连接的mysql主机，如果是本机则为’localhost’
  • 参数port：连接的mysql主机的端口，默认为3306
  • 参数user：连接的用户名
  • 参数password：连接的密码
  • 参数database：数据库的名称
  • 参数charset：通信采用的编码方式，一般为utf8

连接对象操作说明：

  • 关闭连接：conn.close()
  • 提交数据：conn.commit()
  • 撤销数据：conn.rollback()

3. 获取游标对象

获取游标对象的目的就是要执行sql语句，完成对数据库的增、删、改、查操作

调用连接对象的cursor()方法获取游标对象

cur = conn.cursor()

游标操作说明：

  • 使用游标执行sql语句：execute(operation [parameters])执行SQL语句，返回受影响的行数，主要用于执行insert、update、delete、select等语句
  • 获取查询结果集中的一条数据：cur.fetchone()返回一个元组
  • 获取查询结果集中的所有数据：cur.fetchall()返回一个元组，其中每条数据也是一个元组
  • 关闭游标：cur.close() 表示和数据库操作完成

4. pymysql完成数据的查询操作

  1. # 导包
  2. import pymysql
  3. # 创建连接对象
  4. conn = pymysql.connect(
  5.     host='localhost',
  6.     port=3306,
  7.     user='root',
  8.     password='数据库的密码',
  9.     database='hello',
  10.     charset='utf8'
  11. )
  12. # 获取游标对象（游标就好比一个搬运工，把sql语句交给数据库，把数据库返回的结果交给python）
  13. cursor = conn.cursor()
  14. sql = 'select * from students;'
  15. # 执行sql语句
  16. cursor.execute(sql)
  17. # 获取查询结果（fetchone返回第一个结果，fetchall返回所有结果）
  18. # row = cursor.fetchone()
  19. # print(row)
  20.  
  21. res = cursor.fetchall()
  22. for i in res:
  23.     print(i)
  24.  
  25. # 关闭游标
  26. cursor.close()
  27. # 关闭连接
  28. conn.close()

结果：

我们再对比ubuntu中数据库的数据：

5. pymysql完成对数据的增删改

  1. import pymysql
  2. conn = pymysql.connect(
  3.     user='root',
  4.     password='密码',
  5.     host='localhost',
  6.     database='hello',
  7.     port=3306,
  8.     charset='utf8'
  9. )
  10. cursor = conn.cursor()
  11. # sql = 'insert into classes(name) values("新增数据");'
  12. # sql = 'update classes set name="修改数据" where id=4;'
  13. sql = 'delete from classes where id=4;'
  14. try:
  15.     # 执行sql语句
  16.     cursor.execute(sql)
  17.     # 提交增删改数据
  18.     conn.commit()
  19. except Exception as e:
  20.     # 回滚数据，即撤销刚刚进行的sql语句操作
  21.     conn.rollback()
  22. finally:
  23.     cursor.close()
  24.     conn.close()

说明：

  • conn.commit() 表示将修改操作提交到数据库
  • conn.rollback() 表示回滚数据

添加数据之前：

运行python添加数据之后：

运行python修改数据之后：

运行python删除数据之后：

6. 防止SQL注入

什么是SQL注入？

用户提交带有恶意的数据与SQL语句进行字符串方式的拼接，从而影响了SQL语句的语义，最后出现数据泄露的现象

如何防止SQL注入？

SQL语句参数化：

  • SQL语言中的参数使用%s来占位，这不是python中的字符串格式化操作
  • 将SQL语句中%s占位所需要的参数存在一个列表（或元组或字典）中，把参数列表传递给execute方法中的第二个参数

SQL注入代码：

  1. # 导包
  2. import pymysql
  3. # 创建连接对象
  4. conn = pymysql.connect(
  5.     host='localhost',
  6.     port=3306,
  7.     user='root',
  8.     password='密码',
  9.     database='hello',
  10.     charset='utf8'
  11. )
  12. # 获取游标对象（游标就好比一个搬运工，把sql语句交给数据库，把数据库返回的结果交给python）
  13. cursor = conn.cursor()
  14. sql = "select * from students where name= '%s';" % "张三' or 1 = 1 or'"
  15. # 执行sql语句
  16. cursor.execute(sql)
  17. # 获取查询结果（fetchone返回第一个结果，fetchall返回所有结果）
  18. # row = cursor.fetchone()
  19. # print(row)
  20.  
  21. res = cursor.fetchall()
  22. for i in res:
  23.     print(i)
  24.  
  25. # 关闭游标
  26. cursor.close()
  27. # 关闭连接
  28. conn.close()

结果（返回了所有数据）：

防止SQL注入代码：

  1. # 导包
  2. import pymysql
  3. # 创建连接对象
  4. conn = pymysql.connect(
  5.     host='localhost',
  6.     port=3306,
  7.     user='root',
  8.     password='密码',
  9.     database='hello',
  10.     charset='utf8'
  11. )
  12. # 获取游标对象（游标就好比一个搬运工，把sql语句交给数据库，把数据库返回的结果交给python）
  13. cursor = conn.cursor()
  14. sql = "select * from students where name= %s;"
  15. # 执行sql语句
  16. cursor.execute(sql, ["张三' or 1 = 1 or'"])
  17. # 获取查询结果（fetchone返回第一个结果，fetchall返回所有结果）
  18. # row = cursor.fetchone()
  19. # print(row)
  20.  
  21. res = cursor.fetchall()
  22. for i in res:
  23.     print(i)
  24.  
  25. # 关闭游标
  26. cursor.close()
  27. # 关闭连接
  28. conn.close()

结果（没有查询到结果）：

正常输入时才会查询到结果：

  1. # 导包
  2. import pymysql
  3. # 创建连接对象
  4. conn = pymysql.connect(
  5.     host='localhost',
  6.     port=3306,
  7.     user='root',
  8.     password='密码',
  9.     database='hello',
  10.     charset='utf8'
  11. )
  12. # 获取游标对象（游标就好比一个搬运工，把sql语句交给数据库，把数据库返回的结果交给python）
  13. cursor = conn.cursor()
  14. sql = "select * from students where name= %s;"
  15. # 执行sql语句
  16. cursor.execute(sql, ["张三"])
  17. # cursor.execute(sql, ["张三' or 1 = 1 or'"])
  18. # 获取查询结果（fetchone返回第一个结果，fetchall返回所有结果）
  19. # row = cursor.fetchone()
  20. # print(row)
  21.  
  22. res = cursor.fetchall()
  23. for i in res:
  24.     print(i)
  25.  
  26. # 关闭游标
  27. cursor.close()
  28. # 关闭连接
  29. conn.close()