SQLALchemy: update with in clause from kwargs

[Larry Martell]

On Tue, Aug 3, 2021 at 7:26 PM dn via Python-list
<python-list at python.org> wrote:
>
> On 04/08/2021 13.08, Larry Martell wrote:
> > 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? I think I need to use setattr in building
> > up the filter_group list, but I'm not quite sure how to do it.
>
>
> When feeling bamboozled by a problem, particularly when using
> sophisticated tools such as SQLAlchemy, the trick is often to simplify
> the problem.
>
> Step 1 (using the sample data provided)
> Write the query on paper - and as constant-values.
>
> Step 2
> Compare the two input dicts with that requirement.
>
> Step 3
> Work-out the transformation(s) required...
>
>
> One complexity is that the parameter to update_by_in() is formed by
> joining two dicts. However, the function later tries to treat them in
> distinct fashions. Why the join/why not two parameters?
>
> companyCode = ['A', 'B', 'C']
> values = {'portfolioName': 'test'}
>
> leading to:
>
> self.update_by_in( companyCode, values )
>
> and:
>
> def update_by_in(self, company_code, portfolio_type ):
>     ....
>
>
> As to the core of the question-asked, I'm a little confused (which may
> be my fuzzy head). Do you want the update(s) - portrayed as a list -
> like this:
>
> [('A', 'test'), ('B', 'test'), ('C', 'test')]
>
> like this:
>
> [('A', 'portfolioName'), ('B', None), ('C', None)]
>
> or only:
>
> [('A', 'portfolioName')]
>
>
> You will find a friend in the itertools (PSL) library:
>
> import itertools as it
>
> list( it.product( companyCode, values.values() ) )
> [('A', 'test'), ('B', 'test'), ('C', 'test')]
>
> list( it.zip_longest( companyCode, values.values() ) )
> [('A', 'test'), ('B', None), ('C', None)]
>
> list( zip( companyCode, values ) )
> [('A', 'portfolioName')]
>
>
> Now, have we simplified things to the point of being able to more-easily
> code the update and filter?

I appreciate the reply, but it does not address my issue, which was
how to get at the column object. Turned out that was simple enough:

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

Unfortunately that is causing something to change the query_params, as
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>

This has now become more of a sqlalchemy question than a python one.