Python, MySQLdb, unable to update row

Question

I know SO is not a "debug my code service" but after checking and retrying for hours either I'm missing something very stupid or maybe there's a bug (or bad compilation) in my MySQLdb module...

Also I've some related question I've put along with the posted code...

def NextDocumentIdToCache():
 if not OpenConnection():
 return 0
 # ...setting string values... #
 cur = connection.cursor(mysql.cursors.DictCursor)
 cur.execute('SELECT * FROM documents WHERE resolutions <> %s AND pdf > 0 AND status = %s AND expire > %s AND locked = %s LIMIT 0,1', (resolutions, status, expireLimit, ''))
 rowsCount = cur.rowcount
 if rowsCount==0:
 return 0
 row = cur.fetchone()
 id = row['document_id'] 

Everything ok since now. Connection opens, I get one row and retrieve the correct id that is returned at the end of the function.

Then I need to perform an update operation on the row fetched...

 cur.close()
 cur = connection.cursor(mysql.cursors.Cursor) 

I've closed the cursor and opened a new one. Do I actually need to do this? Or can I reuse the same cursor?

 cur.execute("""UPDATE documents SET locked = %s WHERE document_id = %s""", ("worker: rendering pages", id))

This actually doesn't update the row. No exceptions happens, just don't work.

Finally the function ends...

 cur.close()
 return id

Also a couple of questions.

What's the difference between

cur.execute("""UPDATE documents....

and

cur.execute("UPDATE documents....

I've seen around both versions. What's the functional difference between triple double-quote, single double-quote, single single-quote?

And finally

If I write the query

cur.execute("""UPDATE documents SET locked = %s WHERE document_id = %d""", ("worker: rendering pages", id))

(note the %d instead of %s) I get an error. But id is a long integer, I've checked. So what's wrong?

Thank you

Answer 1

The change doesn't take effect:

Are you doing a transaction? You'll need to commit() if that's the case. PEP 249, which defines the Python Database API, states that a connection will use transactions even if you have not explicitly enabled them: "Note that if the database supports an auto-commit feature, this must be initially off."

Quoting

Triple quoted strings are multiline, single quoted strings aren't.

Interpolation

Because this is not actually string interpolation. Those placeholders are interpreted by MySQLdb, which will handle quoting for you.

• Paolo: Not explicitly (I didn't wrote code to start a transaction. Just opened the connection and executed the two queries) However adding connection.commit() at the end solved the prolem. Thank you.