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
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 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
