how to make session.expunge_all() work to detach all instances from session in SQLAlchemy?

Question

I have created a SQLAlchemy session as:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
session.autoflush = False

I am using the same session object for multiple POST requests. For the first time when I add an object to this session object, and do session.commit(), the object gets inserted to MySQL with INSERT query. But next time when I use the same session as session.add() again, the old object gets updated with UPDATE query. I am commit() ing my first INSERT and detaching instances as session.expunge_all() but still it does the same.

    def new(self, **kwargs):
      self.firstname = kwargs['firstname']
      self.lastname = kwargs['lastname']
      session.add(self)
      session.commit()
      session.expunge_all()

Does it mean that I will have to create a new session object every time I use it to update the database? Or there is some logical error in the code for detaching instances from session?

tuomur tuomur · Accepted Answer · 2014-01-07T10:12:06

Yes, you should create a new session instance at the beginning of new. You don't need to call expunge_all after that either. Session objects don't cost anything so you can use them as you like.

However, this code is still weird. Are you sure my example below isn't what you're after?

Your mapped class definition:

class MyObject(Base):
    def __init__(self, *args, **kwargs):
        self.firstname = kwargs['firstname']
        self.lastname = kwargs['lastname']

And usage:

session = Session()
my_ob = MyObject(firstname='John', lastname='Smith')
session.add(my_ob)
session.commit()
session.close()

how to make session.expunge_all() work to detach all instances from session in SQLAlchemy?

1 Answers