为什么pymysql重连后才能查到被其他地方修改的数据 pymysql缓存？

如题

查阅资料后发现是事务的隔离级别导致的这个问题。

InnoDB 的默认隔离级别。它可以防止任何被查询的行被其他事务更改，从而阻止不可重复的读取，而不是 幻读取。它使用中度严格的锁定策略，以便事务内的所有查询都会查看同一快照中的数据，即数据在事务开始时的数据。

那么此时问题就找到了，跟当前的事务级别有关系的；当创建查询事务时，事务一直没有进行更新，每次查询到的数据都是之前查询结果的快照。

此类问题有三种解决方案，修改事务隔离级别、每次查询后更新事务、关闭数据库的事务（慎选）

解决方案：

http://www.bkjia.com/Pythonjc/1228355.html

数据库连接公共方法

import time
import mysql.connector
from mysql.connector.connection_cext import CMySQLCursorDict
import threading

lock = threading.Lock()


class MysqlConn:
    def __init__(self, host, username, password, port, database, charset):
        """
        mysql 初始化
        :param host:
        :param username:
        :param password:
        :param port:
        """
        try:
            self.db = mysql.connector.connect(host=host, user=username, password=password,
                                              database=database, port=port, charset=charset, autocommit=True)
            # 这里 我使用了 autocommit=True 的选项 来让每次提交都去调用commit 函数 即更新事务
            self.cursor = self.db.cursor(cursor_class=CMySQLCursorDict)
        except mysql.connector.Error as e:
            print(e.args)

    def insert(self, table, data):
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql = 'insert into %s (%s) values (%s)' % (table, keys, values)
        try:
            self.cursor.execute(sql, tuple(data.values()))
            self.db.commit()
        except mysql.connector.Error as e:
            print(e.args)
            self.db.rollback()

    def exec_sql(self, sql, params=None):
        try:
            if params:
                self.cursor.execute(sql, params)
            else:
                self.cursor.execute(sql)
            self.db.commit()
        except mysql.connector.Error as e:
            print('mysql insert error:' + str(e.args))
            self.db.rollback()

    def insert_many(self, table, data):
        if isinstance(data, list) or isinstance(data, tuple):
            keys = str(tuple(data[0].keys())).replace('(', '').replace(')', '').replace("'", '')
            sql = 'insert into %s (%s) value' % (table, keys) + ' (' + '%s, ' * (len(data[0].keys()) - 1) + '%s);'
            values = [tuple(d.values()) for d in data]
            if lock.acquire():
                try:
                    self.cursor.executemany(sql, values)
                    self.db.commit()
                except mysql.connector.Error as e:
                    print(e.args)
                    self.db.rollback()
                finally:
                    lock.release()

    def find_key_val(self, table, key_val):
        sql = None
        if isinstance(key_val, str):
            sql = 'SELECT * FROM `%s` WHERE %s ;' % (table, key_val)
        elif isinstance(key_val, dict):
            sql_where = ''
            for item in key_val.items():
                row_str = str(item[0]) + '=' + str(item[1]) + ' and '
                sql_where += row_str
            sql_where = sql_where[:-4]
            sql = 'SELECT * FROM `%s` WHERE %s ;' % (table, sql_where)
        if sql:
            try:
                self.cursor.execute(sql)
                data = self.cursor.fetchall()
                if len(data) > 0:
                    return data
                return None
            except mysql.connector.Error as e:
                print(e.args)
        return None

    def find_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                data = self.cursor.fetchall()
                if len(data) > 0:
                    return data
                return None
            except mysql.connector.Error as e:
                print(e.args)
        return None

    def del_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                self.db.commit()
                return True
            except mysql.connector.Error as e:
                print(e.args)
                self.db.rollback()
        return None

    def update_by_sql(self, sql):
        if sql:
            try:
                self.cursor.execute(sql)
                self.db.commit()
                return True
            except mysql.connector.Error as e:
                print(e.args)
                self.db.rollback()
        return None

    def close(self):
        try:
            self.cursor.close()
        except:
            pass
        try:
            self.db.close()
        except:
            pass

如果报错缺失DLL文件 需要下载安装mysql-connector驱动  (Windows使用)

https://dev.mysql.com/downloads/connector/python/ 

Ubuntu端的报错不一样：

(-1, 'Cursor class needs be to subclass of cursor.CursorBase', None)
Traceback (most recent call last):
  File "/myServices/strategy_files/python_strategy/t6N95eTlPFUv3E/run_main_ePCEsD64.py", line 21, in <module>
    info = get_strategy_base_params(options.strategyId)
  File "/myServices/strategyManagement/strategyPackage/readBaseData.py", line 143, in get_strategy_base_params
    strategy = mysql_conn.find_key_val('strategy', {'id': strategy_id})
  File "/myServices/strategyManagement/strategyPackage/readBaseData.py", line 81, in find_key_val
    self.cursor.execute(sql)
AttributeError: 'MysqlConn' object has no attribute 'cursor'

需要安装两个包：