Confused accessing db sessions

[Kate Boelhauf]

A little background - I'm updating my entire site and used a cookiecutter as referenced in Pyramids docs to do so. I am using sqlalchemy.

It has a db session as a request method. This works perfectly for me in all of my views. However, I have class methods in my models that need to access a session.  I can't figure out a way to access the session that the cookiecutter set up in models.py so I reverted to creating a separate session using  scoped_session(sessionmaker(extension=ZopeTransactionExtension())) and importing it into my model and using the request session in my views.

This just seems wrong - am I approaching this wrong?

Below is the model.py file that the cookiecutter created:

from sqlalchemy import engine_from_config
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import configure_mappers
import zope.sqlalchemy

# import or define all models here to ensure they are attached to the
# Base.metadata prior to any initialization routines
from .mymodel import MyModel  # flake8: noqa

# run configure_mappers after defining all of the models to ensure
# all relationships can be setup
configure_mappers()


def get_engine(settings, prefix='sqlalchemy.'):
    return engine_from_config(settings, prefix)


def get_session_factory(engine):
    factory = sessionmaker()
    factory.configure(bind=engine)
    return factory


def get_tm_session(session_factory, transaction_manager):
    """
    Get a ``sqlalchemy.orm.Session`` instance backed by a transaction.

    This function will hook the session to the transaction manager which
    will take care of committing any changes.

    - When using pyramid_tm it will automatically be committed or aborted
      depending on whether an exception is raised.

    - When using scripts you should wrap the session in a manager yourself.
      For example::

          import transaction

          engine = get_engine(settings)
          session_factory = get_session_factory(engine)
          with transaction.manager:
              dbsession = get_tm_session(session_factory, transaction.manager)

    """
    dbsession = session_factory()
    zope.sqlalchemy.register(
        dbsession, transaction_manager=transaction_manager)
    return dbsession


def includeme(config):
    """
    Initialize the model for a Pyramid app.

    Activate this setup using ``config.include('tutorial.models')``.

    """
    settings = config.get_settings()
    settings['tm.manager_hook'] = 'pyramid_tm.explicit_manager'

    # use pyramid_tm to hook the transaction lifecycle to the request
    config.include('pyramid_tm')

    # use pyramid_retry to retry a request when transient exceptions occur
    config.include('pyramid_retry')

    session_factory = get_session_factory(get_engine(settings))
    config.registry['dbsession_factory'] = session_factory

    # make request.dbsession available for use in Pyramid
    config.add_request_method(
        # r.tm is the transaction manager used by pyramid_tm
        lambda r: get_tm_session(session_factory, r.tm),
        'dbsession',
        reify=True
    )

[Michael Merickel]

This is a super common complaint from people and it doesn't have a single definitive solution. There are a couple handy tricks:

1. If your classmethod receives any managed objects, you can get a reference to the session from the object itself. For example ``sqlalchemy.orm.object_session(user)``.

2. The easy answer when the above doesn't work is to pass the dbsession into the class method.

After this - if still not satisfied - are some hacky things you can do:

1. Stop using classmethod approaches and instead put in a real service layer. This has tons of benefits, see something like pyramid_services for more information about this. This approach will allow you to bind the whole service to a dbsession, and no longer need to pass it around at all.

2. Define a helper to grab the session from the request threadlocal. Something like ``get_current_dbsession = lambda: get_current_request().dbsession``. Then import that and use it via ``dbsession = get_current_dbsession()``.

The threadlocal approach above has some downsides but is still better than using a scoped session. Allowing the request to seep into and infect your data model is a little wonky and makes your model code less reusable outside of Pyramid. Even if you are okay with that, something like ``get_current_dbsession().query()`` is going to be better than ``scoped_session.query()`` because it's more explicitly a value that changes over time versus the magic scoped session object proxy.

In my own apps I use all the approaches mentioned above except the threadlocal one.

- Michael

--
You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pylons-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/c60ba3e3-d3ea-4f59-9c94-460c7964c865%40googlegroups.com.

--

Michael

[Kate Boelhauf]

This is insanely helpful - thank you Michael.  I'm going to pass the dbsession into the class method for now but look into a service layer.  

To unsubscribe from this group and stop receiving emails from it, send an email to pylons-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/c60ba3e3-d3ea-4f59-9c94-460c7964c865%40googlegroups.com.

--

Michael

[Mike Orr]

I've gone completely away from scoped sessions. In the main function I
create two engines for two databases as registry attributes
('request.registry.sa_engine_main'). My request subclass has two
reified properties ('request.registry.sa_session_main') that
initialize a session and hook it into Pyramid's transaction manager
('pyramid_tm'). Views use this session. Business-logic functions take
a session or connection as an argument, so they work the same when
called from views, console scripts, and unit tests. I put those in
separate modules, although in the past I've put them in the model
modules or as model class methods. For console scripts I have a
function 'get_sqlalchemy_session(settings, "main")' that creates an
engine from the app settings, binds it to a session, and returns the
session'. The caller is responsible for committing/rolling back (or
letting it implicitly roll back if the script aborts).

[Jonathan Vanasco]

On Monday, January 6, 2020 at 4:03:19 PM UTC-5, Kate Boelhauf wrote:

This is insanely helpful - thank you Michael.  I'm going to pass the dbsession into the class method for now but look into a service layer.  

A few years ago I adopted the pattern of passing Pyramid's `request` object into all class methods - and pretty much everything else - as the first argument representing the "context".  From that, I can grab my active core database session via `request.dbsession`.  If I need to override with an explicit dbSession, I pass in a dbsession kwarg or will inspect the object using `sqlalchemy.orm.object_session(user)` as Michael suggested.

At one point I passed in the dbSession, but I found myself with a chunks of code that expected a dbSession and chunks that expected a request.  Standardizing everything to a request saved a lot of headaches down the line for me.