Re: [sqlalchemy] The right way to use gevent with sqlalchemy

[Tony Wang]

Hi Mike

Great thanks, error seems gone.

On Sun, Jul 16, 2017 at 11:37 PM Mike Bayer 
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 
> 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 
> > wrote:
> >>
> >> On Fri, Jul 14, 2017 at 8:08 PM, Tony Wang 
> >> 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 = cre

Re: [sqlalchemy] The right way to use gevent with sqlalchemy

[Tony Wang]

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 
wrote:

> On Fri, Jul 14, 2017 at 8:08 PM, Tony Wang 
> 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 "sqlalc

[sqlalchemy] The right way to use gevent with sqlalchemy

[Tony Wang]

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


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.