From: | <hagen(at)datasundae(dot)com> |
---|---|
To: | "'Adrian Klaver'" <adrian(dot)klaver(at)aklaver(dot)com>, <psycopg(at)lists(dot)postgresql(dot)org>, <psycopg(at)postgresql(dot)org> |
Subject: | RE: Handling (None,) Query Results |
Date: | 2020-12-05 16:14:57 |
Message-ID: | 067801d6cb21$c6b442f0$541cc8d0$@datasundae.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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(dot)klaver(at)aklaver(dot)com>
Sent: Saturday, December 5, 2020 9:03 AM
To: Hagen Finley <hagen(at)datasundae(dot)com>; psycopg(at)lists(dot)postgresql(dot)org; psycopg(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-12-05 16:19:32 | Re: Handling (None,) Query Results |
Previous Message | Adrian Klaver | 2020-12-05 16:02:47 | Re: Handling (None,) Query Results |