Geek Answers Handbook

LAST_INSERT_ID () is disabled by one

I have a web application written in Python using SQLAlchemy to access data. I am trying to get the last insert insert id. This code worked when I used PyODBC, but now it does not work with SQLAlchemy. LAST_INSERT_ID() seems to consistently return an identifier that is disabled by one.

  query = text("""INSERT INTO HR_PunchBatch (StoreID, UserID, Source,Timestamp,Status) VALUES (:StoreID,:UserID,:Source,NOW(),:Status)""") g.engine.execute(query, StoreID=StoreID, UserID=session['UserID'], Source=source, Status='New') batch_id = g.engine.execute('SELECT LAST_INSERT_ID() AS id').fetchone() return batch_id['id']

Any ideas as to why this would be disabled by one (e.g. instead of 8 instead of 9)?

+4
source share
3 answers

It is still not clear why the request returned an inaccurate ID. However, I seem to have dealt with the problem by getting the connection object, rather than using implicit / non-contact execution. Perhaps this was a grasp of two different compounds before and, therefore, providing inconsistent results:

  conn = g.engine.connect() query = text("""INSERT INTO HR_PunchBatch (StoreID, UserID, Source,Timestamp,Status) VALUES (:StoreID,:UserID,:Source,NOW(),:Status)""") conn.execute(query, StoreID=StoreID, UserID=session['UserID'], Source=source, Status='New') batch_id = conn.execute('SELECT LAST_INSERT_ID() AS id').fetchone() return batch_id['id']
+3
source

See what SQLAlchemy documentation has to say about Engine.execute .

Each time you call this function, you implicitly get a new connection. I assume your INSERT is not busy yet when you select last_id in another session.

+3
source

Cursor.lastrowid

This read-only attribute provides the rowid of the last modified row (most databases return rowid only when one INSERT operation is completed). If the operation did not set rowid or if the database does not support rowids, this attribute should be set to None.

http://www.python.org/dev/peps/pep-0249/

 import MySQLdb conn = MySQLdb.connect('localhost', 'usr', 'pswrd', 'db'); cur = conn.cursor() cur.execute('INSERT IGNORE INTO table (name) VALUES "text"') conn.commit() print cur.lastrowid # print LAST_INSERT_ID()
+1
source

More articles:


All Articles