Introduction

In SQLAlchemy, after creating a session, it is necessary to perform commit (), rollback (), and close () as needed. Here, we will create a class that performs DB operations and summarize how to pass sessions. In the following, it is assumed that the following model class exists.

`models.py`


class User(Base):
    __tablename__="user" #Specify table name
    id=Column(Integer, primary_key=True)
    first_name=Column(String(255))
    last_name=Column(String(255))

Bad treatment of session

It is not good to make it like the following source code.

`wrong_way.py`


from models import User

class FirstName(object):
    def update_first_name(self, user_id, first_name):
        session=Session()
        try:
            user=session.query(User).filter(User.id==user_id).one() #id=user_Extract obj which is id
            user.first_name=first_name #first_Change name
            session.commit()
        except:
            session.rollback()
            raise


class LastName(object):
    def update_last_name(self, user_id, last_name):
        session=Session()
        try:
            user=session.query(User).filter(User.id==user_id).one() #id=user_Extract obj which is id
            user.first_name=first_name #last_Change name
            session.commit()
        except:
            session.rollback()
            raise


def run_my_program():
    FirstName().update_first_name(1, "update_first_name")
    LastName().update_last_name(1, "update_last_name")

Because the same session is not used in ʻupdate_first_name and ʻupdate_last_name in run_my_program. "First_name was updated, but last_name was not." This is because something like this happens.

Good way to handle session 1

Therefore, it can be solved by using the following source code.

`right_way_1.py`


from models import User

class FirstName(object):
    def update_first_name(self, user_id, first_name, session):
        user=session.query(User).filter(User.id==user_id).one()
        user.first_name=first_name


class LastName(object):
    def update_last_name(self, user_id, last_name, session):
        user=session.query(User).filter(User.id==user_id).one()
        user.first_name=first_name


def run_my_program():
    session = Session()
    try:
        FirstName().update_first_name(session)
        LastName().update_last_name(session)
        session.commit()
    except:
        session.rollback()
        raise
    finally:
        session.close()

By doing this, the same session will be used for ʻupdate_first_name and ʻupdate_last_name. "Only one ran successfully" The situation like this does not occur.

Good way to handle session 2

Next, we will introduce how to handle session using context manager. As for the context manager, the following articles are very easy to understand and helpful, so the explanation is omitted here. What is a Python context manager? The story I searched for

`right_way_2.py`


from models import User

from contextlib import contextmanager

@contextmanager
def session_scope():
    session = Session()  # def __enter__
    try:
        yield session  #Pass session with as
        session.commit()  #If nothing happens, commit()
    except:
        session.rollback()  #Rollback if error occurs()
        raise
    finally:
        session.close()  #Either way, it will eventually close()


class FirstName(object):
    def update_first_name(self, user_id, first_name, session):
        user=session.query(User).filter(User.id==user_id).one()
        user.first_name=first_name


class LastName(object):
    def update_last_name(self, user_id, last_name, session):
        user=session.query(User).filter(User.id==user_id).one()
        user.first_name=first_name


def run_my_program():
    with session_scope() as session:
        FirstName().update_first_name(session)
        LastName().update_last_name(session)

References

This article was written with reference to the following information. -Official Documents (Session Basics) · [What is a Python context manager? The story I investigated (https://blog.mtb-production.info/entry/2018/04/10/183000)

[PYTHON] How to handle session in SQLAlchemy

Introduction

models.py