Get last inserted value from MySQL using SQLAlchemy

Question

I just ran into a rather unpleasant problem, and after testing, I found that NONE of the available answers is enough.

I saw various suggestions, but no one seems to be able to return the last inserted value for the auto_increment field in MySQL.

I saw examples that mention using session.flush () to add an entry and then get the id. However, this always returns 0.

I also saw examples that mention using session.refresh (), but this causes the following error: InvalidRequestError: Failed to update instance ''

What I'm trying to do seems insanely simple, but I can't understand the secret.

I use a 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 moment, the object f was transferred to the database and the unique identifier of the primary key was automatically assigned. However, I cannot find a way to get the value to use in some additional operations. I would like to do the following:

my_new_id = f.ModelID

I know that I can just do another query to search for ModelID based on other parameters, but I would prefer it to be possible.

I would really appreciate an understanding of the solution to this problem.

Thanks for the help in advance.

Answer 1

The problem is that you set defaul to auto-increment. Therefore, when it starts pasting into the request, the server log

 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 set the default value for the autoincrement column.

If I run the same code without default , then it gives 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() . Then you can use f.ModelID .

Answer 3

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

So, the last few lines of code:

 f = Foo(ModelName='Bar', ModelMemo='Foo') session.add(f) session.flush() print "ID:", f.ModelID session.commit()