`scoped_session.close()` 在 SQLAlchemy 中的含义是关闭一个 scoped session。

huangapple go评论60阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年2月8日 18:13:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/75384254.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定