A transaction problem encountered by Python using MySQLdb

Source: Internet
Author: User
Tags mysql in

When I use mysqldb today, when I find that a record is updated, select does not update and then discovers that it is a transaction problem.

When I operate MySQL in Python, I always use mysqldb, and this MYSQLDB library is the default shutdown autocommit transaction, that is, if you do not set the set autocommit=1 explicitly in the program, the table with the transaction function, For example, when the engine's InnoDB table is modified, a commit is executed each time to actually commit the current modification.

Here are my previous settings and the autocommit values I queried

Importmysqldbdb=MySQLdb.connect (myhost,myuser,mypass,mydb) Db.set_character_set ('UTF8') Cursor= Db.cursor (Cursorclass =MySQLdb.cursors.DictCursor) Cursor.execute ('SET NAMES UTF8;') SQL='Show variables like "autocommit"'cursor.execute (SQL) record=Cursor.fetchone ()PrintRecord#execution results are{'Value':'OFF','variable_name':'autocommit'}

This didn't happen before. Today, however, when I write a circular query function for a queue, I find that if I query a record first, then modify the record and then query this record, I cannot find the modified record.

The problem was in my use of the business. Although I used a commit for a statement with a modification operation (update,delete), I did not commit the select, which would cause my select to always be in a transaction, I can't query the record in this transaction, unless I explicitly commit the current transaction before I make the next query, so that I can get the latest data when I query again.

Fix it: Once the database is created, I explicitly specify Autocommit as 1, then the select problem is resolved

 #  -*-coding:utf-8-*- import   mysqldbdb  = MySQLdb.connect (Myhost,myuser,mypass,mydb) db.set_character_set (  " Span style= "COLOR: #800000" >utf8   " ) cursor  = Db.cursor (cursorclass = MySQLdb.cursors.DictCursor) Cursor.execute ( Span style= "COLOR: #800000" > " set NAMES UTF8;   " ) cursor.execute (  "  SET autocommit=1;    ") #   explicitly specifies that you want to automatically commit a transaction  

PS. Why has not been a problem before, because the previous execution of this script with a scheduled task, each time the program executes the exit, MySQL connection automatically shut down, and now I am writing a queue processing script has been executed, MySQL connection is continuous, the problem is exposed.

A transaction problem encountered by Python using MySQLdb

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.