Re: [sqlalchemy] How right use session/scoped_session in web app?
OK, so what's not working? On Fri, Feb 16, 2018 at 10:55 AM, wrote: > Yes session.is_active is True. > I really sure, because i log this operation. > > пятница, 16 февраля 2018 г., 13:50:51 UTC+3 пользователь Simon King написал: >> >> You haven't explained in what way it's not working with your latest >> iteration. >> >> The last code you posted only called session.close() if >> session.is_active was true. Are you sure you really are closing the >> session? >> >> Simon >> >> On Fri, Feb 16, 2018 at 10:02 AM, wrote: >> > This option does not suit me. >> > My app based on CherryPy 3.2.2. >> > I add more logging, and all session closing by >> > session.close() >> > in finally section. >> > I can't understand why this not work... >> > >> > четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан >> > написал: >> >> >> >> You need just that: >> >> from proj.core import Session >> >> >> >> @app.teardown_request >> >> def clear_session(): >> >> Session.remove() >> >> >> >> Session created with scoper_session, of course. We do not use >> >> flask-sqlalchemy package, just flask and separated sqlalchemy. >> >> >> >> >> >> чт, 15 февр. 2018 г., 16:28 Simon King : >> >>> >> >>> 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, 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,
Re: [sqlalchemy] How right use session/scoped_session in web app?
Yes session.is_active is True. I really sure, because i log this operation. пятница, 16 февраля 2018 г., 13:50:51 UTC+3 пользователь Simon King написал: > > You haven't explained in what way it's not working with your latest > iteration. > > The last code you posted only called session.close() if > session.is_active was true. Are you sure you really are closing the > session? > > Simon > > On Fri, Feb 16, 2018 at 10:02 AM, > > wrote: > > This option does not suit me. > > My app based on CherryPy 3.2.2. > > I add more logging, and all session closing by > > session.close() > > in finally section. > > I can't understand why this not work... > > > > четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан > > написал: > >> > >> You need just that: > >> from proj.core import Session > >> > >> @app.teardown_request > >> def clear_session(): > >> Session.remove() > >> > >> Session created with scoper_session, of course. We do not use > >> flask-sqlalchemy package, just flask and separated sqlalchemy. > >> > >> > >> чт, 15 февр. 2018 г., 16:28 Simon King : > >>> > >>> 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, 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). > >>> >> > >>> >>
Re: [sqlalchemy] How right use session/scoped_session in web app?
You haven't explained in what way it's not working with your latest iteration. The last code you posted only called session.close() if session.is_active was true. Are you sure you really are closing the session? Simon On Fri, Feb 16, 2018 at 10:02 AM, wrote: > This option does not suit me. > My app based on CherryPy 3.2.2. > I add more logging, and all session closing by > session.close() > in finally section. > I can't understand why this not work... > > четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан > написал: >> >> You need just that: >> from proj.core import Session >> >> @app.teardown_request >> def clear_session(): >> Session.remove() >> >> Session created with scoper_session, of course. We do not use >> flask-sqlalchemy package, just flask and separated sqlalchemy. >> >> >> чт, 15 февр. 2018 г., 16:28 Simon King : >>> >>> 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, 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, 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 >>
Re: [sqlalchemy] How right use session/scoped_session in web app?
Maybe you try to change same rows within several concurrent requests? I do not use mssql, but in postgres it is normal. Anyway it is normal behavior. One transaction tries to change a row, another one tries to change the same. When the first transaction commited, the second can continue its job. пт, 16 февр. 2018 г. в 13:02, : > This option does not suit me. > My app based on CherryPy 3.2.2. > I add more logging, and all session closing by > session.close() > in finally section. > I can't understand why this not work... > > четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан > написал: >> >> You need just that: >> from proj.core import Session >> >> @app.teardown_request >> def clear_session(): >> Session.remove() >> >> Session created with scoper_session, of course. We do not use >> flask-sqlalchemy package, just flask and separated sqlalchemy. >> >> чт, 15 февр. 2018 г., 16:28 Simon King : >> >>> 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, 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, 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' n
Re: [sqlalchemy] How right use session/scoped_session in web app?
This option does not suit me. My app based on CherryPy 3.2.2. I add more logging, and all session closing by session.close() in finally section. I can't understand why this not work... четверг, 15 февраля 2018 г., 18:07:49 UTC+3 пользователь Антонио Антуан написал: > > You need just that: > from proj.core import Session > > @app.teardown_request > def clear_session(): > Session.remove() > > Session created with scoper_session, of course. We do not use > flask-sqlalchemy package, just flask and separated sqlalchemy. > > чт, 15 февр. 2018 г., 16:28 Simon King >: > >> 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, > >> 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, 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 tr
Re: [sqlalchemy] How right use session/scoped_session in web app?
You need just that: from proj.core import Session @app.teardown_request def clear_session(): Session.remove() Session created with scoper_session, of course. We do not use flask-sqlalchemy package, just flask and separated sqlalchemy. чт, 15 февр. 2018 г., 16:28 Simon King : > 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, 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, 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 resul
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, 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, 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 >> >> decora
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, > > 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. Si
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, 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, 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 >> >
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, > > 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, 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 COMMITTE
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, 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, 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, Евгений Рымарев >> >>> 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, Евгений Рымарев >> >>> >> wrote: >> >>> >> > Hello, everyone! >> >>> >> >
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, > > 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, Евгений Рымарев > >>> 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, Евгений Рымарев > >>> >> 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
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, 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, Евгений Рымарев >>> 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, Евгений Рымарев >>> >> 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://group
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, Евгений Рымарев >> 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, Евгений Рымарев >> >> 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.
Re: [sqlalchemy] How right use session/scoped_session in web app?
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, Евгений Рымарев > > 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, Евгений Рымарев > >> 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
Re: [sqlalchemy] How right use session/scoped_session in web app?
On Sat, Jan 27, 2018 at 5:49 AM, Евгений Рымарев 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, Евгений Рымарев >> 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+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.
Re: [sqlalchemy] How right use session/scoped_session in web app?
I receive this error: This result object does not return rows. It has been closed automatically. суббота, 27 января 2018 г., 1:09:53 UTC+3 пользователь Mike Bayer написал: > > On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев > > 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+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.
Re: [sqlalchemy] How right use session/scoped_session in web app?
I use mssql 2017 with compatibility level = 100 (mssql 2008). I enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT, but that not solve a problem. When i run application via python manage.py (one thread) all works normal. суббота, 27 января 2018 г., 1:09:53 UTC+3 пользователь Mike Bayer написал: > > On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев > > 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+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.
Re: [sqlalchemy] How right use session/scoped_session in web app?
On Fri, Jan 26, 2018 at 4:21 PM, Евгений Рымарев 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+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.
[sqlalchemy] How right use session/scoped_session in web app?
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? -- 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.