LAST_INSERT_ID() off by one

I have a web application written in Python using SQLAlchemy for data access. I'm trying to get the last insert ID of an insert. This code was working when I was using PyODBC, but now with SQLAlchemy it doesn't seem to work. LAST_INSERT_ID() seems to consistently return an ID that is off 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 off by one (returning 8 instead of 9 for example)?

Answers


Still not certain why the query was returning an inaccurate ID. However, I seem to have worked around the problem by getting a connection object rather than using implicit/connectionless execution. Perhaps it was grabbing two different connections 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']

Take a look what SQLAlchemy documentation has to say about Engine.execute.

Every time you call this function you are implicitly getting new connection. My guess is that your INSERT is not commited yet when you are selecting last_id in a different session.


Cursor.lastrowid

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a 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()

Need Your Help

Rails Sass media query variables cause Heroku push to fail

ruby-on-rails heroku sass

I am using Sass directive/variable to define screen-size on my media query. However, my app is failing to compile while pushing to Heroku.