PyMysql操控

问题描述：

　　之前做数据库模块的时候用到了pymysql，测试中发现了一个问题，创建两个程序，select.py从数据库中不断的读取，insert.py在数据库中插入多条数据，但是select.py程序查不到新添加的数据，像是做了缓存一样，查到的数据永远不变；只有重启模块，再次建立连接后，新添加的数据才能被查到；还原当时的代码如下：

查询:

# select.py 不断的进行查询

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)
    time.sleep(1)

插入：

# insert.py 进行数据插入

import pymysql.cursors
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,)
try:
    # execute方法
    with connection.cursor() as cursor:
        # Create a new record
        start = time.time()
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        for i in range(5):
            cursor.execute(sql,('webmaster@python.org','test'))
            connection.commit()
            time.sleep(1)
finally:
   connection.close()

 运行30秒，查询输出打印：

()
()
()
()
()

此时数据表中的数据确实是插入进去了：

 重新启动程序（重新建立的sql连接）后发现，之前添加的数据又都可以查到，但是新添加的数据又都查询不到？！！ 问题确实很懵逼 ，了解完下面内容后，问题会迎刃而解，开始补课！  -》》相关的问题描述

 

剖析：

想要解决上述的问题，首先要明白mysql中事务这个概念，本地的mysql数据库是默认安装的，默认存储引擎是（InnoDB），事务隔离级别是（REPEATABLE READ）：

查看存储引擎：

mysql> show engines;    # 查看数据库支持的存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show variables like '%storage_engine%';       # 当前的存储引擎
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)

查看当前的事务隔离级别：

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