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.

Answer 1

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

Answer 2

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