Subject: Handling (None,) Query Results

From: Hagen Finley <[email-masked]>
Date: Sat, 5 Dec 2020 08:57:06 -0700

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.

Best,

Hagen Finley

Fort Collins, CO

---

From: Christophe Pettus <[email-masked]>
Date: Sat, 5 Dec 2020 08:02:25 -0800

> On Dec 5, 2020, at 07:57, Hagen Finley <[email-masked]> wrote:

For #2, you can do that directly in the query:

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

For #1, you can write it more compactly, of course:

	commitd1 = int(cur.fetchone()[0])

Note that there's no such thing as an int with value 450992.10, because that's not an integer.  It will truncate it if you cast it to int, or you can use other operations to round it the way you'd like do.

As you probably know, it's returning a tuple because you are getting back a row of one column, and a Decimal because (presumably) that's what type revusd is in the database.

--
-- Christophe Pettus
   [email-masked]

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Dec 2020 08:02:47 -0800

>   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.

-- 
Adrian Klaver
[email-masked]

---

From: <[email-masked]>
Date: Sat, 5 Dec 2020 09:14:57 -0700

I tried  the COALESCE approach but I thought the query still returned (None,).

However, I just tried it again and I got a (Decimal('0'),) return. 

I should be able to make that work. 

Thanks everyone for your rapid assistance.

Best,

Hagen

-----Original Message-----
From: Adrian Klaver <[email-masked]> 
Sent: Saturday, December 5, 2020 9:03 AM
To: Hagen Finley <[email-masked]>; [email-masked]; [email-masked]
Subject: Re: Handling (None,) Query Results

On 12/5/20 7:57 AM, Hagen Finley wrote:
> sum(revenue) where select statements in psycopg2 have proven to be 
> more complex than I imagined.
> 
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big 
> Company' AND stage LIKE 'Commit%';")
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount = 'Big 
> Company' AND stage LIKE 'Win%';")
> wind1 = cur.fetchone()
> conn.commit()
> wind2 = wind1[0]
> 
> 
> Possibly ignoring my fledgling attempts to solve this problem, is 
> there a simple method by which people convert the:
> 

Just do it in the query:

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

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 5 Dec 2020 08:19:32 -0800

> I tried  the COALESCE approach but I thought the query still returned (None,).

 From docs:

https://www.postgresql.org/docs/12/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL

"The COALESCE function returns the first of its arguments that is not 
null. Null is returned only if all arguments are null. ..."

So if it returned NULL/None then the first and second values both 
resolved to NULL.