Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Ladislav Lenart]

Hello.

I responded yesterday but it seems the mail did not make it. Here it goes and
sorry for the duplicate...


On 3.3.2016 21:09, Ladislav Lenart wrote:
> Hello.
> 
> 
> On 3.3.2016 19:52, Brian Cherinka wrote:
>> Yeah, that might ultimately be the best way to go if things get too 
>> complicated.
>>   I think people might not want to re-run several lines of code to change 
>> some
>> parameters but that could be a thing I make them just live with.
> 
> I think that the two concerns, namely:
> * update SQLAlchemy query / rebuild it from scratch
> * execute query manually / automatically
> are independent.
> 
> You could implement the following background task:
> * parse the code snippet
> * build the query from it / report errors to the user
> * execute the query
> * show / update results to the user
> and (re)schedule it in the near future whenever the user edits her code 
> snippet
> (like an incremental search).
> 
> But this starts to get complicated, i.e. the usual threading caveats apply.
> 
> HTH,
> 
> Ladislav Lenart
> 
> 
>> On Thursday, March 3, 2016 at 3:06:57 AM UTC-5, Ladislav Lenart wrote:
>>
>> Hello.
>>
>> I think it would be (much) easier to simply rebuild the query from 
>> scratch
>> before each run. IMHO the time to build the query is not that big a 
>> factor to
>> justify the added source code complexity.
>>
>> HTH,
>>
>> Ladislav Lenart
>>
>>
>> On 3.3.2016 05:47, Brian Cherinka wrote:
>> >
>> >
>> > well you need a list of names so from a mapped class you can get:
>> >
>> > for name in inspect(MyClass).column_attrs.keys():
>> > if name in :
>> > q = q.filter_by(name = bindparam(name))
>> >
>> > though I'd think if you're dynamically building the query you'd 
>> have the
>> > values already, not sure how it's working out that you need 
>> bindparam()
>> > at that stage...
>> >
>> >  
>> > Ok.  I'll try this out. This looks like it could work.  I think I need 
>> it for
>> > the cases where a user specifies a query with condition e.g. X < 10, 
>> runs it,
>> > gets results.  Then they want to change the condition to X < 5 and 
>> rerun the
>> > query.  As far as I know, if condition 2 gets added into the filter, 
>> you
>> would
>> > have both X < 10 and X < 5 in your filter expression.  Rather than a 
>> single
>> > updated X < 5.
>> >
>> > What would be even more awesome is if there was a way to also update 
>> the
>> > operator in place as well.  So changing X < 10 to X > 10.  
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

Yeah, that might ultimately be the best way to go if things get too 
complicated.   I think people might not want to re-run several lines of 
code to change some parameters but that could be a thing I make them just 
live with.

On Thursday, March 3, 2016 at 3:06:57 AM UTC-5, Ladislav Lenart wrote:
>
> Hello. 
>
> I think it would be (much) easier to simply rebuild the query from scratch 
> before each run. IMHO the time to build the query is not that big a factor 
> to 
> justify the added source code complexity. 
>
> HTH, 
>
> Ladislav Lenart 
>
>
> On 3.3.2016 05:47, Brian Cherinka wrote: 
> > 
> > 
> > well you need a list of names so from a mapped class you can get: 
> > 
> > for name in inspect(MyClass).column_attrs.keys(): 
> > if name in : 
> > q = q.filter_by(name = bindparam(name)) 
> > 
> > though I'd think if you're dynamically building the query you'd have 
> the 
> > values already, not sure how it's working out that you need 
> bindparam() 
> > at that stage... 
> > 
> >   
> > Ok.  I'll try this out. This looks like it could work.  I think I need 
> it for 
> > the cases where a user specifies a query with condition e.g. X < 10, 
> runs it, 
> > gets results.  Then they want to change the condition to X < 5 and rerun 
> the 
> > query.  As far as I know, if condition 2 gets added into the filter, you 
> would 
> > have both X < 10 and X < 5 in your filter expression.  Rather than a 
> single 
> > updated X < 5. 
> > 
> > What would be even more awesome is if there was a way to also update the 
> > operator in place as well.  So changing X < 10 to X > 10.   
> > 
> > 
> > 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Ladislav Lenart]

