Python mysql update with no subquery results in subquery error

Question

I have the following code

conn = MySQLdb.connect(server, user, password, database, autocommit=True, charset='utf8')
    for e in list_to_updpate:
        nid = e[0]
        vid = e[1]
        text = e[2]
        delta = e[3]
        deleted = e[4]
        langcode = e[5]
        to_update = e[6]
        if (to_update == 1):
            with conn.cursor() as cursor:
                cursor.execute("""update node__body set body_value=%s 
                                     where entity_id=%s AND revision_id=%s AND
                                     delta=%s AND deleted=%s AND langcode=%s;""" , 
                               (MySQLdb.escape_string(text) , int(nid), 
                                int(vid), int(delta), int(deleted), langcode, ))
            conn.commit()
    conn.close()

Where I have the list of entries I want to update and text is a LONG TEXT which can contain anything from the UTF8 table.

When I run the code I notice 2 things:

1. It runs for a while but nothing gets updated.

2. At some point it crashes with the error:

   _mysql_exceptions.OperationalError: (1242, 'Subquery returns more than 1 row')
   

This I can not get a grip of it and more I am sure that only one row gets updated since as per table definition the primary key is defined as (entity_id, revision_id, delta, deleted, langcode)

Note: This behavior is repeated with pymysql and with MySQL-client on python3.6

Regards, T

Answer 1

Found the answer:

It turns out that in order to match the LONGTEXT of the mysql db one should use io.String() object to pass the text. Otherwise it does not behave as expected. Mostly you can expect that a simple string would be mapped to varchar which should be at a max length. Be careful, since a varchar can is a subset of LONG TEXT you can expect that no error would be raised if the query commits and the input would be cropped if it gets committed to the DB. Why in this particular case it never committed(see discussion in the question) is still unclear to me. Now the working code:

conn = MySQLdb.connect(server, user, password, database, autocommit=True, charset='utf8')
for e in list_to_update:
    cursor = conn.cursor()
    nid = e[0]
    vid = e[1]
    text = e[2]
    delta = e[3]
    deleted = e[4]
    langcode = e[5]
    to_update = e[6]
    if to_update == 1:
        cursor.execute(
            "update node__body set body_value=%s where entity_id=%s AND revision_id=%s AND delta=%s AND deleted=%s AND langcode=%s",
            (io.StringIO(text), nid, vid, delta, deleted, langcode))
        conn.commit()
    cursor.close()
conn.close()

Note this is Python3. For Python2 use StringIO.String()

Hope that helps,
Todor

Answer 2

There are a few things I would like to point out before I post the code.

First thing, escape_string is not needed as the execute will do it for you, as long as you include the parameters in a tuple as the second element.

I suspect your problem is around the cursor. Seems that is being opened, used and closed before commiting anything.

If there aren't any elements that go against having the cursor opened for as long as needed to update all the elements in the list, I suggest this approach.

conn = MySQLdb.connect(server, user, password, database, autocommit=True, charset='utf8')
cursor = conn.cursor()
for e in list_to_update:
    nid = e[0]
    vid = e[1]
    text = e[2]
    delta = e[3]
    deleted = e[4]
    langcode = e[5]
    to_update = e[6]

    if to_update == 1:
        cursor.execute("update node__body set body_value=%s where entity_id=%s AND revision_id=%s AND delta=%s AND deleted=%s AND langcode=%s" , 
                               (text , nid, vid, delta, deleted, langcode))
        conn.commit()
    cursor.close()
    conn.close()

If you need to make only one commit, right at the end, you only need to edit the commit line removing one indent. Also, note that i've added cursor.close, as I'm not using the with approach.