Mixins and lazy (query) attributes

[Christian Barra]

Hi, I am trying to understand what the best approach is to have lazy attributes defined on a mixin and then used them on the subclasses.

Ideally I'd would like to defer the load of that attribute (controller) and use `undefer` when I know that that attribute is needed, to execute only one query.

Below there's the code I am trying to use, `recipe_set` is a backref, same for `unit`, Controller is another model.

class BaseRecipeMixin(APIResourceMixin):
    _key = None

    @declared_attr
    def recipe_set_id(self):
        return db.Column(db.ForeignKey('recipe_set.id'), unique=True, nullable=False)

    @property
    def controller(self):
        return column_property(
            Controller.query.filter(
                Controller.unit == self.recipe_set.unit,
                Controller.is_master.is_(True),
                Controller.is_deleted.is_(False),
            ).one_or_none(),
            deferred=True,
        )


class EcRecipe(DosingRecipeMixin, BaseRecipeMixin, UpdateMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    pump_time = db.Column(db.Float, nullable=False, default=8)

But when I try to run this

models.EcRecipe.query.first().controller

I get this error

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
  File "/Users/cbarra/Projects/kompost/recipes/models.py", line 262, in controller
    deferred=True,
  File "<string>", line 2, in column_property
  File "<string>", line 2, in __init__
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/util/deprecations.py", line 130, in warned
    return fn(*args, **kwargs)
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py", line 134, in __init__
    self._orig_columns = [expression._labeled(c) for c in columns]
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/properties.py", line 134, in <listcomp>
    self._orig_columns = [expression._labeled(c) for c in columns]
  File "<string>", line 1, in <lambda>
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 4373, in _labeled
    return element.label(None)
AttributeError: 'NoneType' object has no attribute 'label'

while when I try to use `undefer` with

models.EcRecipe.query.options(orm.undefer('controller'))

the error is different

Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.7/lib/python3.7/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1523, in options
    return self._options(False, *args)
  File "<string>", line 2, in _options
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/base.py", line 220, in generate
    fn(self, *args[1:], **kw)
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 1542, in _options
    opt.process_query(self)
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 168, in process_query
    self._process(query, True)
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 542, in _process
    raiseerr,
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 720, in _bind_loader
    raiseerr,
  File "/Users/cbarra/Projects/.venv/lib/python3.7/site-packages/sqlalchemy/orm/strategy_options.py", line 235, in _generate_path
    attr = found_property = attr.property
AttributeError: 'property' object has no attribute 'property'

I am bit puzzled between property, hybrid_property and column_property, what is the best way to have lazy attributes defined on a mixin?

[Mike Bayer]

On Wed, Jun 5, 2019, at 12:02 PM, Christian Barra wrote:

Hi, I am trying to understand what the best approach is to have lazy attributes defined on a mixin and then used them on the subclasses.

Ideally I'd would like to defer the load of that attribute (controller) and use `undefer` when I know that that attribute is needed, to execute only one query.

Below there's the code I am trying to use, `recipe_set` is a backref, same for `unit`, Controller is another model.

class BaseRecipeMixin(APIResourceMixin):
    _key = None

    @declared_attr
    def recipe_set_id(self):
        return db.Column(db.ForeignKey('recipe_set.id'), unique=True, nullable=False)

    @property
    def controller(self):
        return column_property(
            Controller.query.filter(
                Controller.unit == self.recipe_set.unit,
                Controller.is_master.is_(True),
                Controller.is_deleted.is_(False),
            ).one_or_none(),
            deferred=True,
        )


class EcRecipe(DosingRecipeMixin, BaseRecipeMixin, UpdateMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    pump_time = db.Column(db.Float, nullable=False, default=8)

But when I try to run this

when you declare mapped attributes on a mixin you have to use @declared_attr:

https://docs.sqlalchemy.org/en/13/orm/extensions/declarative/mixins.html#mixing-in-deferred-column-property-and-other-mapperproperty-classes

The column_property() object needs to be made part of the mapping and declarative uses this decorator to know it has to do that.

--

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 post to this group, send email to sqlal...@googlegroups.com.

Visit this group at https://groups.google.com/group/sqlalchemy.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dd9354d6-5759-43f5-9fe4-b69f331e4822%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Christian Barra]

Thanks Mike, I'll try to use @declared_attr and go through the link.