Обсуждение: Handling (None,) Query Results

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
 

<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

> On Dec 5, 2020, at 07:57, Hagen Finley <hagen@datasundae.com> wrote:
> 1. (Decimal('450992.10'),) to a <class 'int'> 450992.10?
>
> 2. (None,) to 0.00?
>  Thanks for your thoughts on this question.


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
youcast 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
   xof@thebuild.com

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

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 <adrian.klaver@aklaver.com>
Sent: Saturday, December 5, 2020 9:03 AM
To: Hagen Finley <hagen@datasundae.com>; psycopg@lists.postgresql.org; psycopg@postgresql.org
Subject: Re: Handling (None,) Query Results

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

On 12/5/20 8:14 AM, hagen@datasundae.com wrote:
> 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.

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

-- 
Adrian Klaver
adrian.klaver@aklaver.com