pythonmysqlsqlalchemydeclarative

Get last inserted value from MySQL using SQLAlchemy


I want 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 '<MyModel....>'

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.


Solution

  • 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