pymysql详解（connect连接、游标cursor、获取查询结果集、获取实时数据、解决查询数据为历史数据问题、insert into需要注意的地方）

#### pymysql

​ pymysql 的语法兼容MySQLdb，是使用Python写的

​ pymysql 是可以远程连接数据库并对数据库中的数据进行操作的工具

pymysql是使用

• ​ 首先，必须先和数据库建立一个传输数据的连接通道，需要用到pymysql下的connect()方法

  ​ pymysql.connect() 方法返回的是Connections模块下的Connection 实例，connect() 方法传参就是在给Connection类的 _init_ 初始化魔术方法参数，也可以理解为 connect() 方法就是在创建新的 Connetion 类

  connect() / Connection初始化 常用参数	说明
  host	主机ip
  user	用户名
  password	密码
  database	数据库
  port	端口号
  charset	字符集

  ​ 例如：

  db = pymysql.connect(host='localhost', 
  　　　　　　　　　　　　　　user='root',
  　　　　　　　　　　　　　　password='123456', 
  　　　　　　　　　　　　　　database='userinfo',   # 所用的数据库名
  　　　　　　　　　　　　　　charset = 'utf-8')
  

- ​ 在使用pymysql.connect() 方法与数据库建立连接后，想要操作数据库时，就需要使用游标 Cursor

​ 通过连接好的数据库（此处为db）调用 cursor() 方法即可返回一个新的游标对象，在连接没有关闭之前，游标对象可以反复使用

cursor = db.cursor()

- ​ 数据库操作需要使用Cursor类的实例（即通过 db.cursor() 创建的 cursor 游标对象）提供的 execute() 方法，执行SQL语句，成功则返回结果

​ 例如：插入操作

sql = "insert into user_pwd(username,password) values('vera', '1234')" 
cursor.execute(sql)

- 这里插入数据的时候插入的表名有个需要注意的地方，插入表名的时候最好带着库名，例如：

sql = "insert into database.table(username, password)  values('xxx', '123')"

否则可能会出现如下错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

具体可以参考我的另一篇文章：pymysql中cursor.execute()执行insert into的坑及解决方法

- 查询操作

sql = "select * from table"
response1 = cursor.execute(sql)
response2 = cursor.fetchall()

这里单独说一下查询操作

这里有两个response返回，
response1是对cursor.execute(sql) 的返回数据接收，这个返回可能并不是你想要的返回值，因为它返回的是查询到的个数，是个int类型的数字
而我想response2才是你想要的返回
response2是调用了fetchall方法：查询时获取结果集中的所有行，一行构成一个元组，然后再将这些元组返回（即嵌套元组）
还有一些别的方法如下
||| （这是个箭头）
V

• 获取查询结果集的方法

  ​ 当调用 cursor.execute(sql) 方法查询数据的时候，如果执行成功，获取查询的结果集的方法有

  名称	说明
  fetchone()	获取结果集的下一行
  fetchmany(size=None)	size指定返回的行数,None则返回空元组
  fetchall()	返回剩下的所有行,如果走到末尾,就返回空元组,否则返回一个元组,其元素是每一行的记录封装的一个元组
  cursor.rownumber	返回当前行号.可以修改,支持负数
  cursor.rowcount	返回的总行数

  ​ 注意： fetch操作的是结果集，结果集是保存在客户端的，也就是说fetch的时候，查询已经结束了
  
  

• ​ Connection类提供了三个方法： begin 开始事务， commit 提交事务， rollback 回滚事务，如果通过 sql 语句对数据库中的数据进行了修改， 则需要提交事务。

  db.commit()
  

  • rollback()也是个很重要的方法，正确的使用rollback可以避免commit提交事务的时候发生错误导致程序中断。主要使用方式：结合try except捕获异常，将事务进行rollback回滚

    try:
     	
     	db.commit()
    except Exception as e:
     	db.rollback()
    

- ​ 释放资源，在程序结束时需要建立的连接即建立的游标资源释放掉，避免资源的浪费，可以调用close() 方法

连接查询的数据为历史数据（获取实时数据的方法）

-- 如果对数据即时性要求很高，或者说数据随时可能发生变化，那么close的使用将会很重要。close是将当前连接关闭。注意，这里和sqlalchemy的close不太相同，cur.close()是关闭游标，但是仅仅关闭游标是不够的，创建新的游标查询的依旧是历史数据，只有将当前db.connect数据库连接关闭，即db.close()后在重新创建连接才有效，才可以得到最新的数据；研究了一下，其实和mysql的事务有关，所以另外一种方法就是：db.commit()提交事务，即时只是查询，也将当前事务提交，开启新的事务，连接就会重新获取当前最新的内容。否则查询到的数据依旧是历史数据，而不是最新的数据。 -- 在sqlalchemy中，是通过创建引擎以及连接池的方式实现的，一个连接池中可以有多个连接，调用session.close()则是将当前连接关闭，和这里的db.close()一样，但是sqlalchemy有连接池的存在，关闭后若想继续使用连接，连接池会自动创建好连接，直接使用session即可，不需要重新创建连接，但pymysql不行，pymysql是单独创建一个连接，没有连接池的概念

if cursor:
    cursor.close()
db.close()

• ​ 在执行查询操作的时候要尽量避免 使用%s 或者 format() 的字符串替换操作，这样是为了避免sql注入的发生

  ​ sql 注入指：字符串替换操作中如果出现了 如下操作时，返回的结果将是全部数据

  sql = "select * from table where id={}".format('5 or 1=1')
  

  ​ 字符串替换的方式将会让攻击者有机可乘，攻击者猜测后台数据库的查询语句使用的拼接字符串等方式，从而经过设计为服务端传参，令其拼接处特殊字符串的SQL语句，返回攻击者想要的结果，所有，永远不要相信客户端传来的数据是规范及安全的！

  ​ 解决此问题的方法：

  ​ 参数化查询，不仅可以有效的防止注入攻击，还可以提高查询的效率

  cursor.execute(query,args=None)
  

  ​ args必须是元组 列表或字典,如果查询字符串使用%(name)s,就必须使用字典.

  ​ 例如：

  sql = "select * from table where name like %s and age < %s"
  cursor.execute(sql,('tom%',25))
  
  或者
  
  sql = "select * from table where name like %(name)s and age < %(age)s"
  cursor.execute(sql,{'name':'tom%','age':25})
  

  ​ 参数化查询提高了效率，原因是–SQL语句缓存.
  ​ 数据库服务器一般会对SQL语句编译和缓存,编译只对SQL语句部分,所以参数就算有SQL指令也不会被当作指令执行.
  ​ 编译过程,需要词法分析、语句分析、生成AST、优化、生成执行计划等过程，比较耗费资源。
  ​ 服务端会先查找是否对同一条查询语句进行了缓存，如果缓存未失效，则不需要再次编译，从而降低了编译的成 本，降低了内存消耗。
  ​ 可以认为SQL语句字符串就是一个key，如果使用拼接方案，每次发过去的SQL语句都不一样，都需要编译并缓存。 大量查询的时候，首选使用参数化查询，以节省资源。

- ​ 上下文管理： 不关闭游标对象cursor 和 数据库对象db，就可以接着用，连接（db）应该不需要反反复复创建销毁，应该是多个cursor共享一个db.

​ 例如：

import pymysql

db = pymysql.connect(ip,user,password,database,port)

with db.cursor() as cursor:
    sql = "select * from table where id=%s"
    cursor.execute(sql,5)
    print(cursor.fetchall())
    
cursor.close()
db.close()