On Sun, Jan 31, 2016 at 8:57 AM, Michael Torrie <torr...@gmail.com> wrote:
> On 01/30/2016 02:19 PM, Chris Angelico wrote:
>> where the ... is the full original query. In other words, the whole
>> query has to be run twice - once to assert that there's exactly one
>> result, and then a second time to get that result. The existing
>> algorithm ("try to fetch a row - if it fails error; then try to fetch
>> another - if it succeeds, error") doesn't need to fetch more than two
>> results, no matter how big the query result is.
>
> Actually it occurs to me this doesn't have to be true. The same
> information he needs to know can be done with one query and only 1 result.
>
> SELECT count(some_id_field),field1,field2,field3 FROM wherever WHERE
> conditions
>
> If the first column (or whatever you decide to alias it as) contains a
> count, and the rest of the information is still there. If count is 1,
> then the row is what you want and you can do whatever you wish with it.
> If not, throw your exception.
That actually violates the SQL spec. Some servers will accept it,
others won't. (You're not supposed to mix column functions and
non-column functions.) It also can't cope with 'group by' queries, as
it'll count the underlying rows, not the groups. I also suspect it
can't handle join queries.
The original approach is still the most general, and IMO the best.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
