New query doesn't expire column_property with bindparam

[Lukas Siemon]

The column_property seems to be cached even when a new query is issued. Is doesn't matter if I use deferred or not. The expunge is obviously really ugly as a workaround. Is this something that could potentially be fixed or is there a better way of "flushing" the cache?

We are using this pattern in many places in our application. However I only noticed today that the value gets cached. I would very much prefer a generic solution (i.e. where the model is declared and not where the query is generated).

Test case:

import unittest
from sqlalchemy import (Column, Integer, String, select, bindparam)
from sqlalchemy.orm import (column_property, undefer)
from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy


class TestDynamicBindparam(unittest.TestCase):

    def setUp(self):
        # -- create all the database models
        app = Flask(__name__)
        app.config['SQLALCHEMY_DATABASE_URI'] = ("sqlite://")
        db = SQLAlchemy(app)

        class Label(db.Model):
            __tablename__ = 'label'
            id = Column(Integer, primary_key=True, nullable=False)

            write_only = column_property(
                select(['-w-' == bindparam('perm', value=None, type_=String)]),
                deferred=True
            )

        db.drop_all()
        db.create_all()

        self.Label = Label
        self.db = db

    def test_dynamic_bindparam(self):
        label = self.Label()
        self.db.session.add(label)
        self.db.session.commit()
        self.assertEqual(label.id, 1)
        self.assertIsNone(label.write_only)

        label = label.query.options(
            undefer(self.Label.write_only)
        ).params(perm='-w-').one()
        self.assertIsNone(label.write_only)  # this should be True (!)

        self.db.session.expunge(label)

        label = label.query.options(
            undefer(self.Label.write_only)
        ).params(perm='-w-').one()
        self.assertTrue(label.write_only)

        label = label.query.options(
            undefer(self.Label.write_only)
        ).params(perm='---').one()
        self.assertTrue(label.write_only)  # this should be False (!)

[Mike Bayer]

this is not really a great way to use column_property as it is treated like any other persisted attribute, and is not reloaded unnecessarily. To have an attribute be overwritten on query, use either session.expire(obj, ['attrname']) or query.populate_existing(): http://docs.sqlalchemy.org/en/rel_1_0/orm/session_api.html?highlight=session.expire#sqlalchemy.orm.session.Session.expire http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=query.populate_existing#sqlalchemy.orm.query.Query.populate_existing

since you're looking for an ad-hoc column value I'd recommend just using simple column expressions instead of querying for a full object, see the second example at http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#querying.

[Lukas Siemon]

Thank you for the quick reply! It seems populate_existing is what I am looking for.

Can you clarify a little bit what exactly it does? The documentation is sparse unfortunately.

Some questions I have:

• Can using populate_existing change the query that is generated? My understanding is that it shouldn't.

• Could accessing properties on the returned objects cause queries to be executed that would not have been executed without populate_existing?

• My understanding is that populate_existing uses fetched data and always overwrites existing data model data instead of knowing "oh I've seen this object before - no need to overwrite identical data". What happens with data that is not fetched but already in the model?

• What negative impacts could I be seeing from using this option?

[Mike Bayer]

Can using populate_existing change the query that is generated?

no, it only says that when it locates already loaded objects, to treat them more or less like they are new and write data into them

Could accessing properties on the returned objects cause queries to be executed that would not have been executed without populate_existing?

yup, both the deferred loader for columns and the lazyloader for relationships (the latter is the default) will pop whatever value is present for their attribute and reset themselves as the "load on access" callable.

My understanding is that populate_existing uses fetched data and always overwrites existing data model data instead of knowing "oh I've seen this object before - no need to overwrite identical data". What happens with data that is not fetched but already in the model?

it's discarded

What negative impacts could I be seeing from using this option?

well any changes to model objects that haven't been flushed would be overwritten as well, though when using autoflush a flush always occurs right before Query emits SQL. It also means that a query which loads rows that are already loaded is going to spend a lot more in-Python time re-populating those objects which is usually unnecessary. and because deferred() and relationships with lazy=True pop out attributes it means you'll have more queries emitted on attribute access, again for data that is already local to the transaction and is very unlikely to show visible changes (though this depends on isolation level as well, some discussion at http://docs.sqlalchemy.org/en/rel_1_0/orm/session_state_management.html?highlight=isolation#when-to-expire-or-refresh particularly in the sidebar).

[Lukas Siemon]

Thank you! That solves my issue!

I was thinking about another possible solution for this: Is there a way to change the session on a query that is already defined but not yet executed?

I'm using something similar to the following to serialize objects. However I don't care about existing state. It's pretty much straight db -> json. If I can just use a separate session or no session at all that would also solve my issue.

[to_json(res) for res in query]

[Mike Bayer]

here you go: http://docs.sqlalchemy.org/en/rel_1_0/orm/query.html?highlight=with_session#sqlalchemy.orm.query.Query.with_session