SQLALchemy: update with in clause from kwargs
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. -- https://mail.python.org/mailman/listinfo/python-list
Re: SQLALchemy: update with in clause from kwargs
On 2021-08-04 02: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. If it's any help, on this page: https://docs.sqlalchemy.org/en/14/core/metadata.html it has this: # access the column "employee_id": employees.columns.employee_id # or just employees.c.employee_id # via string employees.c['employee_id'] -- https://mail.python.org/mailman/listinfo/python-list
Re: SQLALchemy: update with in clause from kwargs
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 -- https://mail.python.org/mailman/listinfo/python-list
Re: SQLALchemy: update with in clause from kwargs
On Tue, Aug 3, 2021 at 7:26 PM dn via Python-list 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])) 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 This has now become more of a sqlalchemy question than a python one. -- https://mail.python.org/mailman/listinfo/python-list