1

I have several worker threads that need to pull rows from a database and work on them. Trying to make a single call that shows the row has/is being checked. I had hoped cursor.lastrowid would work but it always returns zero. The docs say "This read-only property returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement". The table has two columns, id (unique, auto increment) checked (int).

cursor.execute('UPDATE the_table SET checked = 1 WHERE checked = 0 LIMIT 1')
mysql.commit()
id = cursor.lastrowid
print(id) #returns 0 not last id

This does update the row but the cursor.lastrowid is always 0. cursor.rowcount returns 1. Checking the database shows a single row affected. Are there any known issues or perhaps I have something out of order?

EDIT: This may not be possible. I have not seen any working example where an UPDATE can return the auto_increment value. Perhaps the docs need to be updated?

Ryan Mills
  • 939
  • 1
  • 10
  • 23
  • The docs you are quoting from appear to be those of the official [MySQL Connector/Python](https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-lastrowid.html), but the tag you chose indicates the use of the [pymysql](https://pymysql.readthedocs.io/en/latest/user/index.html) library. Which one is it? – shmee Jan 29 '19 at 07:55
  • Possible duplicate of [UPDATE on mysql python always returns 0 as lastrowid](https://stackoverflow.com/questions/36542315/update-on-mysql-python-always-returns-0-as-lastrowid) – shmee Jan 29 '19 at 08:17
  • As @Mike outlined in the answer to the [duplicate question](https://stackoverflow.com/questions/36542315/update-on-mysql-python-always-returns-0-as-lastrowid): `lastrowid` (resp. [`mysql_insert_id`](https://dev.mysql.com/doc/refman/8.0/en/mysql-insert-id.html)) only returns non-zero values after an `UPDATE` statement if the [`LAST_INSERT_ID(expr)`](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id) function occurred in the statement, in which case it will return _expr_. – shmee Jan 29 '19 at 08:22
  • I did see mikes reply. It does not match the documentation if that is the case and does not correctly solve the issue. It seems it should only be used for INSERT. Do you have an example statement showing how UPDATE would effect an auto_increment? – Ryan Mills Jan 29 '19 at 19:36

0 Answers0