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 |
+-------