public inbox for [email protected]
help / color / mirror / Atom feedHandling (None,) Query Results
5+ messages / 4 participants
[nested] [flat]
* Handling (None,) Query Results
@ 2020-12-05 15:57 Hagen Finley <[email protected]>
2020-12-05 16:02 ` Re: Handling (None,) Query Results Christophe Pettus <[email protected]>
2020-12-05 16:02 ` Re: Handling (None,) Query Results Adrian Klaver <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: Hagen Finley @ 2020-12-05 15:57 UTC (permalink / raw)
To: [email protected]; psycopg
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling (None,) Query Results
2020-12-05 15:57 Handling (None,) Query Results Hagen Finley <[email protected]>
@ 2020-12-05 16:02 ` Christophe Pettus <[email protected]>
1 sibling, 0 replies; 5+ messages in thread
From: Christophe Pettus @ 2020-12-05 16:02 UTC (permalink / raw)
To: Hagen Finley <[email protected]>; +Cc: [email protected]
> On Dec 5, 2020, at 07:57, Hagen Finley <[email protected]> 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 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 protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling (None,) Query Results
2020-12-05 15:57 Handling (None,) Query Results Hagen Finley <[email protected]>
@ 2020-12-05 16:02 ` Adrian Klaver <[email protected]>
2020-12-05 16:14 ` RE: Handling (None,) Query Results [email protected]
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2020-12-05 16:02 UTC (permalink / raw)
To: Hagen Finley <[email protected]>; [email protected]; psycopg
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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Handling (None,) Query Results
2020-12-05 15:57 Handling (None,) Query Results Hagen Finley <[email protected]>
2020-12-05 16:02 ` Re: Handling (None,) Query Results Adrian Klaver <[email protected]>
@ 2020-12-05 16:14 ` [email protected]
2020-12-05 16:19 ` Re: Handling (None,) Query Results Adrian Klaver <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: [email protected] @ 2020-12-05 16:14 UTC (permalink / raw)
To: 'Adrian Klaver' <[email protected]>; [email protected]; 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 <[email protected]>
Sent: Saturday, December 5, 2020 9:03 AM
To: Hagen Finley <[email protected]>; [email protected]; [email protected]
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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Handling (None,) Query Results
2020-12-05 15:57 Handling (None,) Query Results Hagen Finley <[email protected]>
2020-12-05 16:02 ` Re: Handling (None,) Query Results Adrian Klaver <[email protected]>
2020-12-05 16:14 ` RE: Handling (None,) Query Results [email protected]
@ 2020-12-05 16:19 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2020-12-05 16:19 UTC (permalink / raw)
To: [email protected]; [email protected]; psycopg
On 12/5/20 8:14 AM, [email protected] 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
[email protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-12-05 16:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-05 15:57 Handling (None,) Query Results Hagen Finley <[email protected]>
2020-12-05 16:02 ` Christophe Pettus <[email protected]>
2020-12-05 16:02 ` Adrian Klaver <[email protected]>
2020-12-05 16:14 ` [email protected]
2020-12-05 16:19 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox