Subject: Re: Handling (None,) Query Results
To: Hagen Finley <hagen@datasundae.com>, psycopg@lists.postgresql.org,
 psycopg@postgresql.org
References: <5f75fbb2-6da2-60f3-5488-9013d1328361@datasundae.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <80505104-1778-6157-cb9f-8cbafb748660@aklaver.com>
Date: Sat, 5 Dec 2020 08:02:47 -0800
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.5.1
MIME-Version: 1.0
In-Reply-To: <5f75fbb2-6da2-60f3-5488-9013d1328361@datasundae.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 12/5/20 7:57 AM, Hagen Finley wrote:
> Hello,
> 
> I was thinking ‘finally, something I know how to do’ but alas simple 
> sum(revenue) where select statements in psycopg2 have proven to be more 
> complex than I imagined.
> 
> First, there’s the Decimal tuple parsing which I can do (albeit somewhat 
> unnaturally)  (Decimal('450992.10'),)
> 
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big Company' AND stage 
> LIKE 'Commit%';")
> commitd1 = cur.fetchone()
> conn.commit()
> commitd2 = commitd1[0]
> 
> 
> if type(commitd2)is not None:commit =int(commitd2)
>    
> else:
>      commit =0
> 
> 450992.10
> 
> <class 'int'>
> 
> If there is a better way to get to int I'd be all ears.
> 
> 
> Second, there’s the NoneType  (None,) result from queries with no values.
> 
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big Company' AND stage 
> LIKE 'Win%';")
> wind1 = cur.fetchone()
> conn.commit()
> wind2 = wind1[0]
> 
> 
> if type(wind2)is int:win =int(wind2)
> 
> else:
>      win =0
> 
> My goal is to return 0.00 when there are no results and an int when 
> there are results using the same code. Right now my if statements are 
> different:
> 
> if type(commitd2)is not None:
> 
> if type(wind2)is int:
> 
> Possibly ignoring my fledgling attempts to solve this problem, is there 
> a simple method by which people convert the:
> 
> 1. (Decimal('450992.10'),) to a <class 'int'> 450992.10?
> 
> 2. (None,) to 0.00?
> 
>   Thanks for your thoughts on this question.

Just do it in the query:

"SELECT COALESCE(SUM(revusd, 0)) FROM sfdc where saccount = 'Big 
Company' AND stage  LIKE 'Commit%';"

If SUM(revusd) is NULL then COALESCE will substitute 0.


> 
> Best,
> 
> Hagen Finley
> 
> Fort Collins, CO


-- 
Adrian Klaver
adrian.klaver@aklaver.com