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.de...@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+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.
