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 :
- 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
- 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
- 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 .
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
The sidebar is recommended
- Python data analysis - linear regression selection fund
- How to make a python SDK and upload and download private servers
- Python from 0 to 1 (day 20) - basic concepts of Python dictionary
- Django -- closure decorator regular expression
- Implementation of home page and back end of Vue + Django tourism network project
- Easy to use scaffold in Python
- [Python actual combat sharing] I wrote a GIF generation tool, which is really TM simple (Douluo continent, did you see it?)
- [Python] function decorators and common decorators
- Explain the python streamlit framework in detail, which is used to build a beautiful data visualization web app, and practice making a garbage classification app
- Construction of the first Django project
guess what you like
-
Python crawler actual combat, pyecharts module, python realizes the visualization of river review data
-
Python series -- web crawler
-
Plotly + pandas + sklearn: shoot the first shot of kaggle
-
How to learn Python systematically?
-
Analysis on several implementations of Python crawler data De duplication
-
leetcode 1616. Split Two Strings to Make Palindrome (python)
-
Python Matplotlib drawing violin diagram
-
Python crawls a large number of beautiful pictures with 10 lines of code
-
[tool] integrated use of firebase push function in Python project
-
How to use Python to statistically analyze access logs?
Random recommended
- How IOS developers learn Python Programming 22 - Supplement 1
- Python can meet any API you need
- Python 3 process control statement
- The 20th of 120 Python crawlers, 1637. All the way business opportunity network joined in data collection
- Datetime of pandas time series preamble
- How to send payslips in Python
- [Python] closure and scope
- Application of Python Matplotlib color
- leetcode 1627. Graph Connectivity With Threshold (python)
- Python thread 08 uses queues to transform the transfer scenario
- Python: simple single player strange game (text)
- Daily python, chapter 27, Django template
- TCP / UDP communication based on Python socket
- Use of pandas timestamp index
- leetcode 148. Sort List(python)
- Confucius old book network data collection, take one anti three learning crawler, python crawler 120 cases, the 21st case
- [HTB] cap (datagram analysis, setuid capability: Python)
- How IOS developers learn Python Programming 23 - Supplement 2
- How to automatically identify n + 1 queries in Django applications (2)?
- Data analysis starts from scratch. Pandas reads HTML pages + data processing and analysis
- 1313. Unzip the coding list (Java / C / C + + / Python / go / trust)
- Python Office - Python edit word
- Collect it quickly so that you can use the 30 Python tips for taking off
- Strange Python strip
- Python crawler actual combat, pyecharts module, python realizes China Metro data visualization
- DOM breakpoint of Python crawler reverse
- Django admin custom field stores links in the database after uploading files to the cloud
- Who has powder? Just climb who! If he has too much powder, climb him! Python multi-threaded collection of 260000 + fan data
- Python Matplotlib drawing streamline diagram
- The game comprehensively "invades" life: Python releases the "cool run +" plan!
- Python crawler notes: use proxy to prevent local IP from being blocked
- Python batch PPT to picture, PDF to picture, word to picture script
- Advanced face detection: use Dlib, opencv and python to detect face markers
- "Python 3 web crawler development practice (Second Edition)" is finally here!!!!
- Python and Bloom filters
- Python - singleton pattern of software design pattern
- Lazy listening network, audio novel category data collection, multi-threaded fast mining cases, 23 of 120 Python crawlers
- Troubleshooting ideas and summary of Django connecting redis cluster
- Python interface automation test framework (tools) -- interface test tool requests
- Implementation of Morse cipher translator using Python program