current position:Home>Answer 2: why can you delete the table but not update the data with the same Python code

Answer 2: why can you delete the table but not update the data with the same Python code

2022-02-01 19:11:11 GreatSQL

Welcome to GreatSQL Community shared MySQL Technical articles , If you have questions or want to learn , You can leave a message in the comment area below , When you see it, you will answer

problem

Run the following paragraph Python Code , But always unable to update the data :

import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "update t1 set c3 = rand()*10240 where c1 = rand()*1024"
cur.execute(sql)
cur.close()
conn.close()
 And run the following code that looks the same , But you can delete the table normally :

import pymysql
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
sql = "drop table tmp1"
cur.execute(sql)
cur.close()
conn.close()
 Copy code 

answer

In fact, the problem is not complicated , There are several reasons :

  • 1. The table to be written is InnoDB engine , and InnoDB The engine supports transactions , That is, after writing , To commit a transaction is to actually complete the write .

  • 2. When connecting to the database , You need to set the transaction auto commit mode by yourself , Is it on or off .

  • 3.pymysql In the module , Auto submit mode is not enabled by default .

So the table is DML In operation , The transaction needs to be committed before it can succeed .

  • 4. Deleting a table is DDL operation , at present DDL The operation does not yet support transactions , So even if Auto submit is not turned on , It can also be successful .

It's easy to know the above reasons . Let's take a look first pymysql The setting of automatic submission in the source code :

[[email protected] pymysql]# cat /usr/lib/python2.7/site-packages/pymysql/connections.py
...
# about 158 Near the line 
 158     :param autocommit: Autocommit mode. None means use server default. (default: False)
...
 Copy code 

therefore , There are several solutions :

  1. Turn on auto submit mode during connection initialization , for example :
# Set properties autocommit=1 Yes 
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4', autocommit=True)
 Copy code 
  1. Or after the execution DML After the operation , Execute it again commit request , for example :
sql = "update t1 ...
cur.execute(sql)
cur.execute("commit")
 Copy code 
  1. Or after creating the connection , modify autocommit Pattern , for example :
conn=pymysql.connect(
host = '127.0.0.1', user = 'yewen', passwd='YeWen.3306',
port= 3306, db='test', charset='utf8mb4')
cur = conn.cursor()
cur.execute("set autocommit=1")
 Copy code 

Come here , The problem of automatic submission has been solved .

But we need to go further , On or off autocommit What are the pros and cons ? in short , Here are some suggestions :

  • 1. When there is a large number of data updates , You can turn it off first autocommit, When the business is over , And then manually submit . Business commit Refresh when redo log、binlog etc. , The price is still relatively high .

  • 2. close autocommit The disadvantage of is , When you forget to actively commit a transaction , It may cause the corresponding row lock to be held all the time and not released , Other transactions will be blocked for a long time , If it's an online production environment , May cause serious consequences ( Business unavailable for a long time ).

  • 3. therefore , It needs to be dynamically adjusted according to the actual situation autocommit The pattern of , There are no general settings .

  • 4. Many development frameworks have default settings set autocommit=0, What is more , One at a time SQL front , Send it once set request , Increased unnecessary expenses , If there is such a situation , You can adjust the code of the development framework .

Enjoy MySQL :)

Article recommendation :

Technology sharing | MGR Best practices (MGR Best Practice) mp.weixin.qq.com/s/66u5K7a9u…

Technology sharing | Wanli database MGR Bug The road to repair mp.weixin.qq.com/s/IavpeP93h…

Macos System compilation percona And some functions in Macos Operational differences on the system mp.weixin.qq.com/s/jAbwicbRc…

Technology sharing | utilize systemd management MySQL Single machine multi instance mp.weixin.qq.com/s/iJjXwd0z1…

product | GreatSQL, Create better MGR ecology mp.weixin.qq.com/s/ByAjPOwHI…

product | GreatSQL MGR Optimization reference mp.weixin.qq.com/s/5mL_ERRIj…

About GreatSQL

GreatSQL It is maintained by Wanli database MySQL Branch , Focus on Improvement MGR Reliability and performance , Support InnoDB Parallel query feature , It is suitable for financial grade applications MySQL Branch version .

Gitee: gitee.com/GreatSQL/Gr…

GitHub: github.com/GreatSQL/Gr…

WeChat &QQ Group :

Scan code to add GreatSQL Community assistant wechat friend , Send verification information “ Add group ” Join in GreatSQL/MGR Exchange wechat group , You can also directly scan the code to add GreatSQL/MGR communication QQ Group .

file

This article by the blog one article many sends the platform OpenWrite Release !

copyright notice
author[GreatSQL],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/02/202202011911102165.html

Random recommended