CODEWITHSUNDEEP
pythonmysql-python
Paolo
Paolo

Reputation: 15847

Python, MySQLdb, unable to update row

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

Upvotes: 1

Views: 314

Answers (1)

Thomas Orozco
Thomas Orozco

Reputation: 55303

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.

Upvotes: 2

Related Questions