On Jan 26, 9:49 am, "Michael Bayer" <[email protected]> wrote:
> programmer.py wrote:
> > Hi. I've been working on some unittests for a pylons application.
> > Part of that testing involves adding data to the database, submitting
> > a request through the paster testing framework and examining the
> > response.
>
> > I use two separate sessions for unittests. One is generated by the
> > pylons application itself and the other is created for the unittest.
> > Whenever I add data in the unittest session, it is not seen by the
> > pylons application. This simple script below demonstrates whats
> > happening during my tests. (Or at least what I think is happening).
>
> > Is there any way around this? The only alternative I see is using the
> > same session as the pylons application. But I've run into other
> > issues when I try to do that.
>
> > The output from the script below is ->
>
> > 0
> > 10
> > 0
>
> when I run it the output is:
>
> zzzeek-3:sqlalchemy classic$ python test.py
> 0
> 10
> 10
>
> This is because by default the engine is MyISAM and there's no transaction
> isolation between the two connections.
>
> However, if I change the table to use InnoDB, then the transaction
> isolation kicks in and we get the expected:
>
> zzzeek-3:sqlalchemy classic$ python test.py
> 0
> 10
> 0
>
> the second connection gets zero because the transaction is referencing
> what's already been selected (i.e. no phantom reads). Rolling back s2
> before reselecting again gives us:
>
> zzzeek-3:sqlalchemy classic$ python test.py
> 0
> 10
> 10
>
>
So, the s2 query is `cached`? I'm still confused about this.
Whenever I issue a rollback() on s2, like you described, it works.
I'm just surprised, because I expected the query to always fetch fresh
results. Should I not be surprised?
Thanks for your help!
jw
>
> > # Begin python script
> > import sys
> > from sqlalchemy.ext.declarative import declarative_base
> > from sqlalchemy import Column, Integer, String, create_engine
> > from sqlalchemy.orm import sessionmaker
>
> > Base = declarative_base()
>
> > class SimpleTest(Base):
> > __tablename__ = 'simpletest'
>
> > id = Column(Integer, primary_key=True)
> > s1 = Column(String(255))
>
> > def main():
> > engine1 = create_engine('mysql://test:t...@localhost/test')
> > engine2 = create_engine('mysql://test:t...@localhost/test')
> > SimpleTest.metadata.create_all(engine1)
>
> > # Clear out the test table.
> > con = engine1.connect()
> > con.execute('TRUNCATE simpletest')
>
> > # Create two distinct sessions.
> > s1 = sessionmaker(bind=engine1)()
> > s2 = sessionmaker(bind=engine2)()
>
> > # Show empty table count on session 2.
> > print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
>
> > # Load the database with 10 rows.
> > for x in range(10):
> > st = SimpleTest()
> > st.s1 = str(x)
> > s1.add(st)
> > s1.commit()
>
> > print s1.execute('SELECT COUNT(*) FROM simpletest').scalar()
> > print s2.execute('SELECT COUNT(*) FROM simpletest').scalar()
>
> > if __name__ == '__main__':
> > sys.exit(main())
>
> > Thanks,
> > jw
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group at
> >http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
