Get last inserted value from MySQL using SQLAlchemy

Question

I've just run across a fairly vexing problem, and after testing I have found that NONE of the available answers are sufficient.

I have seen various suggestions but none seem to be able to return the last inserted value for an auto_increment field in MySQL.

I have seen examples that mention the use of session.flush() to add the record and then retrieve the id. However that always seems to return 0.

I have also seen examples that mention the use of session.refresh() but that raises the following error: InvalidRequestError: Could not refresh instance ''

What I'm trying to do seems insanely simple but I can't seem to figure out the secret.

I'm using the declarative approach.

So, my code looks something like this:

class Foo(Base):
    __tablename__ = 'tblfoo'
    __table_args__ = {'mysql_engine':'InnoDB'}

    ModelID = Column(INTEGER(unsigned=True), default=0, primary_key=True, autoincrement=True)
    ModelName = Column(Unicode(255), nullable=True, index=True)
    ModelMemo = Column(Unicode(255), nullable=True)

f = Foo(ModelName='Bar', ModelMemo='Foo')
session.add(f)
session.flush()

At this point, the object f has been pushed to the DB, and has been automatically assigned a unique primary key id. However, I can't seem to find a way to obtain the value to use in some additional operations. I would like to do the following:

my_new_id = f.ModelID

I know I could simply execute another query to lookup the ModelID based on other parameters but I would prefer not to if at all possible.

I would much appreciate any insight into a solution to this problem.

Thanks for the help in advance.

score 9 · Accepted Answer · answered Dec 21 '11 at 08:22

The problem is you are setting defaul for the auto increment. So when it run the insert into query the log of server is

2011-12-21 13:44:26,561 INFO sqlalchemy.engine.base.Engine.0x...1150 INSERT INTO tblfoo (`ModelID`, `ModelName`, `ModelMemo`) VALUES (%s, %s, %s)
2011-12-21 13:44:26,561 INFO sqlalchemy.engine.base.Engine.0x...1150 (0, 'Bar', 'Foo')
ID : 0

So the output is 0 which is the default value and which is passed because you are setting default value for autoincrement column.

If I run same code without default then it give the correct output.

Please try this code

from sqlalchemy import create_engine
engine = create_engine('mysql://test:test@localhost/test1', echo=True)

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)

session = Session()

from sqlalchemy import Column, Integer, Unicode

class Foo(Base):
    __tablename__ = 'tblfoo'
    __table_args__ = {'mysql_engine':'InnoDB'}

    ModelID = Column(Integer, primary_key=True, autoincrement=True)
    ModelName = Column(Unicode(255), nullable=True, index=True)
    ModelMemo = Column(Unicode(255), nullable=True)

Base.metadata.create_all(engine)

f = Foo(ModelName='Bar', ModelMemo='Foo')
session.add(f)
session.flush()

print "ID :", f.ModelID

Ugh...yes, nice catch. My head was in the SQL world where a default value is required for a primary key field when you create the table. Thanks for the find. — PlaidFan, Dec 22 '11 at 14:16

Pedro Nascimento · Answer 2 · 2011-12-21T05:27:16.543

2

Try using session.commit() instead of session.flush(). You can then use f.ModelID.

edited Dec 21 '11 at 05:27

answered Dec 21 '11 at 05:20

Pedro Nascimento

13,136
4
36
64

I tried it after both the flush and commit but received the following error: AttributeError: 'Foo' object has no attribute 'id' – PlaidFan Dec 21 '11 at 05:26
Sorry, updated the answer to use ModelID like you used into your schema declaration. Still, you should probably use `id` instead of `ModelID`. – Pedro Nascimento Dec 21 '11 at 05:27
Sadly, I've tried that approach many ways and continue to get the following error: ObjectDeletedError: Instance '' has been deleted. – PlaidFan Dec 21 '11 at 05:31
`flush()` is suppose to clear the transaction, that's why I'm suggesting a `commit()`. If that doesn't work, I'm lost then. – Pedro Nascimento Dec 21 '11 at 05:37
If I use commit() then I get the following error: ObjectDeletedError: Instance '' has been deleted. If I only use flush() then the value is 0, the default value not the actual inserted value for the record. – PlaidFan Dec 21 '11 at 05:57
In an attempt to align my code with your suggestion I changed the field name of the ID field to id. If I run this: con.add(f) con.flush() print f.id I now receive a different error: ConcurrentModificationError: Updated rowcount 0 does not match number of objects updated 1 I am really confused now... – PlaidFan Dec 21 '11 at 06:03
In my case i was closing the session before get the inserted object ID, just swaped lines. it worked – rodrigorf May 04 '19 at 02:33

score 1 · Answer 3 · answered Dec 12 '16 at 21:44

Not sure why the flagged answer worked for you. But in my case, that does not actually insert the row into the table. I need to call commit() in the end.

So the last few lines of code are:

f = Foo(ModelName='Bar', ModelMemo='Foo')
session.add(f)
session.flush()

print "ID:", f.ModelID

session.commit()

Get last inserted value from MySQL using SQLAlchemy

3 Answers3