Hello.

I think it would be (much) easier to simply rebuild the query from scratch
before each run. IMHO the time to build the query is not that big a factor to
justify the added source code complexity.

HTH,

Ladislav Lenart


On 3.3.2016 05:47, Brian Cherinka wrote:
> 
> 
> well you need a list of names so from a mapped class you can get:
> 
> for name in inspect(MyClass).column_attrs.keys():
> if name in :
> q = q.filter_by(name = bindparam(name))
> 
> though I'd think if you're dynamically building the query you'd have the
> values already, not sure how it's working out that you need bindparam()
> at that stage...
> 
>  
> Ok.  I'll try this out. This looks like it could work.  I think I need it for
> the cases where a user specifies a query with condition e.g. X < 10, runs it,
> gets results.  Then they want to change the condition to X < 5 and rerun the
> query.  As far as I know, if condition 2 gets added into the filter, you would
> have both X < 10 and X < 5 in your filter expression.  Rather than a single
> updated X < 5. 
> 
> What would be even more awesome is if there was a way to also update the
> operator in place as well.  So changing X < 10 to X > 10.  
> 
> 
> 
> 
> -- 
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email
> to sqlalchemy+unsubscr...@googlegroups.com
> .
> To post to this group, send email to sqlalchemy@googlegroups.com
> .
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

Also, I've noticed that when you update the bindparams

q = q.params(x='1234')

and then try to print the whereclause, the parameters are not updated.  Yet 
in the statement, they are updated. 

print 
q.query.whereclause.compile(dialect=postgresql.dialect(),compile_kwargs={'literal_binds':True})
shows old x condition

print 
q.query.statement.compile(dialect=postgresql.dialect(),compile_kwargs={'literal_binds':True})
 
shows updated x='1234'

Is this a bug or does the whereclause need to be updated separately?  

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

>
>
>
> well you need a list of names so from a mapped class you can get: 
>
> for name in inspect(MyClass).column_attrs.keys(): 
> if name in : 
> q = q.filter_by(name = bindparam(name)) 
>
> though I'd think if you're dynamically building the query you'd have the 
> values already, not sure how it's working out that you need bindparam() 
> at that stage... 
>
>  
Ok.  I'll try this out. This looks like it could work.  I think I need it 
for the cases where a user specifies a query with condition e.g. X < 10, 
runs it, gets results.  Then they want to change the condition to X < 5 and 
rerun the query.  As far as I know, if condition 2 gets added into the 
filter, you would have both X < 10 and X < 5 in your filter expression. 
 Rather than a single updated X < 5. 

What would be even more awesome is if there was a way to also update the 
operator in place as well.  So changing X < 10 to X > 10.  




-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Mike Bayer]

On 03/02/2016 04:59 PM, Brian Cherinka wrote:

Thanks, Mike.  This is excellent.  That did the trick.  That's much
easier than what I was trying to do.   Do you know if there is a way to
auto bindparam every parameter I have in my Declarative Bases, if and
when they get added into a filter?  Basically, I need to allow the user
to be able to modify any parameter they set after the fact, but I have a
crazy amount of parameters to explicitly do this for.


well you need a list of names so from a mapped class you can get:

for name in inspect(MyClass).column_attrs.keys():
   if name in :
   q = q.filter_by(name = bindparam(name))

though I'd think if you're dynamically building the query you'd have the 
values already, not sure how it's working out that you need bindparam() 
at that stage...









Cheers, Brian

On Wednesday, March 2, 2016 at 4:28:46 PM UTC-5, Mike Bayer wrote:




--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

I'm essentially building a tool for a group that doesn't know SQLalchemy, 
and doesn't want to know or care about it, or even SQL in general.  They 
need to be able to query a dataset using minimal input, namely only caring 
about the input filter conditions (e.g. X > 10).  Presumably, they will 
need to be able to update and change these parameters without having to 
reset or rebuild the query I think.  The parameter list is also spread 
across multiple tables as well. 

My query builder is a mix of SQLalchemy + WTForm-Alchemy, but the 
pseudocode is something like

