When you say your first execute function doesn't work, what do you mean? Do you get an error? Do the results not show up in the database?
I don't think there's any need for session.begin() http://docs.sqlalchemy.org/en/latest/orm/session_api.html#sqlalchemy.orm.session.Session.begin Simon On Thu, Feb 15, 2018 at 12:33 PM, <eugene.de...@gmail.com> wrote: > Hello, Simon! > Where did you read that I was using Flask? > I just write about it like example few posts ago. > Anyway. > I try another variant without decorator - just execute function > def execute(statement, **kwargs): > session = SESSION() > session.begin(subtransactions=True) > kwargs['tries'] = kwargs.get('tries', 0) > fetch = kwargs.get('fetch', 'all') > try: > result = session.execute(statement) > if fetch == 'all': > return result.fetchall() > elif fetch is False: > return True > elif fetch == 'count': > return result > return result.fetchone() > except ResourceClosedError: > session.rollback() > session.close() > time.sleep(.1) > print('try number {}'.format(kwargs['tries'])) > if kwargs['tries'] < 100: > kwargs['tries'] += 1 > return execute(statement, **kwargs) > return list() > except Exception as e: > session.rollback() > session.close() > print(e) > print(statement) > finally: > if session.is_active: > session.commit() > session.close() > but that not help too. Idk why that not work. > > I try another variant: > > def execute(statement, **kwargs): > fetch = kwargs.get('fetch', 'all') > with engine.connect() as session: > result = session.execute(statement) > if fetch == 'all': > return result.fetchall() > elif fetch is False: > return list() > elif fetch == 'count': > return result > return result.fetchone() > > But same error. That variant must always close connection after return, but > problem in something another. > > > > четверг, 15 февраля 2018 г., 13:21:51 UTC+3 пользователь Simon King написал: >> >> Personally I wouldn't use decorators for this. I would make every >> function that needs to interact with the database take an explicit >> session parameter, and I would use the facilities of the web framework >> to create the session at the beginning of the request and close it at >> the end. I've never used Flask, but I see it has a "signals" mechanism >> (http://flask.pocoo.org/docs/0.12/api/#signals) with request_started >> and request_finished events. You could use the request_started signal >> to create the session and attach it to the request object, and clean >> it up in request_finished. >> >> Looking at the Flask docs, I think you could also write a simple >> extension. They have a SQLite example at >> http://flask.pocoo.org/docs/0.12/extensiondev/#the-extension-code, >> which you ought to be able to adapt for SQLAlchemy. The app context is >> per-request, so it should be safe >> (http://flask.pocoo.org/docs/0.12/appcontext/#locality-of-the-context). >> >> Hope that helps, >> >> Simon >> >> On Thu, Feb 15, 2018 at 9:52 AM, <eugene...@gmail.com> wrote: >> > Hello, Simon! >> > So what better way? >> > Something like this? >> > SESSION = sessionmaker(bind=engine, autocommit=True) >> > >> > >> > >> > @decorator_with_args >> > def session_decorator(func, default=None): >> > def wrapper(*a, **kw): >> > session = SESSION() >> > session.begin(subtransactions=True) >> > if 'session' not in kw: >> > kw['session'] = session >> > try: >> > return func(*a, **kw) >> > except Exception as e: >> > session.rollback() >> > logging.error(e) >> > return default >> > finally: >> > if session.is_active: >> > session.commit() >> > session.close() >> > return wrapper >> > >> > I try create additional function >> > >> > @session_decorator() >> > def execute(statement, **kwargs): >> > session = kwargs['session'] >> > fetch = kwargs.get('fetch', 'all') >> > result = session.execute(statement) >> > if fetch == 'all': >> > return result.fetchall() >> > elif fetch is False: >> > return True >> > elif fetch == 'count': >> > return result >> > return result.fetchone() >> > >> > and use it in all execute statements, but that not help. >> > Still receive error >> > This result object does not return rows. It has been closed >> > automatically. >> > But how it closed if that another session? >> > Can you correct my code? >> > >> > >> > >> > >> > среда, 14 февраля 2018 г., 17:45:59 UTC+3 пользователь Simon King >> > написал: >> >> >> >> I think there are a couple of problems with this. >> >> >> >> 1. You are calling scoped_session and sessionmaker every time the >> >> decorated function is called, which is unnecessary. sessionmaker >> >> returns a factory function for creating sessions, so you typically >> >> only have one sessionmaker() call in your application. You could >> >> either make it a module-global, or do it during application >> >> configuration. >> >> >> >> 2. You are never calling session.close(). This means that the >> >> transaction started by a web request will stay open after the request >> >> ends. Since you are using scoped_session as well, the session is >> >> maintained as a thread-local object, so when the thread handles >> >> another web request, it will use the same session and the same >> >> transaction. Your long-running transactions are probably the reason >> >> why you are getting blocked requests. >> >> >> >> You might like to read >> >> >> >> >> >> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-frequently-asked-questions. >> >> >> >> I was going to say that you need to add a "finally" section to your >> >> session_decorator that calls session.close(), but if you ever have one >> >> decorated function calling another decorated function, they are going >> >> to interfere with each other. If you are *absolutely certain* that >> >> will never happen, adding the "finally" section will probably improve >> >> things, but I would recommend that you try to structure your app a >> >> little differently, so that the session creation and teardown happen >> >> at the beginning and end of the web request. >> >> >> >> Hope that helps, >> >> >> >> Simon >> >> >> >> >> >> On Wed, Feb 14, 2018 at 2:14 PM, <eugene...@gmail.com> wrote: >> >> > Decorator like this >> >> > >> >> > engine = create_engine( >> >> > >> >> > >> >> > >> >> > 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}&timeout={TIMEOUT}'.format(**DATABASE), >> >> > isolation_level='READ COMMITTED' >> >> > ) >> >> > >> >> > >> >> > def decorator_with_args(decorator_to_enhance): >> >> > """ >> >> > https://habrahabr.ru/post/141501/ >> >> > """ >> >> > def decorator_maker(*args, **kwargs): >> >> > def decorator_wrapper(func): >> >> > return decorator_to_enhance(func, *args, **kwargs) >> >> > return decorator_wrapper >> >> > return decorator_maker >> >> > >> >> > >> >> > def scope_func(): >> >> > return scoped_session(sessionmaker(bind=engine, >> >> > autocommit=True))() >> >> > >> >> > >> >> > @decorator_with_args >> >> > def session_decorator(func, default=None, **kwargs): >> >> > def wrapper(*a, **kw): >> >> > session = scope_func() >> >> > if 'session' not in kw: >> >> > kw['session'] = session >> >> > try: >> >> > return func(*a, **kw) >> >> > except Exception as e: >> >> > session.rollback() >> >> > >> >> > print('#####FUNCTION NAME: {}'.format(func.__name__)) >> >> > >> >> > print(e) >> >> > logging.error(e) >> >> > return default >> >> > return wrapper >> >> > >> >> > >> >> > >> >> > >> >> > среда, 14 февраля 2018 г., 17:06:54 UTC+3 пользователь Simon King >> >> > написал: >> >> >> >> >> >> The pattern you should be aiming for is one in which a fresh >> >> >> transaction is started for every web request that touches the >> >> >> database, and that the transaction is closed at the end of the >> >> >> request. How are you ensuring that at the moment? >> >> >> >> >> >> Simon >> >> >> >> >> >> On Wed, Feb 14, 2018 at 12:51 PM, <eugene...@gmail.com> wrote: >> >> >> > If I run tests where all functions run one-by-one - all tests >> >> >> > passed. >> >> >> > But when i run web app and functions can call almost in parallel >> >> >> > then >> >> >> > i >> >> >> > have >> >> >> > a problem, then there are problems - transactions can block each >> >> >> > other. >> >> >> > I tried to set the isolation level of SNAPSHOT and READ COMMITTED, >> >> >> > but >> >> >> > it >> >> >> > did not help. >> >> >> > >> >> >> > среда, 14 февраля 2018 г., 14:58:37 UTC+3 пользователь >> >> >> > eugene...@gmail.com >> >> >> > написал: >> >> >> >> >> >> >> >> Hello, Mike! >> >> >> >> In my web app i have many selects like >> >> >> >> session.execute(select([table1]).where(condition)) >> >> >> >> and not so much updates, inserts and deletes like >> >> >> >> session.execute(update(table1).where(condition).values(**values)) >> >> >> >> session.execute(insert(table1).values(**values)) >> >> >> >> session.execute(delete(table1).where(condition)) >> >> >> >> >> >> >> >> What better way to create session for web application? >> >> >> >> Without additional components like flask-sqlalchemy. >> >> >> >> >> >> >> >> суббота, 27 января 2018 г., 20:23:05 UTC+3 пользователь Mike >> >> >> >> Bayer >> >> >> >> написал: >> >> >> >>> >> >> >> >>> On Sat, Jan 27, 2018 at 5:49 AM, Евгений Рымарев >> >> >> >>> <rymarev...@gmail.com> wrote: >> >> >> >>> > I receive this error: >> >> >> >>> > This result object does not return rows. It has been closed >> >> >> >>> > automatically. >> >> >> >>> >> >> >> >>> there's a lot of weird situations which can cause that error, >> >> >> >>> usually >> >> >> >>> when using a connection that has had some failure condition >> >> >> >>> occur >> >> >> >>> upon >> >> >> >>> it which renders the connection unusable until either a >> >> >> >>> transaction >> >> >> >>> is >> >> >> >>> rolled back or sometimes the connection needs to be discarded. >> >> >> >>> We >> >> >> >>> can't diagnose it without a full example that reproduces it as >> >> >> >>> well >> >> >> >>> as >> >> >> >>> the complete stack trace. >> >> >> >>> >> >> >> >>> >> >> >> >>> > >> >> >> >>> > >> >> >> >>> > >> >> >> >>> > суббота, 27 января 2018 г., 1:09:53 UTC+3 пользователь Mike >> >> >> >>> > Bayer >> >> >> >>> > написал: >> >> >> >>> >> >> >> >> >>> >> On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев >> >> >> >>> >> <rymarev...@gmail.com> wrote: >> >> >> >>> >> > Hello, everyone! >> >> >> >>> >> > Engine: >> >> >> >>> >> > engine = create_engine( >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > 'mssql+pymssql://{LOGIN}:{PASSWORD}@{HOST}/{DATABASE}?charset={CHARSET}'.format(**DATABASE), >> >> >> >>> >> > isolation_level='READ COMMITTED' >> >> >> >>> >> > ) >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > My first decorator for session: >> >> >> >>> >> > @decorator_with_args >> >> >> >>> >> > def session_decorator(func, default=None): >> >> >> >>> >> > def wrapper(*a, **kw): >> >> >> >>> >> > s = Session(engine) >> >> >> >>> >> > kw['session'] = s >> >> >> >>> >> > try: >> >> >> >>> >> > return func(*a, **kw) >> >> >> >>> >> > except Exception as e: >> >> >> >>> >> > func(*a, **kw) >> >> >> >>> >> > s.rollback() >> >> >> >>> >> > s.close() >> >> >> >>> >> > logging.error(e) >> >> >> >>> >> > return default >> >> >> >>> >> > finally: >> >> >> >>> >> > s.commit() >> >> >> >>> >> > s.close() >> >> >> >>> >> > return wrapper >> >> >> >>> >> > >> >> >> >>> >> > My second decorator for session: >> >> >> >>> >> > session = scoped_session(sessionmaker(bind=engine)) >> >> >> >>> >> > >> >> >> >>> >> > >> >> >> >>> >> > @decorator_with_args >> >> >> >>> >> > def session_decorator(func, default=None): >> >> >> >>> >> > def wrapper(*a, **kw): >> >> >> >>> >> > kw['session'] = session >> >> >> >>> >> > try: >> >> >> >>> >> > return func(*a, **kw) >> >> >> >>> >> > except Exception as e: >> >> >> >>> >> > session.remove() >> >> >> >>> >> > logging.error(e) >> >> >> >>> >> > return default >> >> >> >>> >> > finally: >> >> >> >>> >> > session.remove() >> >> >> >>> >> > return wrapper >> >> >> >>> >> > >> >> >> >>> >> > In both attempts, I came to the conclusion that sessions >> >> >> >>> >> > can >> >> >> >>> >> > block >> >> >> >>> >> > other >> >> >> >>> >> > sessions. >> >> >> >>> >> > How correctly to create a session once, so that it can be >> >> >> >>> >> > used >> >> >> >>> >> > throughout >> >> >> >>> >> > the web application and no locks were created? >> >> >> >>> >> >> >> >> >>> >> The Session doesn't create locks, transactions and your >> >> >> >>> >> database >> >> >> >>> >> does. >> >> >> >>> >> >> >> >> >>> >> What is the nature of these "blocks", are they deadlocks >> >> >> >>> >> between >> >> >> >>> >> tables or rows? What operations are prevented from >> >> >> >>> >> proceeding? >> >> >> >>> >> Are >> >> >> >>> >> the locks timing out? SQL Server has graphical consoles >> >> >> >>> >> that >> >> >> >>> >> can >> >> >> >>> >> show you this. >> >> >> >>> >> >> >> >> >>> >> What version of SQL Server is this? Do you have >> >> >> >>> >> ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT turned >> >> >> >>> >> on? >> >> >> >>> >> >> >> >> >>> >> >> >> >> >>> >> > >> >> >> >>> >> > -- >> >> >> >>> >> > 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. >> >> >> >>> > >> >> >> >>> > -- >> >> >> >>> > 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. >> >> >> > >> >> >> > -- >> >> >> > 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. >> >> > >> >> > -- >> >> > 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. >> > >> > -- >> > 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. > > -- > 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.
