Struggling with 'synchronisation' between Session's in SQLAlchemy
Question: I've a created `delete_entity` function which delete's entities and I've a
function which tests this functions.

    
    
    #__init__.py
    engine = create_engine('sqlite://:memory')
    Session = scoped_session(sessionmaker(engine))
    
    # entity.py
    def delete_entity(id, commit=False):
      """ Delete entity and return amount of amount of rows affected. """
      rows_affected = Session.query(Entity).filter(Entity.id == id).delete()
      if commit:
        Session.commit()
        # Marker @A
    
      return rows_affected
    
    # test_entity.py
    def test_delete_entity(Session):
       # ... Here I add 2 Entity objects. Database now contains 2 rows.
    
       assert delete_entity(1) == 1                 # Not committed, row stays in database.
       assert delete_entity(1, commmit=True)        # Row should be deleted 
       # marker @B 
       assert len(Session.query(Entity).all()) == 1
    

This test passes when I run the `test_delete_entity()` **alone**. But when I
run this test together with other tests this test fails. It fails on `assert
len(Session.query(Entity)).all()) == 1`. The query finds 2 rows, so it looks
like the row hasn't been deleted. But, when I use the Python debugger
(`pytest.set_trace()`) on @A and query for all Entity objects in the database
I find 1 row. So the delete query was succesfull and one row has been deleted.
But when I query for all Entity rows on @B I get 2 rows.

How can I 'synchronize' both Sessions, so my test will pass?


Answer: I just tried but couldn't reproduce your issue. Here's my full script with the
results:

    
    
    import sqlalchemy as sa
    
    from sqlalchemy import create_engine, Column, Integer, Unicode
    from sqlalchemy.orm import sessionmaker, scoped_session
    from sqlalchemy.ext.declarative import declarative_base
    
    
    engine = create_engine('sqlite://', echo=True)
    
    Base = declarative_base(bind=engine)
    Session = scoped_session(sessionmaker(bind=engine))
    
    
    class Entity(Base):
        __tablename__ = 'test_table'
        id = Column(Integer(), primary_key=True)
        name = Column(Unicode(200))
    
    
    Base.metadata.create_all()
    
    e1 = Entity()
    e1.name = 'first row'
    e2 = Entity()
    e2.name = 'second row'
    
    Session.add_all([e1, e2])
    Session.commit()
    
    print len(Session.query(Entity).all())
    # CORRECTLY prints 2
    
    rows_affected = Session.query(Entity).filter(Entity.id == 1).delete()
    print rows_affected
    # CORRECTLY prints 1
    Session.commit()
    
    print len(Session.query(Entity).all())
    # CORRECTLY prints 1
    

Since your script is not runnable, we can't find your issue.

Please provide a runnable script that shows the entire issue, like I did. The
script must do all imports, insert the data, delete, and query in the end.
Otherwise there's no way I can reproduce it here in my environment.

