18

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?

Shay
  • 1,375
  • 5
  • 16
  • 26
  • 1
    I suspect pymysql is automatically starting a transaction which you are not explicitly committing, so its rolling back when you close the connection. – Joe Day Jul 20 '13 at 02:32
  • So what should i do to fix it? remove the cursor.close() connection.close()? – Shay Jul 20 '13 at 02:37
  • 4
    try a call to `connection.commit()` after your call to `cursor.execute()` – Joe Day Jul 20 '13 at 02:41
  • It worked! but can you try to explain to me what .commit() exactly does so I can understand what went wrong? – Shay Jul 20 '13 at 02:45

2 Answers2

27

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.

Joe Day
  • 6,965
  • 4
  • 25
  • 26
24

In fact, what @JoeDay has described above has little to do with default MySQL transaction's behaviour. MySQL by default operates in auto-commit mode and normally you don't need any additional twist to persist your changes:

By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. The change cannot be rolled back.

PEP-249's (DB API) authors decided to complicate things and break Zen of Python by making a transaction's start implicit, by proposing auto-commit to be disabled by default.

What I suggest to do, is to restore MySQL's default behaviour. And use transactions explicitly, only when you need them. 

import pymysql

connection = pymysql.connect(autocommit=True)

I've also written about it here with a few references.

saaj
  • 23,253
  • 3
  • 104
  • 105
  • 1
    This is the best answer! – radtek Jan 13 '18 at 19:56
  • Turning on autocommit can significantly decrease the performance of transactions. Not in the author's simple case, though. – ZuOverture Apr 24 '18 at 15:27
  • @ZuOverture Autocommit has nothing to do with *performance of transactions* as such. You, as a developer, control granularity and isolation of transactions which directly affects their performance. – saaj Apr 24 '18 at 17:54
  • @saaj, I wasn't talking about performance of their intrinsics, obviously. Autocommit is not off by default in pymysql for no reason, it encourages learning how to organize transactions to avoid excessive autocommit overhead. – ZuOverture Apr 24 '18 at 18:34
  • The answer as always is: it depends. Explicit commits are needed in Oracle and they change performance in a very noticeable way. In MySQL the difference is negligible and they just over complicate things. In case of doubt, always measure! – Nicolay77 Dec 06 '19 at 10:02
  • saved my life. Thanks – Abpostman1 Jun 18 '23 at 06:59