LAST_INSERT_ID() off by one

Question

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)?

Answer 1

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']