How to catch when SQL UPDATE SETs not existing line

Question

I'm developing with Flask and MySQL. Problem is: I have test table with this values id | resource_name | +----+---------------+ | 1 | google | | 2 | google | | 3 | google | | 4 | google |

In Python I have this:

def edit_resource(res_name, res_id):
    with db_pool().manager() as conn:
        cursor = conn.cursor()
        sql = """UPDATE `resource` SET `resource_name` = %s WHERE id = %s;"""
        cursor.execute(sql, (res_name, res_id)) 
        conn.commit()
    return True

When this query executed and I put id that not exists answer from DB is "query ok". Please help me to catch problem!

What is the goal of the catching of the *does not exist* issue?, because SQL definition wise the return of the update is OK. (So another question approach is needed to get you to a solution) — Norbert, Nov 07 '15 at 19:39

score 1 · Answer 1 · edited May 23 '17 at 12:23

1

Append this to your SQL query:

SELECT ROW_COUNT();

Then treat the entire query like a SELECT, where the single returned column is the number of "rows affected". In this case, the number of rows updated.

Details here: How to get number of rows affected, while executing MySQL query from bash?

edited May 23 '17 at 12:23

Community

1
1

answered Nov 07 '15 at 19:41

modal_dialog

733
5
12

ROW_COUNT can be 0 if the value is equal to the already existing value (so if $id==%s) no real update is performed with as result ROW_COUNT=0 – Norbert Nov 07 '15 at 19:47
@Norbert Really?! That seems bizarre and unhelpful. I would expect ROW_COUNT to correspond only to the results filtered by my WHERE clause and for it not to care one bit what value I'm updating to. The semantics of that sounds really bad, are you sure this is the case? – modal_dialog Nov 07 '15 at 19:50
1

100% Certain (tested it to be certain). It is a performance optimization (Saves on expensive write IO). There is however a CLIENT_FOUND_ROWS (see http://stackoverflow.com/questions/19062530/change-client-found-rows-flag-in-django-for-mysql-python-mysqldb ), which could do the trick. – Norbert Nov 07 '15 at 19:53
1

@Norbert, got interested and started checking.. You're right! ...but there's a flag to override it (has to be set when you open the connection: http://dev.mysql.com/doc/refman/5.5/en//information-functions.html#function_row-count. Reading the full convoluted behavior makes me sad. :) – modal_dialog Nov 07 '15 at 19:56
The override flag: Interesting. I personally would go for a different data design (if possible): `INSERT INTO blabla ON DUPLICATE KEY UPDATE blabla`. No checks needed. – Norbert Nov 07 '15 at 19:58
@Norbert - I agree. In answering these types of questions, I usually assume the guy wants to change as little as possible and get working, so that's how I got down that rabbit hole. :) – modal_dialog Nov 07 '15 at 20:01

score 1 · Accepted Answer · answered Nov 07 '15 at 19:44

Modify your code like this:

def edit_resource(res_name, res_id):
    res = False
    with db_pool().manager() as conn:
        cursor = conn.cursor()
        sql = """UPDATE `resource` SET `resource_name` = %s WHERE id = %s;"""
        if cursor.execute(sql, (res_name, res_id)) > 0:
          res = True
        conn.commit()
    return res

the idea is to check the return value of cursor.execute(), which returns the number of affected rows.

How to catch when SQL UPDATE SETs not existing line

2 Answers2