Python操作MySQL详解——PyMySQL

• • 一，Python操作数据库简介
  • 二，Python操作MySQL——PyMySQL
  • • （一）PyMySQL模块简介
    • （二）PyMySQL使用
    • （三）SQL防注入

      一，Python操作数据库简介

      Python标准数据库规范为 DB-API， DB-API定义了一系列必须的对象和数据库操作方式，以便为各种数据库系统和数据库访问程序提供一致的访问接口。

      

      开发人员将接口封装成不同的数据库操作模块，不同的数据库需要不同数据库操作模块，例如，MySQL数据库，它对应以下操作模块：https://wiki.python.org/moin/MySQL

      

      其中，最常用的应该是

      1，MySQL-python：也就是MySQLdb，底层是通过C操作MySQL，效率高，但是只支持py2，不支持py3。

      2，mysqlclient：是MySQL-python的一个分支。它增加了Python 3支持，并修复了许多错误。Django文档推荐的MySQL依赖库。

      3，PyMySQL：纯Python实现的模块，可以与Python代码兼容衔接，并也几乎兼容MySQL-python。

      4，MySQL Connector/Python：MySQL官方推出的纯Python实现的模块。

      二，Python操作MySQL——PyMySQL

      （一）PyMySQL模块简介

      纯Python实现的模块，可以与Python代码兼容衔接，并也几乎兼容MySQL-python。遵循 Python 数据库 API v2.0 规范。安装PyMySQL需要满足以下需求。

      

      （二）PyMySQL使用

      1，连接数据库

      使用connect函数创建连接对象，此连接对象提供关闭数据库、事务提交、事务回滚等操作。

      传入参数有很多，具体参考文档，一般参数基本连接信息 host, user, password, port(默认为3306), database。

      import pymysql
      conn = pymysql.connect(
          host='127.0.0.1',
          user='xxx',
          password='xxxx',
          port=3306,
          database='nba_data'
      )
      

      主要方法

      方法	功能
      cursor()	获取游标对象，操作数据库，如执行DML操作，调用存储过程等
      commit()	提交事务
      rollback()	回滚事务
      close()	关闭数据库连接

      2，操作数据库

      使用Cursor对象与数据库进行交互。具体方法参考：https://pymysql.readthedocs.io/en/latest/modules/cursors.html

      2.1，查询操作

      import pymysql
      conn = pymysql.connect(
          host='127.0.0.1',
          user='python_link',
          password='python_link',
          port=3306,
          database='nba_data'
      )
      # 创建游标，查询数据以元组形式返回
      # cursor = conn.cursor()
      # 创建游标，查询数据以字典形式返回
      cursor = conn.cursor(pymysql.cursors.DictCursor)
      sql = 'select * from player_info_test limit 10'
      try:
          cursor.execute(sql)
          # result = cursor.fetchall()  # 返回所有数据
          # result = cursor.fetchone()  # 返回一行数据
          result = cursor.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
          print(result)
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      

      2.2，插入操作

      插入操作中参数可以以元组、列表和字典形式传入，需要使用到占位符 “%s”，注意这只是个占位符，不同于Python 中字符串格式化中的转换说明符。

      

      # 元组、列表形式传参
      sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
      try:
          cursor.execute(sql, ("999999", "kenny", 28))
          conn.commit()
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      # 字典形式传参
      sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%(person_id)s, %(person_name)s, %(age)s)'
      try:
          cursor.execute(sql, {"person_id": "999998", "person_name": "kenny", "age": 28})
          conn.commit()
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      

      2.3，更新操作

      sql = 'update  player_info_test set PERSON_NAME=%s where PERSON_ID = %s'
      try:
          cursor.execute(sql, ['liu', '999998'])
          conn.commit()
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      

      2.4，删除操作

      sql = 'delete from  player_info_test where PERSON_ID = %s'
      try:
          cursor.execute(sql, ['999999'])
          conn.commit()
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      

      增删改需要有提交事务的操作，除了execute方法，还有批量操作方法executemany()。

      2.5，批量插入操作

      sql = 'insert into player_info_test(PERSON_ID,PERSON_NAME,AGE) VALUES(%s, %s, %s)'
      param = [("999999", "kenny", 28), (["999998", "liu", 28)]   # 元组列表作为传入参数
      try:
          cursor.executemany(sql, param)
          conn.commit()
      except Exception as e:
          conn.rollback()
          print(e)
      finally:
          cursor.close()
          conn.close()
      

      （三）SQL防注入

      SQL注入是一种常见的网络攻击手法，它利用sql的语法特性和程序员编写程序时产生的漏洞，用一些特殊符号的组合产生特殊的含义，使得正常的sql语句失效，从而逃脱正常的业务逻辑，完成一些如跳过密码验证等的非法操作。

      产生原因：SQL语句使用了动态拼接的方式。

      比如，登录时，使用以下SQL查询验证用户信息

      'SELECT username FROM user WHERE username = %s AND password = %s' % (username ,password)
      

      并且，没有对用户的输入做任何处理，直接放到了SQL语句中。那么，当黑客输入了’jjyang’ OR 1=1 – jjyang 作为用户名时，原来的SQL语句就会变成下面的样子：

      SELECT username FROM user WHERE username = 'jjyang' OR 1=1 -- jjyang AND password=''
      

      WHERE username = ‘jjyang’ OR 1=1 是一个恒成立的条件，所以无论输入什么用户名都会返回True；而–后面的语句被当作注释忽略掉了，密码验证也被跳过。最终，绕过验证，成功登录。

      针对参数不要采用拼接处理，交给pymysql中的方法（execute）自动处理，并对输入数据进行检查校验

      # 元组,列表形式
      param = ('jjyangs','123445')
      sql = 'SELECT username FROM user WHERE username = %s AND password = %s'
      cursor.execute(sql, param)
      # 字典形式
      param = {'name':'jjyangs','pwd':'123445'}
      sql = 'SELECT username FROM user WHERE username = %(name)s AND password = %(pwd)s'
      cursor.execute(sql, param)
      

      需要注意的是，不要因为参数是其他类型而换掉 %s，pymysql 的占位符并不是 python 的通用格式化转换说明符。同时，也不要因为参数是 string 就在 %s 两边加引号，mysql 会自动去处理。