On Sun, Jan 31, 2016 at 6:42 AM, Michael Torrie <torr...@gmail.com> wrote:
> On 01/30/2016 01:22 AM, Frank Millman wrote:
>> There are times when I want to execute a SELECT statement, and test for
>> three possibilities -
>> - if no rows are returned, the object does not exist
>> - if one row is returned, the object does exist
>> - if more that one row is returned, raise an exception
>
> Is there a reason you cannot get SQL to answer this question for you?
> Something like:
>
> SELECT count(some_field) WHERE condition
>
> That will always return one row, with one field that will either be 0,
> 1, or more than 1.
Efficiency. That's a fine way of counting actual rows in an actual
table. However, it's massive overkill to perform an additional
pre-query for something that's fundamentally an assertion (this is a
single-row-fetch API like "select into", and it's an error to fetch
anything other than a single row - but normal usage will never hit
that error), and also, there's no guarantee that the query is looking
at a single table. Plus, SQL's count function ignores NULLs, so you
could get a false result. Using count(*) might be better, but the only
way I can think of to be certain would be something like:
select count(*) from (...)
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.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
