SQLALchemy: update with in clause from kwargs

[dn]

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?

PS I fear even that step is/those steps are more complicated than needed
- but you know your data and schema better than I!


Critique:
1 never, never, never(!) use a name which will "shadow" a Python keyword
or frequently-used function-name (in this case "values" ) - if you don't
confuse Python you will confuse simple-boys like me! Plus, when you come
back in six-month's time, does "values" tell you what kind of value it
is/holds?
2 Python != C | Java
Thus: company_code, portfolio_name


Web.Refs:
https://www.dictionary.com/browse/bamboozled
https://docs.python.org/3/library/itertools.html
-- 
Regards,
=dn