CODEWITHSUNDEEP
pythonmysqlsqlalchemydeclarative
PlaidFan
PlaidFan

Reputation: 797

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.

Upvotes: 6

Views: 6945

Answers (3)

tuanh118
tuanh118

Reputation: 361

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

Upvotes: 2

Neel
Neel

Reputation: 21243

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

Upvotes: 10

Pedro Nascimento
Pedro Nascimento

Reputation: 13886

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

Upvotes: 3

Related Questions