Python开发【笔记】：为什么pymysql重连后才能查到新添加的数据 - Python

TOP

Python开发【笔记】：为什么pymysql重连后才能查到新添加的数据(二)

2017-09-30 12:54:27 【大中小】浏览:9987次

----------+ 1 row in set (0.00 sec)

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

REPEATABLE READ    
The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.

重复读

　　那么此时问题就找到了，跟当前的事务级别有关系的；当创建查询事务时，事务一直没有进行更新，每次查询到的数据都是之前查询结果的快照，下面会详细介绍每种事务隔离级别的区别

解决：

知道了具体原因是事务级别的问题，导致查询事务并没有更新，那么针对事务隔离级别进行应对就可以了，此类问题有三种解决方案，修改事务隔离级别、每次查询后更新事务、关闭数据库的事务（慎选）

① 每次查询后更新事务：

# 第一种方案，每次查询后进行commit操作，进行事务更新

import pymysql
import time
# Connect to the database
connection = pymysql.connect(host='192.168.1.134',
                             port=3306,
                             user='remote',
                             password='tx_1234abc',
                             db='Jefrey',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor,
                             )
while True:
    with connection.cursor() as cursor:
        # Create a new record
        sql = " select * from users WHERE email=%s"
        # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        rows_count = cursor.execute(sql, ('webmaster@python.org'))
        result = cursor.fetchall()
        print(result)
        connection.commit()           # 新增
    time.sleep(1)

# 第二种方案，创建connect连接时，autocommit=True，自动进行commit提交
import pymysql
import time
# Connect to the database
connection = pymysql.connect(host='192.168.1.134',
                             port=3306,
                             user='remote',
                             password='tx_1234abc',
                             db='Jefrey',
                             charset='utf8mb4',
                             autocommit = True,        #新增
                             cursorclass=pymysql.cursors.DictCursor,
                             )
while True:
    with connection.cursor() as cursor:
        # Create a new record
        sql = " select * from users WHERE email=%s"
        # sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        rows_count = cursor.execute(sql, ('webmaster@python.org'))
        result = cursor.fetchall()
        print(result)
        # connection.commit()
    time.sleep(1)

打印输出，可以查到新更新的数据：

()
()
[{u'password': u'test', u'id': 340, u'email': u'webmaster@python.org'}]
[{u'password': u'test', u'id': 340, u'email': u'webmaster@python.org'}]
[{u'password': u'test', u'id': 340, u'email': u'webmaster@python.org'}, {u'password': u'test', u'id': 341, u'email': u'webmaster@python.org'}]

② 修改事务隔离级别（具体级别详情下面介绍）　

　　设置隔离级别命令 set [global/session] transaction isolation level xxxx; 如果使用global则修改的是数据库的默认隔离级别，所有新开的窗口的隔离级别继承自这个默认隔离级别如果使用session修改，则修改的是当前客户端的隔离级别，和数据库默认隔离级别无关。当前的客户端是什么隔离级别，就能防止什么隔离级别问题，和其他客户端是什么隔离级别无关。

# 设置事务隔离级别
mysql> set global transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 130
Server version: 5.6.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+---------

首页上一页 1 2 3 4 5 下一页尾页 2/5/5
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】

上一篇：洗礼灵魂，修炼python（6）--活起..	下一篇：洗礼灵魂，修炼python（5）--pyth..