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 <https://groups.google.com/forum/#!searchin/gevent/SQLAlchemy/gevent/eGLfR7JV0kk/hg1kicBJeQkJ]> 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.
