update with in clause from kwargs

[Larry Martell]

I am trying to write a function that takes kwargs as a param and
generates an update statement where the rows to be updated are
specified in an in clause.

Something like this:

def update_by_in(self, **kwargs):
filter_group = []
for col in kwargs['query_params']:
# obviously this line does not work as col is a string,
but this is the intent
filter_group.append(col.in_(tuple(kwargs['query_params'][col])))

self._session.query(self.model_class).filter(*filter_group).update(kwargs['values'])

self.update_by_in(
**{'query_params': {'companyCode': ['A', 'B', 'C']},
'values': {'portfolioName': 'test'}}
)

Is there a way to do this?

[Lele Gaifax]

Sure, and easy enough: replace the line where you append to `filter_group`
with something like

attr = getattr(self.model_class, col)
filter_group.append(attr.in_(tuple(kwargs['query_params'][col])))

that is, obtain the mapped class member named after "col", and use that to
build the filter expression.

ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Larry Martell]

On Tue, Aug 3, 2021 at 11:11 PM Lele Gaifax <le...@metapensiero.it> wrote:
>
> Larry Martell <larry....@gmail.com> writes:
>
> > I am trying to write a function that takes kwargs as a param and
> > generates an update statement where the rows to be updated are
> > specified in an in clause.
> >
> > Something like this:
> >
> > def update_by_in(self, **kwargs):
> > filter_group = []
> > for col in kwargs['query_params']:
> > # obviously this line does not work as col is a string,
> > but this is the intent
> > filter_group.append(col.in_(tuple(kwargs['query_params'][col])))
> >
> > self._session.query(self.model_class).filter(*filter_group).update(kwargs['values'])
> >
> > self.update_by_in(
> > **{'query_params': {'companyCode': ['A', 'B', 'C']},
> > 'values': {'portfolioName': 'test'}}
> > )
> >
> > Is there a way to do this?
>
> Sure, and easy enough: replace the line where you append to `filter_group`
> with something like
>
> attr = getattr(self.model_class, col)
> filter_group.append(attr.in_(tuple(kwargs['query_params'][col])))
>
> that is, obtain the mapped class member named after "col", and use that to
> build the filter expression.

Thanks for the reply. When I do that, filter group ends up like:

print(type(filter_group[0]))

<class 'sqlalchemy.sql.elements.BinaryExpression'>

print(filter_group[0])

dbo."Portfolio"."companyCode" IN (:companyCode_1, :companyCode_2,
:companyCode_3)

Which then fails with:

sqlalchemy.orm.evaluator.UnevaluatableError: Cannot evaluate
clauselist with operator <function comma_op at 0x7f632f8f3dc0>

I am passing in:

**{'query_params': {'companyCode': ['A', 'B', 'C']},
'values': {'portfolioName': 'test'}}

Any ideas how to get past this?

[Larry Martell]

I found the issue was not from the select but from the update. But I
can't seem to get that to work. Here is my code now (to remove any
questions about the arg passing to update I hard coded for testing):

filter_group = []
for col in kwargs['query_params']:

attr = getattr(self.model_class, col)
filter_group.append(attr.in_(tuple(kwargs['query_params'][col])))

self._session.query(self.model_class).\
filter(*filter_group).\
update({'Portfolio.portfolioName': 'xxxx'})

This gives:

sqlalchemy.orm.evaluator.UnevaluatableError: Cannot evaluate
clauselist with operator <function comma_op at 0x7f33f037adc0>

To verify the query part is working I printed out the return from

self._session.query(self.model_class).filter(*filter_group).all()

[<common.models_dec_core.Portfolio object at 0x7f2b98157f40>]

What am I doing wrong here?

[Larry Martell]

I got this working by adding synchronize_session='fetch' to the
update. Really would like to know why that was needed - I have
similar code, but that does not use variables (the model and columns
are hard coded) and that works without synchronize_session.

[Lele Gaifax]

I think a full traceback could help in diagnosing the problem.