TOP

Python开发【笔记】:为什么pymysql重连后才能查到新添加的数据(一)
2017-09-30 12:54:27 】 浏览:10177
Tags:Python 开发 笔记 为什么 pymysql 连后 才能 查到新 添加 数据

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 |
+-------  
		
Python开发【笔记】:为什么pymysql重连后才能查到新添加的数据(一) https://www.cppentry.com/bencandy.php?fid=77&id=120817

首页 上一页 1 2 3 4 5 下一页 尾页 1/5/5
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇洗礼灵魂,修炼python(6)--活起.. 下一篇洗礼灵魂,修炼python(5)--pyth..