Sqlaclhemy sessions, expunge doesn't seem to work

Question

Simple setup,

I have a simple Task object that looks like this:

# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer, String, Text, DateTime, Float, func, ARRAY,ForeignKey
from sqlalchemy.orm import relationship
from . import Base
import settings
import config.customer
import datetime

class Task(Base):
    __tablename__ = "task"

    id = Column(Integer, primary_key=True)
    value = Column(Integer)

    def __init__(self, value):
        self.value = value

    @classmethod
    def get_fake_task(cls):
        return Task(10)

Then, I am trying to add an instance to the database and use it out of a session, for some reasons, it doesn't work.

# -*- coding: utf-8 -*-
import config
import config.customer
import config.task
import datetime

#config.Base.metadata.create_all(config.engine)


FAKE_TASK = config.task.Task.get_fake_task()


session1 = config.create_session()
session1.add(FAKE_TASK)
session1.commit()
session1.close()

session2 = config.create_session()
session2.add(FAKE_TASK)
session2.refresh(FAKE_TASK)
FAKE_TASK.value = 999999
print(FAKE_TASK.id)
session2.merge(FAKE_TASK)
session2.commit()
session2.expunge_all()
session2.close()



print(FAKE_TASK.value)

It is a pretty simple setup, and in my other projet, I didn't have such an issue. Maybe I am doing wrong somewhere else?

With the script above, I get the following error when trying to print the value at the very end :

sqlalchemy.orm.exc.DetachedInstanceError: Instance is not bound to a Session; attribute refresh operation cannot proceed

Here is the declaration of the Base and engine in the __init__.py file within the config module :

from sqlalchemy.engine.url import URL
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import settings
from contextlib import contextmanager

Base = declarative_base()
engine = create_engine(URL(**settings.DATABASE), pool_size=0, max_overflow=-1)
Session = sessionmaker(bind=engine)

def create_session():
    session = Session()
    return session

Thank you for your help

Answer 1

expire_on_commit=False

is needed on the sessionmaker to avoid expiring object once you commit. An expired object will need to be refreshed from the database before accessing a property thus it needs to be in a session scope. Without expiring it, you can access properties out of the session scope.

Session = sessionmaker(bind=engine,expire_on_commit=False)