How to get the last row id on update?

Question

I am a beginner in mysql and may be its my fault somewhere, and not able to understand how this can be resolved. This is structure of my table:-

CREATE TABLE `nearest_product_type` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `created` datetime NOT NULL,
    `modified` datetime NOT NULL,
    `name` varchar(15) NOT NULL UNIQUE
)
;

And this is the code i am trying:-

base = MySQLdb.connect (host="localhost", user = "root", passwd = "sheeshmohsin", db="points")
basecursor = base.cursor()
queryone = """INSERT INTO nearest_product_type (name,created,modified) VALUES (%s,%s,%s) ON DUPLICATE KEY UPDATE name=name """
category = "Indica"
valueone = (category,datetime.datetime.now(),datetime.datetime.now())
basecursor.execute(queryone, valueone)
product_id = basecursor.lastrowid
basecursor.close()
base.commit()
base.close()
print product_id

On running this python script, first time when category is not unique, it works fine, but on running again with the same category as first time, last row id returns 0. but i need the id of the last row which is updated.

And when i checked the rows in table, the auto-increment is also working, suppose if i run the script four times, in first time when category is unique the id is 1 and suppose another unique category comes in fourth time, then the id assigned to this row is 4, but it should be 2, because its second row. how can i solve this?

Read [this](http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python) question, I guess it might help. — rodrigogq, Nov 06 '14 at 15:14

score 2 · Accepted Answer · answered Nov 06 '14 at 15:26

The ON DUPLICATE KEY UPDATE part here will not work as the key is the auto-increment column, which will never get duplicates.

It is almost certainly this clause that is causing the unexpected counts, particularly given the UNIQUE setting on name.

You can try using something like SELECT MAX(id) FROM nearest_product_type to get the last id added.

score 0 · Answer 2 · answered Nov 06 '14 at 16:46

Something is wrong in the way you access the database. When you try to insert an new row in your database with a name that already exists, as the column name is declared to be unique, the insert will fail.

If you want to modify an existing row , you must use an UPDATE statement not an INSERT one. And there's nothing in SQL to do an insert or update.

And nothing in autoincrement guarantees that id are consecutive. All you know is that the database will allow a different id for each inserted row, but insertion failure can (and do in you case) result is holes is the id sequence.

Furthermore, some drivers may allow for pre-reservation of ids, particurarly with network connections to allow a client connection to get a bunch of ids in case it would insert more than one row. It that case, if another client asks for ids, and both clients insert rows alternatively, the id will not follow the insertion time.

How to get the last row id on update?

2 Answers2