Thread: 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 = 0450992.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
> 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