英文:
scoped_session.close() in sqlalchemy
问题
我正在使用SQLAlchemy Python的scoped_session
来处理我的API。在您提供的代码中,存在一个问题,即session.close()
在calculate
函数中关闭了会话,导致generate
函数中的更改未提交到数据库。
解决这个问题的方法是将session.close()
移到generate
函数的finally
块之外,以确保会话在generate
函数完成后关闭。这样,您的更改将在generate
函数中提交到数据库。
以下是修复后的代码:
class DATABASE():
def __init__(self):
engine = create_engine(
'mssql+pyodbc:///?odbc_connect=%s' % (
urllib.parse.quote_plus(
'DRIVER={/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so};SERVER=localhost;'
'DATABASE=db1;UID=sa;PWD=admin;port=1433;'
)), isolation_level='READ COMMITTED', connect_args={'options': '-c lock_timeout=30 -c statement_timeout=30', 'timeout': 40}, max_overflow=10, pool_size=30, pool_timeout=60)
session = sessionmaker(bind=engine)
self.Session = scoped_session(session)
def calculate(self, book_id):
session = self.Session
output = None
try:
result = session.query(Book).get(book_id)
if result:
output = result.pages
except:
session.rollback()
finally:
session.close()
return output
def generate(self):
session = self.Session
try:
result = session.query(Order).filter(Order.product_name=='book').first()
pages = self.calculate(result.product_id)
if not output:
result.product_details = str(pages)
session.commit()
except:
session.rollback()
finally:
session.close()
return output
database = DATABASE()
database.generate()
通过这种方式,您可以确保在generate
函数中对数据库所做的更改会提交,同时也满足了关闭会话的推荐做法。这是SQLAlchemy的一般工作流程。感谢您的提问!
英文:
I am using the scoped_session
for my APIs from sqlalchemy python
class DATABASE():
def __init__(self):
engine = create_engine(
'mssql+pyodbc:///?odbc_connect=%s' % (
urllib.parse.quote_plus(
'DRIVER={/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so};SERVER=localhost;'
'DATABASE=db1;UID=sa;PWD=admin;port=1433;'
)), isolation_level='READ COMMITTED', connect_args={'options': '-c lock_timeout=30 -c statement_timeout=30', 'timeout': 40}, max_overflow=10, pool_size=30, pool_timeout=60)
session = sessionmaker(bind=engine)
self.Session = scoped_session(session)
def calculate(self, book_id):
session = self.Session
output = None
try:
result = session.query(Book).get(book_id)
if result:
output = result.pages
except:
session.rollback()
finally:
session.close()
return output
def generate(self):
session = self.Session
try:
result = session.query(Order).filter(Order.product_name=='book').first()
pages = self.calculate(result.product_id)
if not output:
result.product_details = str(pages)
session.commit()
except:
session.rollback()
finally:
session.close()
return output
database = DATABASE()
database.generate()
Here, the session is not committing, then I go through the code, the generate
function calls the calculate
function, there, after the calculations are completed, the session
is closed - due to this, the changes made in the generate
function is not committed to the database
If I remove the session.close()
from calculate
function, the changes made in generate
function is committed to the database
From the blogs, it is recommend to close the session after API complete its accessing to the database
How to resolve this, and what is the flow of sqlalchemy?
Thanks
答案1
得分: 1
Scoped sessions default to being thread-local, so as long as you are in the same thread, the factory (self.Session
in this case) will always return the same session. So calculate
and generate
are both using the same session, and closing it in calculate
will roll back the changes made in generate
.
Moreover, scoped sessions should not be closed, they should be removed from the session registry (by calling self.Session.remove()
); they will be closed automatically in this case.
You should work out where in your code you will have finished with your session, and remove it there and nowhere else. It will probably be best to commit or rollback in the same place. In the code in the question I'd remove rollback
and close
from calculate
.
The docs on When do I construct a Session, when do I commit it, and when do I close it? and Contextual/Thread-local Sessions should be helpful.
英文:
Scoped sessions default to being thread-local, so as long as you are in the same thread, the factory (self.Session
in this case) will always return the same session. So calculate
and generate
are both using the same session, and closing it in calculate
will roll back the changes made in generate
.
Moreover, scoped sessions should not be closed, they should be removed from the session registry (by calling self.Session.remove()
); they will be closed automatically in this case.
You should work out where in your code you will have finished with your session, and remove it there and nowhere else. It will probably be best to commit or rollback in the same place. In the code in the question I'd remove rollback
and close
from calculate
.
The docs on When do I construct a Session, when do I commit it, and when do I close it? and Contextual/Thread-local Sessions should be helpful.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论