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