Hi Mike Great thanks, error seems gone.
On Sun, Jul 16, 2017 at 11:37 PM Mike Bayer <mike...@zzzcomputing.com> wrote: > it means like this (here, I adapt your SqlHelper into a recipe that is > basically equivalent to the context manager at > > http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it > ) > > > engine = create_engine(...) > sessionmaker = sessionmaker(engine) > > class SqlHelper(object): > def __init__(self): > self.session = sessionmaker() > > def __enter__(self): > return self > > def __exit__(self ,type, value, traceback): > try: > if type: > self.session.rollback() > else: > self.session.commit() > finally: > self.session.close() > > def insert(self, object): > self.session.add(object) > > def delete(self, object): > self.session.delete(object) > > # ... > > > def run_in_gevent(): > with SqlHelper() as helper: > for item in things_to_do(): > helper.insert(...) > helper.delete(...) > # ... > > if __name__ = '__main__': > for i in range(num_workers): > spawn(run_in_gevent) > > # .. etc > > Following the guidelines at > > http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it > : > > "keep the lifecycle of the session separate and external from > functions and objects that access and/or manipulate database data. " > - we don't commit() the session in the same place that we are doing > individual insert(), delete(), select() statements - we should have a > single transaction surrounding a group of operations. > > "Make sure you have a clear notion of where transactions begin and > end" - the SqlHelper() is used as a context manager, and that's when > the transaction starts. outside the "with:" block, the transaction is > done. > > On Sat, Jul 15, 2017 at 9:16 PM, Tony Wang <plantpark....@gmail.com> > wrote: > > Hi Mike > > > > Thanks very much for your detailed reply. Very kind of you. > > > > For "threadlocal" strategy, I used without it for a long time but always > > coming with "Runtime Error" that posted in BitBucket. I found some guys > in > > Gevent Group solved it by "threadlocal". I tried but error kept there. > > > > Little confusing about the second suggestion. You mean put "sqlhelper = > > SqlHelper()" outside of insert_data function, and let gevent workers > share > > the same engine and session? It doesn't work. If not, > > could you share me some demo code? > > > > Thanks! > > > > On Sat, Jul 15, 2017 at 11:51 PM Mike Bayer <mike...@zzzcomputing.com> > > wrote: > >> > >> On Fri, Jul 14, 2017 at 8:08 PM, Tony Wang <plantpark....@gmail.com> > >> wrote: > >> > I simplify a complex system of gevent with sqlachemy to a simple demo > >> > code. > >> > > >> > > >> > Before using of sqlachemy, pymysql is my best choice for communication > >> > between MySql and gevent because of its easy and direct usage. But > now I > >> > feel a little confusing, what's the best way to make the two (gevent > and > >> > sqlachemy) work efficiently. > >> > > >> > > >> > In pymysql case, though some "_io.BufferedReader " errors would > happen, > >> > it > >> > doesn't affect the INSERT or UPDATE operations. > >> > > >> > > >> > In sqlalchemy case, it's quite different in the opposite. Much more > >> > errors > >> > and little success. > >> > > >> > > >> > After searching around for such errors, some > >> > > >> > solutions[ > https://groups.google.com/forum/#!searchin/gevent/SQLAlchemy/gevent/eGLfR7JV0kk/hg1kicBJeQkJ > ] > >> > for similar error didn't work. > >> > > >> > > >> > Demo code : > >> > https://gist.github.com/tonywangcn/6dadbd58d7778063b0c7969f62505537 > >> > >> so first, do not use the "threadlocal" strategy. It is legacy and > >> should never be used: > >> > >> > http://docs.sqlalchemy.org/en/latest/core/connections.html#using-the-threadlocal-execution-strategy > >> . With gevent, I'd expect it to be disastrous: > >> > >> self.engine = create_engine( > >> 'mysql+pymysql://root:password@localhost/test?charset=utf8', > >> echo=True, pool_size=50, max_overflow=100, pool_recycle=3600, > >> strategy='threadlocal') > >> > >> so remove that. > >> > >> Next, you are manipulating the private state of the QueuePool after > >> its been constructed: > >> > >> self.engine.pool._use_threadlocal = True > >> > >> Never alter the value of an underscore variable in any library, the > >> underscore means "private". The effect of setting this flag after > >> the fact is that the QueuePool will be in an essentially broken state > >> since the constructor needs to know about this flag; in this specific > >> case, the "threadlocal" engine strategy means the flag was already set > >> in any case so it isn't having any effect, but this code should be > >> removed. > >> > >> Next, you are creating many Engine objects, one for each operation via > >> insert_data() -> SqlHelper() -> create_engine(). This is an > >> antipattern as there should be exactly one Engine within the Python > >> process for a given URL, and should be called at the module level, > >> outside of the instantiation of objects (unless the object itself is > >> created once-per-process). same goes for the sessionmaker(). > >> > >> > >> > >> > >> > >> > > >> > > >> > More details for error > >> > > >> > > https://stackoverflow.com/questions/45113145/the-right-way-to-use-gevent-with-sqlalchemy > >> > > >> > > >> > Thanks! > >> > > >> > -- > >> > SQLAlchemy - > >> > The Python SQL Toolkit and Object Relational Mapper > >> > > >> > http://www.sqlalchemy.org/ > >> > > >> > To post example code, please provide an MCVE: Minimal, Complete, and > >> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > >> > description. > >> > --- > >> > You received this message because you are subscribed to the Google > >> > Groups > >> > "sqlalchemy" group. > >> > To unsubscribe from this group and stop receiving emails from it, send > >> > an > >> > email to sqlalchemy+unsubscr...@googlegroups.com. > >> > To post to this group, send email to sqlalchemy@googlegroups.com. > >> > Visit this group at https://groups.google.com/group/sqlalchemy. > >> > For more options, visit https://groups.google.com/d/optout. > >> > >> -- > >> SQLAlchemy - > >> The Python SQL Toolkit and Object Relational Mapper > >> > >> http://www.sqlalchemy.org/ > >> > >> To post example code, please provide an MCVE: Minimal, Complete, and > >> Verifiable Example. See http://stackoverflow.com/help/mcve for a full > >> description. > >> --- > >> You received this message because you are subscribed to a topic in the > >> Google Groups "sqlalchemy" group. > >> To unsubscribe from this topic, visit > >> https://groups.google.com/d/topic/sqlalchemy/wiAnfZQRHdw/unsubscribe. > >> To unsubscribe from this group and all its topics, send an email to > >> sqlalchemy+unsubscr...@googlegroups.com. > >> To post to this group, send email to sqlalchemy@googlegroups.com. > >> Visit this group at https://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > -- > > > > Tony Wang > > > > Warm Regards > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > You received this message because you are subscribed to the Google Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to sqlalchemy+unsubscr...@googlegroups.com. > > To post to this group, send email to sqlalchemy@googlegroups.com. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/wiAnfZQRHdw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- Tony Wang Warm Regards -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
