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!

[Mike Bayer]

On Fri, Jul 14, 2017 at 8:08 PM, Tony Wang <plantp...@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+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[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 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!

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+...@googlegroups.com.

To post to this group, send email to sqlal...@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

[Mike Bayer]

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.

[Tony Wang]

Hi Mike

Great thanks, error seems gone.