your reader loop needs to end the transaction its within in order to read
new data from other transactions. you can play with MySQL's transaction
isolation levels to modify this behavior but a high level of isolation is
the default.
Bill wrote:
>
> I'm having trouble getting updates from InnoDB table. I have a test
> case with two programs. One simply creates a session and does a
> periodic query. The other writes two values, one into a MyISAM table
> and one into an InnoDB table. The next query from the first program
> doesn't get the updated contents inserted from the second program.
> Here is the test case code:
>
> ----------------------------------
> First the reader program:
>
> #!/usr/bin/python2.5
> import time
> import sqlalchemy
> print sqlalchemy.__version__ # 0.4.8
>
> from sqlalchemy import create_engine
> engine = create_engine('mysql://localhost/test_db', echo=True)
>
> from sqlalchemy import Table, Column, String, MetaData
> metadata = MetaData()
> myisam_table = Table('myisam_table', metadata,
> Column('my_string', String(40), primary_key=True),
> mysql_engine='MyISAM'
> )
> innodb_table = Table('innodb_table', metadata,
> Column('my_string', String(40), primary_key=True),
> mysql_engine='InnoDB'
> )
> metadata.create_all(engine)
>
> class MyisamTableRow(object):
> def __init__(self, my_string):
> self.my_string = my_string
>
> def __repr__(self):
> return "<MyisamTableRow('%s')>" % self.my_string
>
> class InnodbTableRow(object):
> def __init__(self, my_string):
> self.my_string = my_string
>
> def __repr__(self):
> return "<InnodbTableRow('%s')>" % self.my_string
>
> from sqlalchemy.orm import mapper
> mapper(MyisamTableRow, myisam_table)
> mapper(InnodbTableRow, innodb_table)
>
> from sqlalchemy.orm import sessionmaker
> Session = sessionmaker()
> session = Session(bind=engine, autoflush=False, transactional=True)
>
> while (True):
> time.sleep(10)
> print session.query(MyisamTableRow).all()
> print session.query(InnodbTableRow).all()
>
> ----------------------------------
> The writer is essentially the same, with the while loop replaced with:
>
> m = MyisamTableRow('a test string')
> session.save(m)
> session.commit()
>
> i = InnodbTableRow('a test string')
> session.save(i)
> session.commit()
>
> ------------------------------------
>
> While the reader is running, I execute the writer:
>
> 2009-06-01 06:24:51,295 INFO sqlalchemy.engine.base.Engine.0x..50
> BEGIN
> 2009-06-01 06:24:51,296 INFO sqlalchemy.engine.base.Engine.0x..50
> INSERT INTO myisam_table (my_string) VALUES (%s)
> 2009-06-01 06:24:51,297 INFO sqlalchemy.engine.base.Engine.0x..50 ['a
> test string']
> 2009-06-01 06:24:51,298 INFO sqlalchemy.engine.base.Engine.0x..50
> COMMIT
> 2009-06-01 06:24:51,300 INFO sqlalchemy.engine.base.Engine.0x..50
> BEGIN
> 2009-06-01 06:24:51,301 INFO sqlalchemy.engine.base.Engine.0x..50
> INSERT INTO innodb_table (my_string) VALUES (%s)
> 2009-06-01 06:24:51,301 INFO sqlalchemy.engine.base.Engine.0x..50 ['a
> test string']
> 2009-06-01 06:24:51,302 INFO sqlalchemy.engine.base.Engine.0x..50
> COMMIT
>
> ------------------------------------
>
> The next iteration of the reader querying, shows the MyISAM table
> contents, but the InnoDB contents haven't updated:
>
> 2009-06-01 06:24:56,515 INFO sqlalchemy.engine.base.Engine.0x..50
> SELECT myisam_table.my_string AS myisam_table_my_string
> FROM myisam_table ORDER BY myisam_table.my_string
> 2009-06-01 06:24:56,516 INFO sqlalchemy.engine.base.Engine.0x..50 []
> [<MyisamTableRow('a test string')>]
> 2009-06-01 06:24:56,523 INFO sqlalchemy.engine.base.Engine.0x..50
> SELECT innodb_table.my_string AS innodb_table_my_string
> FROM innodb_table ORDER BY innodb_table.my_string
> 2009-06-01 06:24:56,523 INFO sqlalchemy.engine.base.Engine.0x..50 []
> []
>
> I am not sure what difference I have that is making the InnoDB table
> not update properly between processes. Any help/suggestions would be
> appreciated. I have not had luck finding previous issues like this on
> Google.
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
