On Tue, Aug 3, 2021 at 7:26 PM dn via Python-list <python-list@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. -- https://mail.python.org/mailman/listinfo/python-list