scoped_session.close() in 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

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.