|
From: Simon W. <cs...@ba...> - 2003-10-03 15:36:49
|
I just spent some time trying to figure out how to execute the
equivalent of a "where col like '%text%'" query using SQLObject. I'm now
using the following:
results = Story.select(CONTAINSSTRING(Story.q.body, query))
This is a bit of an eye-sore, especially compared to the neat
Story.q.body.startswith('blah') and Story.q.body.endswith('blah') shortcuts.
Ideally I'd like to be able to perform this type of query using Python's
"in" operator. From reading through SQLBuilder.py this seems to be
already overloaded to support SQL "something IN ('blah1', 'blah2')"
operations. Is there any way this could be inteligently overloaded to
perform like %% queries on strings and IN queries on tuples?
Alternatively, a contains() method similar to startswith() and
endswith() would be useful.
Cheers,
Simon
|
|
From: Brad B. <br...@bb...> - 2003-10-03 18:40:06
|
On Friday, October 3, 2003, at 11:36 AM, Simon Willison wrote:
> I just spent some time trying to figure out how to execute the
> equivalent of a "where col like '%text%'" query using SQLObject. I'm
> now using the following:
>
> results = Story.select(CONTAINSSTRING(Story.q.body, query))
>
> This is a bit of an eye-sore, especially compared to the neat
> Story.q.body.startswith('blah') and Story.q.body.endswith('blah')
> shortcuts.
You're in luck. select() takes a string as a where argument. So:
Story.select("body like '%something%'")
(replaced with proper variables and escaping the %'s as necessary, if
applicable.)
The startswith and endwith are just methods of Python string objects,
and because SQLObject is (mostly) transparent, string columns act like
Python strings.
> Ideally I'd like to be able to perform this type of query using
> Python's "in" operator. From reading through SQLBuilder.py this seems
> to be already overloaded to support SQL "something IN ('blah1',
> 'blah2')" operations. Is there any way this could be inteligently
> overloaded to perform like %% queries on strings and IN queries on
> tuples?
Python's "in" operator checks for the existence of a value in a
sequence. What you're trying to do is ask for all the objects (or all
the SQLObjects, as it were :) that meet a certain criteria. The
semantics are different.
> Alternatively, a contains() method similar to startswith() and
> endswith() would be useful.
Python strings already have a find() method, but I think you meant to
pass a string as the where clause.
Hope that helps,
--
Brad Bollenbach
BBnet.ca
|
|
From: Ian B. <ia...@co...> - 2003-10-03 18:44:23
|
On Friday, October 3, 2003, at 01:40 PM, Brad Bollenbach wrote:
>> Ideally I'd like to be able to perform this type of query using
>> Python's "in" operator. From reading through SQLBuilder.py this seems
>> to be already overloaded to support SQL "something IN ('blah1',
>> 'blah2')" operations. Is there any way this could be inteligently
>> overloaded to perform like %% queries on strings and IN queries on
>> tuples?
>
> Python's "in" operator checks for the existence of a value in a
> sequence. What you're trying to do is ask for all the objects (or all
> the SQLObjects, as it were :) that meet a certain criteria. The
> semantics are different.
Not exactly -- SQLBuilder (which you get at with SomeSQLObject.q)
captures Python expressions (to the degree possible), which can then be
turned into SQL clauses. In this particular case ("in") I don't think
Python makes it possible to do this, but several operators can be
overloaded like this.
Ian
|
|
From: Brad B. <br...@bb...> - 2003-10-03 19:05:08
|
On Friday, October 3, 2003, at 02:44 PM, Ian Bicking wrote:
> On Friday, October 3, 2003, at 01:40 PM, Brad Bollenbach wrote:
>>> Ideally I'd like to be able to perform this type of query using
>>> Python's "in" operator. From reading through SQLBuilder.py this
>>> seems to be already overloaded to support SQL "something IN
>>> ('blah1', 'blah2')" operations. Is there any way this could be
>>> inteligently overloaded to perform like %% queries on strings and IN
>>> queries on tuples?
>>
>> Python's "in" operator checks for the existence of a value in a
>> sequence. What you're trying to do is ask for all the objects (or all
>> the SQLObjects, as it were :) that meet a certain criteria. The
>> semantics are different.
>
> Not exactly -- SQLBuilder (which you get at with SomeSQLObject.q)
> captures Python expressions (to the degree possible), which can then
> be turned into SQL clauses. In this particular case ("in") I don't
> think Python makes it possible to do this, but several operators can
> be overloaded like this.
Perhaps the semantics aren't really completely different, but as you
say, 1. Python would actually have to allow this (it may or may not, I
haven't checked), 2. it would have to be written like:
Foo.select('bar' in Foo.q.baz)
instead of the normal:
Foo.select(Foo.q.baz == 'baz')
used for direct comparisons. This would be too inconsistent (and
perhaps too easy to confuse with writing the other way around), IMHO.
--
Brad Bollenbach
BBnet.ca
|
|
From: Ian B. <ia...@co...> - 2003-10-03 18:41:32
|
On Friday, October 3, 2003, at 10:36 AM, Simon Willison wrote:
> I just spent some time trying to figure out how to execute the
> equivalent of a "where col like '%text%'" query using SQLObject. I'm
> now using the following:
>
> results = Story.select(CONTAINSSTRING(Story.q.body, query))
>
> This is a bit of an eye-sore, especially compared to the neat
> Story.q.body.startswith('blah') and Story.q.body.endswith('blah')
> shortcuts.
>
> Ideally I'd like to be able to perform this type of query using
> Python's "in" operator. From reading through SQLBuilder.py this seems
> to be already overloaded to support SQL "something IN ('blah1',
> 'blah2')" operations. Is there any way this could be inteligently
> overloaded to perform like %% queries on strings and IN queries on
> tuples?
Hmm... well, there's two string situations, (str in col) and (col in
str). (str in col) means "col LIKE '%str%'", and (col in str) means
"str LIKE '%'||col||'%'". And then (col in tuple) means "col IN
(tuple...)".
But I don't think (col in str) can work, because we can't override
str's __contains__. Which is why we can't really do that with tuples
either (and hence the IN() function -- though that function could be
extended to take strings in addition to tuples.
> Alternatively, a contains() method similar to startswith() and
> endswith() would be useful.
That seems better. Since we can't make the "in" operator work for all
cases, I think it will just be more confusing if it works some places
and not others. Easier to just add another method.
Ian
|