q = Query()
q.createBaseQuery()  # this sets the default   query = 
session.query(ModelClass)
q.set_params(params=params)  # define and set the input parameters to 
search on, as dictionary of {'parameter_name': 'operand value'}  , e.g 
{'X': '< 10'} 
q.add_conditions() # builds and adds the parameters into a giant filter, 
and adds it to the query object, e.g.

myfilt = None
for param in parameters:
 if parameter_table not in join, add it to the join:  
  query = query.join(parameter_table)
 
 parse the input parameter into operand and value
 newfilter = construct a new BinaryExpression based on input
 
 if not myfilt:
 myfilt = and_(newfilter)
 else:
 myfit = and_(myfilt, newfilter)


if any filter exists, add it to query:
query = query.filter(myfilt)



results = q.run()  # runs the sql query and returns the results with either 
query.all(), or query.one(), or query.count(), etc...

So if they want to change the conditions to query with, as far as my 
limited understanding goes, they would either have to rebuild the query 
from scratch and reapply the filters, or they would have to modify the 
values inside the sqlalchemy query object?  And it seems like this 
bindparam is a nice way to allow for flexible attribute changes without 
resetting the query.

Cheers, Brian

On Wednesday, March 2, 2016 at 5:31:09 PM UTC-5, Simon King wrote:
>
> Out of interest, how are you building your query, and why do you need to 
> be able to change the values afterwards? 
>
> Simon 
>
> > On 2 Mar 2016, at 21:59, Brian Cherinka  
> wrote: 
> > 
> > Thanks, Mike.  This is excellent.  That did the trick.  That's much 
> easier than what I was trying to do.   Do you know if there is a way to 
> auto bindparam every parameter I have in my Declarative Bases, if and when 
> they get added into a filter?  Basically, I need to allow the user to be 
> able to modify any parameter they set after the fact, but I have a crazy 
> amount of parameters to explicitly do this for. 
> > 
> > Cheers, Brian 
> > 
> > On Wednesday, March 2, 2016 at 4:28:46 PM UTC-5, Mike Bayer wrote: 
> > 
> > 
> > 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

I'm essentially building a tool for a large group of people that don't know 
SQLalchemy, and will never care enough or want to know, about SQLalchemy or 
SQL in general.  And they need to be able to build queries to the dataset 
based on really minimal information.  All they would input are filter 
conditions of "parameter operand value" and the system builds the rest of 
the query.  They should be able to update these values without having to 
reset and rebuild the query.   

The actual query build is a strange generic combination of SQLalchemy + 
WTForms-Alchemy, but the pseudocode is something like 

q = Query()
q.set_params(params=params)  =  the input parameters they are searching on 
as {'X': '< 10'} 
q.add_conditions()  = builds and adds the filter clauses, something like 

for parameter in parameters:
   if parameter table not in the join: 









On Wednesday, March 2, 2016 at 5:31:09 PM UTC-5, Simon King wrote:
>
> Out of interest, how are you building your query, and why do you need to 
> be able to change the values afterwards? 
>
> Simon 
>
> > On 2 Mar 2016, at 21:59, Brian Cherinka  
> wrote: 
> > 
> > Thanks, Mike.  This is excellent.  That did the trick.  That's much 
> easier than what I was trying to do.   Do you know if there is a way to 
> auto bindparam every parameter I have in my Declarative Bases, if and when 
> they get added into a filter?  Basically, I need to allow the user to be 
> able to modify any parameter they set after the fact, but I have a crazy 
> amount of parameters to explicitly do this for. 
> > 
> > Cheers, Brian 
> > 
> > On Wednesday, March 2, 2016 at 4:28:46 PM UTC-5, Mike Bayer wrote: 
> > 
> > 
> > 
> > 
> > -- 
> > You received this message because you are subscribed to the Google 
> Groups "sqlalchemy" group. 
> > To unsubscribe from this group and stop receiving emails from it, send 
> an email to sqlalchemy+...@googlegroups.com . 
> > To post to this group, send email to sqlal...@googlegroups.com 
> . 
> > Visit this group at https://groups.google.com/group/sqlalchemy. 
> > For more options, visit https://groups.google.com/d/optout. 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Simon King]

Out of interest, how are you building your query, and why do you need to be 
able to change the values afterwards?

Simon

