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 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.
