On Wed, Aug 4, 2021 at 6:08 PM Larry Martell <larry.mart...@gmail.com> wrote:
>
> On Wed, Aug 4, 2021 at 12:30 PM Larry Martell <larry.mart...@gmail.com> wrote:
> >
> > On Tue, Aug 3, 2021 at 11:11 PM Lele Gaifax <l...@metapensiero.it> wrote:
> > >
> > > Larry Martell <larry.mart...@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?
>
> 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?
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.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/CACwCsY5VwFrL_ap-Rc6GjDgdHPUWu2_uwvV%2BK7bk4d0NYptNHw%40mail.gmail.com.
