Retaining plain old python instance member values betwen Flask-SQLAlchemy requests.

[Rob Rosenfeld]

Hi All,

I am using Flask-SQLAlchemy on a legacy database.  My SQLA classes / tables are setup using declarative base and autoload.  In addition to using the class to access persisted data, I also use plain old Python to enrich them with ephemeral data. 

Every time I load a User object, I'd like the instance for that particular object / row to retain its plain old python instance data.

My understanding is that Flask-SQLAlchemy will by default use a separate scoped_session for each request.  And that means at the end of the request session and user will go out of scope.  So in the next request to /users/Bob will instantiate a new User object whose will be reinitialized . . . each time I make this request I will be told it was "asked 1 time".

Alternately, if I call /users_alt/Bob, then second time I call a DetachedInstanceError will be raised when I attempt to read user.owners.

I suspect there's a pattern used to accomplish what I want to do, but I have not tried the right Google search.  Any recommendations on how to approach this or searches to try / doc sections to read?   The state I'm storing in times_name_read_since_launch is actually complex enough that I don't want to persist it and have to keep that dependent state synchronized.

Alternately is there a way for me to reattach a SQLA object to the current requests' session?  Then, ideally, I could get updates from the database to User or Owner  objects, but retain my ephemeral state in the plain old python. 

Thanks for the help,

Rob

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import MetaData
from sqlalchemy import orm

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = connection_string
db = SQLAlchemy(app)


class Owner(db.Model):
    __table__ = db.Table('Owners', MetaData(), autoload=True, autoload_with=db.engine)


class User(db.Model):
    __table__ = db.Table('users', MetaData(), autoload=True, autoload_with=db.engine)
    owners = db.relationship('Owner', primaryjoin="foreign(User.userId)==remote(Owner.userId)", uselist=True,
                             backref=db.backref('user', uselist=False))

    users_seen = dict()

    @orm.reconstructor
    def init_on_load(self):
        self.times_name_read_since_launch = 0

    @classmethod
    def lookup_by_name(cls, name):
        user_ = User.users_seen.get(name)
        if user_ is None:
            user_ = User.query.filter(User.UserName == name).one()
        return user_

    @classmethod
    def store_by_name(cls, user_):
        if user_.UserName not in User.users_seen:
            User.users_seen[user_.UserName] = user_

    @property
    def name(self):
        self.times_name_read_since_launch += 1
        return self.UserName


@app.route("/users/<string:name>")
def user(name):
    user_ = User.query.filter(User.UserName == name).one()
    return "{} with {} - asked {} times".format(user_.name, user_.owners, user_.times_name_read_since_launch)


@app.route("/users_alt/<string:name>")
def user_alt(name):
    user_ = User.lookup_by_name(name)
    User.store_by_name(user_)
    owners = None
    if user_.times_name_read_since_launch > 0:
        # don't lazy load addresses the first request, simulates more complex actual behavior desired
        owners = user_.owners
    return "{} with {} - asked {} times".format(user_.name, owners, user_.times_name_read_since_launch)


db.create_all()

[Jonathan Vanasco]

Session.merge allows you to attach a detached object

https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merging

But

What you are describing to me looks like an anti-pattern, as it will have wildly unpredictable results if you deploy your application with more than one worker (e.g. a multi-threaded or multi-process container) - which is the standard.

I think you may want to use a caching layer.

[Mike Bayer]

On Tue, Feb 11, 2020, at 11:41 PM, Rob Rosenfeld wrote:

Hi All,

I am using Flask-SQLAlchemy on a legacy database.  My SQLA classes / tables are setup using declarative base and autoload.  In addition to using the class to access persisted data, I also use plain old Python to enrich them with ephemeral data. 

Every time I load a User object, I'd like the instance for that particular object / row to retain its plain old python instance data.

My understanding is that Flask-SQLAlchemy will by default use a separate scoped_session for each request.  And that means at the end of the request session and user will go out of scope.  So in the next request to /users/Bob will instantiate a new User object whose will be reinitialized . . . each time I make this request I will be told it was "asked 1 time".

Alternately, if I call /users_alt/Bob, then second time I call a DetachedInstanceError will be raised when I attempt to read user.owners.

I suspect there's a pattern used to accomplish what I want to do, but I have not tried the right Google search.  Any recommendations on how to approach this or searches to try / doc sections to read?   The state I'm storing in times_name_read_since_launch is actually complex enough that I don't want to persist it and have to keep that dependent state synchronized.

Alternately is there a way for me to reattach a SQLA object to the current requests' session?  Then, ideally, I could get updates from the database to User or Owner  objects, but retain my ephemeral state in the plain old python. 

you can get a plain reattach without it emitting any SQL using session.add():

session.add(detached_object)

however, if you're not sure if detached_object's primary key might have been loaded already, you would want to use session.merge() instead.  if your object has no changes on it you can add load=False which will avoid a SQL round trip.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/66717a53-d81b-4cce-be68-601c5d673284%40googlegroups.com.

[Rob Rosenfeld]

Thanks for the thoughts.  I'm going to digest, read docs, and experiment.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/NS4PbZ2nLwU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/31e0db24-1a1a-4099-8510-d9568fb97ca0%40www.fastmail.com.

[Rob Rosenfeld]

For now I have an unoptimized solution working.   I don't ever keep a reference to the SQLA instance and in the reconstructor I reattach the ephemeral state.  Something like.

class User(db.Model):
  __data_cache = dict()

  @orm.reconstructor
  def init_on_load(self):
    data = User.__data_cache.get(self.id)
    if data is None:
      User.__data_cache[self.id] = 0
    User.__data_cache[self.id] += 1

Rob

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

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/66717a53-d81b-4cce-be68-601c5d673284%40googlegroups.com.

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/NS4PbZ2nLwU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlal...@googlegroups.com.