Query UPDATE PyMysql

Question

I try to use PyMysql, and so far everything that I have done (Select / insert), but when I try to update it, it just does not work, there are no errors, it just does nothing.

import pymysql connection = pymysql.connect(...) cursor = connection.cursor() cursor.execute("UPDATE Users SET IsConnected='1' WHERE Username='test'") cursor.close() connection.close() 

and yes, I double checked that the users, IsConnected and Username are all correct and the test exists (SELECT is working on it)

what's my problem here?

Answer 1

When you perform your upgrade, MySQL implicitly starts the transaction. You must commit this transaction by calling connection.commit() after completing your update so that the transaction automatically rolls back when disconnected.

MySQL (at least when using the InnoDB engine for tables) supports transactions that allow you to run a series of update / insert statements, and then use them either commit immediately as efficiently as a single operation, or rollback so that none are used. If you are not explicitly committing a transaction, it will automatically roll back when you close your database connection.

Answer 2

In fact, @JoeDay described above has little to do with MySQL default behavior. MySQL by default runs in auto-commit mode , and usually you don't need any additional changes to save the changes:

By default, MySQL works with autocommit enabled. This means that as soon as you execute the statement that updates (changes) the table, MySQL saves the update on disk to make it permanent. This change cannot be undone.

The authors of PEP-249 (DB API) decided to complicate the situation and break Zen Python, making the transaction run implicit, offering automatic commit by default.

What I propose to do is restore the default MySQL behavior. And use transactions explicitly only when you need it.

 import pymysql connection = pymysql.connect(autocommit=True) 

I also wrote about this here with a few links.