PyMysql UPDATE query

Question

I've been trying using PyMysql and so far everything i did worked (Select/insert) but when i try to update it just doesn't work, no errors no nothing, just doesn't do anything.

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've double checked that Users, IsConnected and Username are all correct and test does exist (SELECT works on it)

what's my problem here?

Answer 1

When you execute your update, MySQL is implicitly starting a transaction. You need to commit this transaction by calling connection.commit() after you execute your update to keep the transaction from automatically rolling back when you disconnect.

MySQL (at least when using the InnoDB engine for tables) supports transactions, which allow you to run a series of update/insert statements then have them either all commit at once effectively as a single operation, or rollback so that none are applied. If you do not explicitly commit a transaction, it will rollback automatically when you close your connection to the database.