CODEWITHSUNDEEP
pythonmysqlsqlalchemy
Kyle Johnson
Kyle Johnson

Reputation: 1675

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

Upvotes: 5

Views: 6620

Answers (3)

Jorik
Jorik

Reputation: 31

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

Upvotes: 1

zifot
zifot

Reputation: 2688

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.

Upvotes: 4

Kyle Johnson
Kyle Johnson

Reputation: 1675

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

Upvotes: 5

Related Questions