Search code examples
mysqlsqlalchemypyramid

Getting the ID from an inserted record in SQLAlchemy

models.py 

class User(Base):
  __tablename__ = 'users'
  id = Column(Integer, primary_key = true, autoincrement=true)
  ...

views.py

entry = User()
with transaction.manager:
  DBSession.add(entry)
  DBSession.flush()
transaction.commit()
pdb.set_trace()

Now when I run 

print user.id

I get

DetachedInstanceError: Instance <Userat 0x3ebc310> is not bound to a Session

if I change if from add to merge it just gives me 

none
Solution

  • If you remove manual fiddling with transaction everything will work:

    entry = User()
DBSession.add(entry)
DBSession.flush()
print entry.id

    Since you're using Pyramid with ZopeTransactionExtension it is better to avoid dealing with transactions manually and leave it to Pyramid - the transaction will be committed on success and rolled back if your code raises an exception.