> On 2 Mar 2016, at 21:59, Brian Cherinka  wrote:
> 
> Thanks, Mike.  This is excellent.  That did the trick.  That's much easier 
> than what I was trying to do.   Do you know if there is a way to auto 
> bindparam every parameter I have in my Declarative Bases, if and when they 
> get added into a filter?  Basically, I need to allow the user to be able to 
> modify any parameter they set after the fact, but I have a crazy amount of 
> parameters to explicitly do this for.
> 
> Cheers, Brian
> 
> On Wednesday, March 2, 2016 at 4:28:46 PM UTC-5, Mike Bayer wrote:
> 
> 
> 
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

Thanks, Mike.  This is excellent.  That did the trick.  That's much easier 
than what I was trying to do.   Do you know if there is a way to auto 
bindparam every parameter I have in my Declarative Bases, if and when they 
get added into a filter?  Basically, I need to allow the user to be able to 
modify any parameter they set after the fact, but I have a crazy amount of 
parameters to explicitly do this for.

Cheers, Brian

On Wednesday, March 2, 2016 at 4:28:46 PM UTC-5, Mike Bayer wrote:
>
>
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Mike Bayer]

On 03/02/2016 03:29 PM, Brian Cherinka wrote:

Hi,

After a query has been constructed with some filter conditions applied,
but before the query has been run, what's the best way to replace the
attribute in the filter clause?

Let's say I have a query like this

|
q =session.query(Cube).join(Version).filter(Version.version =='v1_5_1')
|

and I can print the filters with and without the values bound

|
printq.whereclause
version.version =:version_1

q.whereclause.compile(compile_kwargs={'literal_binds':True})
version.version ='v1_5_1'
|

What's the best way to replace the attribute with a new parameter, like
version = 'v1_3_3'?


use a bound parameter from the start, use params():

q = s.query(Cube).join(Version).filter(Version.whatever == bindparam('x'))

results = q.params(x='1234').all()





For a single condition, the type of whereclause is a BinaryExpression,
and I figured out that left, operator, and right, get me the left-hand,
right-hand side of the clause and the operator.  And I can modify the
value with

|
q.whereclause.right.value='v1_3_3'
|

|
printq.whereclause.compile(compile_kwargs={'literal_binds':True})
version.version ='v1_3_3'
|

Is this the best way?

My bigger problem is I have a list of clauses in my query

|
t =session.query(Cube).join(Version,Sample).filter(Version.version
=='v1_5_1',Sample.x <10)
|

now the t.whereclause is a BooleanClauseList and I can't figure out how
to iterate over this list, such that I can do the above, and modify the
version value in place.   What's the best way to do this?  I can't find
the proper location in the documentation that describes a
BooleanClauseList.  Searching for it returns 0 results.

Thanks.

--
You received this message because you are subscribed to the Google
Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] replacing a filter attribute, or iterating over a booleanclauselist?

[Brian Cherinka]

Hi, 

After a query has been constructed with some filter conditions applied, but 
before the query has been run, what's the best way to replace the attribute 
in the filter clause?

Let's say I have a query like this

q = session.query(Cube).join(Version).filter(Version.version == 'v1_5_1')

and I can print the filters with and without the values bound

print q.whereclause
version.version = :version_1

q.whereclause.compile(compile_kwargs={'literal_binds':True})
version.version = 'v1_5_1'

What's the best way to replace the attribute with a new parameter, like 
version = 'v1_3_3'?

For a single condition, the type of whereclause is a BinaryExpression, and 
I figured out that left, operator, and right, get me the left-hand, 
right-hand side of the clause and the operator.  And I can modify the value 
with 

q.whereclause.right.value='v1_3_3'

print q.whereclause.compile(compile_kwargs={'literal_binds':True})
version.version = 'v1_3_3'

Is this the best way?  

My bigger problem is I have a list of clauses in my query

t = session.query(Cube).join(Version,Sample).filter(Version.version == 
'v1_5_1', Sample.x < 10)

now the t.whereclause is a BooleanClauseList and I can't figure out how to 
iterate over this list, such that I can do the above, and modify the 
version value in place.   What's the best way to do this?  I can't find the 
proper location in the documentation that describes a BooleanClauseList. 
 Searching for it returns 0 results. 

Thanks. 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.