column_property with bindparam

[Mainak Sarcar]

Hi,

I have a column_property defined on a model with a select expression as explained in the examples in http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#using-column-property. I had to add an extra condition to the where clause with a bindparam. I have also defined the column_property with the "deferred=True", so that the query is not fired in the first place. 

I am able to perform queries by using the "undefer" option in the query instance and also passing the param to the query. But when I create an instance of the model and flush and then try to access the column_property, the query tries to get fired and throws exception complaining about the bindparam value not being passed. 

Is there a way I can explicitly prevent the column_property query being fired for an object that is newly created. Or is there a way I can pass the param value for the bindparam when sqlalchemy loads the deferred column_property for the first time on the object.

Thanks

Mainak

[Michael Bayer]

On Dec 12, 2014, at 7:11 AM, Mainak Sarcar <mainak...@gmail.com> wrote:

Hi,

I have a column_property defined on a model with a select expression as explained in the examples in http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#using-column-property. I had to add an extra condition to the where clause with a bindparam. I have also defined the column_property with the "deferred=True", so that the query is not fired in the first place. 

I am able to perform queries by using the "undefer" option in the query instance and also passing the param to the query. But when I create an instance of the model and flush and then try to access the column_property, the query tries to get fired and throws exception complaining about the bindparam value not being passed. 

Is there a way I can explicitly prevent the column_property query being fired for an object that is newly created.

I’m puzzled here, the object is flushed, meaning it now has a database identity.  Then, I’m assuming either the column_property has never been accessed before, so there is no value in __dict__, or you did a commit(), which expired everything, so there’s nothing in __dict__.  Then you access it, which implies you’d like it to return a value of some kind.  However, the property has an unset bound parameter.  What would the correct value be?  If there is no correct value, then what are you expecting to happen when you access this property?

It sounds like what you really want here is a hybrid: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html

[Mainak Sarcar]

Yes Mike, you are right. Once the object is created and flushed, it has the database identity. But the column_property was never accessed. So there is no value for the property in the __dict__. Now if I try to access the parameter, it fires the query with the unset bindparam and I get the exception. Is there a way to set the bindparam value before I access the property for the first time on the object?

BTW here is what my model looks like.

Topic

- id

- name

- project_count (column property with bindparam for user_id)

- projects (relationship)

Project

- id

- name

- topic_id (FK to Topic)

- user_id

- status

I have a REST endpoint for Topic. When I create a new Topic instance, I emit the json version of the object. That is where the column_property gets accessed for the first time. Now in reality, there wont be any projects when the Topic is created, so firing a query is not really required. 

I did give "hybrid property" a try initially using a func.count(Topic.projects). But that did not fire a count query, instead fired the complete query for fetching projects. I even tried to make Topic.projects a "dynamic" load, so that I can pass the extra where clause for user_id and status etc. Then I gave column_property a try based on the example. 

Regards

Mainak

--
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/gfkaA5UwtU4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Michael Bayer]

On Dec 12, 2014, at 10:58 PM, Mainak Sarcar <mainak...@gmail.com> wrote:

Yes Mike, you are right. Once the object is created and flushed, it has the database identity. But the column_property was never accessed. So there is no value for the property in the __dict__. Now if I try to access the parameter, it fires the query with the unset bindparam and I get the exception. Is there a way to set the bindparam value before I access the property for the first time on the object?

BTW here is what my model looks like.

Topic

- id

- name

- project_count (column property with bindparam for user_id)

- projects (relationship)

Project

- id

- name

- topic_id (FK to Topic)

- user_id

- status

Well what immediately doesn’t make sense is that what does Topic.project_count mean if there is no user_id that is local to Topic?   the Topic has N number of projects, but you only want M, those with some arbitrary user_id.  So it makes no sense to use column_property for this.  You need to pass in user_id to a function as an argument.    Suppose column_property did what you wanted, now Topic.project_count is “10”.  But now user_id, somewhere on the outside, changes to something else.  Now Topic.project_count is incorrect.  It doesn’t make sense for this to be a “mapped” value, because Topic has no fixed user_id.

OTOH if user_id were part of Topic then you’d just use a correlated subquery as you see in http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#using-column-property.

i think basically it doesn’t make any sense to use a dynamically bound parameter with column_property().

I have a REST endpoint for Topic. When I create a new Topic instance, I emit the json version of the object. That is where the column_property gets accessed for the first time. Now in reality, there wont be any projects when the Topic is created, so firing a query is not really required. 

I did give "hybrid property" a try initially using a func.count(Topic.projects). But that did not fire a count query, instead fired the complete query for fetching projects. I even tried to make Topic.projects a "dynamic" load, so that I can pass the extra where clause for user_id and status etc. Then I gave column_property a try based on the example. 

Regards

Mainak

On Fri, Dec 12, 2014 at 9:44 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:

On Dec 12, 2014, at 7:11 AM, Mainak Sarcar <mainak...@gmail.com> wrote:

Hi,

I have a column_property defined on a model with a select expression as explained in the examples in http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html#using-column-property. I had to add an extra condition to the where clause with a bindparam. I have also defined the column_property with the "deferred=True", so that the query is not fired in the first place. 

I am able to perform queries by using the "undefer" option in the query instance and also passing the param to the query. But when I create an instance of the model and flush and then try to access the column_property, the query tries to get fired and throws exception complaining about the bindparam value not being passed. 

Is there a way I can explicitly prevent the column_property query being fired for an object that is newly created.

I’m puzzled here, the object is flushed, meaning it now has a database identity.  Then, I’m assuming either the column_property has never been accessed before, so there is no value in __dict__, or you did a commit(), which expired everything, so there’s nothing in __dict__.  Then you access it, which implies you’d like it to return a value of some kind.  However, the property has an unset bound parameter.  What would the correct value be?  If there is no correct value, then what are you expecting to happen when you access this property?

It sounds like what you really want here is a hybrid: http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/hybrid.html

--
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/gfkaA5UwtU4/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

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.

[Mainak Sarcar]

Yes I totally agree with you. If Topic was mapped to a specific user then I would go that route. In my case it is a shared entity across users, but the projects are specific to a user. So the query changes based on which user is logged in.

Querying for a specific set of M projects out of the total N projects associated to a Topic is very simple with a relationship with explicit primaryjoin condition or by making the relationship with dynamic and putting the required filter clause.

My current requirement is a little quirky because I wanted a mapped property on the Topic entity, whereas I can have the following to get the results too.

query = select([func.count(...).label('proj_count')]).where(.....user clause....)

topics = session.query(Topic, query)

topics = topics.all()

But then the result of this will be a KeyedTuple and I have to access the tuple with its keys. I just cant pass the single Topic entity and have the count data associated with it. I would have to the handle the KeyedTuple in every place. I will try out some more options and see what best can be done.

Thanks,

Mainak

[Michael Bayer]

On Dec 13, 2014, at 3:40 PM, Mainak Sarcar <mainak...@gmail.com> wrote:

Yes I totally agree with you. If Topic was mapped to a specific user then I would go that route. In my case it is a shared entity across users, but the projects are specific to a user. So the query changes based on which user is logged in.

Querying for a specific set of M projects out of the total N projects associated to a Topic is very simple with a relationship with explicit primaryjoin condition or by making the relationship with dynamic and putting the required filter clause.

Well I’d just use a regular relationship and then emit a Query() against it as needed:

def user_specific_projects(self, user_id):

    return object_session(self).query(Project).with_parent(self).filter_by(user_id=user_id).all()