From: <hagen@datasundae.com>
To: "'Adrian Klaver'" <adrian.klaver@aklaver.com>,
	<psycopg@lists.postgresql.org>,
	<psycopg@postgresql.org>
References: <5f75fbb2-6da2-60f3-5488-9013d1328361@datasundae.com>
 <80505104-1778-6157-cb9f-8cbafb748660@aklaver.com>
In-Reply-To: <80505104-1778-6157-cb9f-8cbafb748660@aklaver.com>
Subject: RE: Handling (None,) Query Results
Date: Sat, 5 Dec 2020 09:14:57 -0700
Message-ID: <067801d6cb21$c6b442f0$541cc8d0$@datasundae.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Language: en-us
Thread-Index: AQHKyhNfAY+U0JaN6MQjBdkj3+KcvAH1SHdkqfEDYQA=
Precedence: bulk

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

I should be able to make that work.=20

Thanks everyone for your rapid assistance.

Best,

Hagen

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>=20
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,
>=20
> I was thinking =E2=80=98finally, something I know how to do=E2=80=99 =
but alas simple
> sum(revenue) where select statements in psycopg2 have proven to be=20
> more complex than I imagined.
>=20
> First, there=E2=80=99s the Decimal tuple parsing which I can do =
(albeit=20
> somewhat
> unnaturally)  (Decimal('450992.10'),)
>=20
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount =3D 'Big=20
> Company' AND stage LIKE 'Commit%';")
> commitd1 =3D cur.fetchone()
> conn.commit()
> commitd2 =3D commitd1[0]
>=20
>=20
> if type(commitd2)is not None:commit =3Dint(commitd2)
>   =20
> else:
>      commit =3D0
>=20
> 450992.10
>=20
> <class 'int'>
>=20
> If there is a better way to get to int I'd be all ears.
>=20
>=20
> Second, there=E2=80=99s the NoneType  (None,) result from queries with =
no values.
>=20
> cur.execute("SELECT SUM(revusd) FROM sfdc where saccount =3D 'Big=20
> Company' AND stage LIKE 'Win%';")
> wind1 =3D cur.fetchone()
> conn.commit()
> wind2 =3D wind1[0]
>=20
>=20
> if type(wind2)is int:win =3Dint(wind2)
>=20
> else:
>      win =3D0
>=20
> My goal is to return 0.00 when there are no results and an int when=20
> there are results using the same code. Right now my if statements are
> different:
>=20
> if type(commitd2)is not None:
>=20
> if type(wind2)is int:
>=20
> Possibly ignoring my fledgling attempts to solve this problem, is=20
> there a simple method by which people convert the:
>=20
> 1. (Decimal('450992.10'),) to a <class 'int'> 450992.10?
>=20
> 2. (None,) to 0.00?
>=20
>   Thanks for your thoughts on this question.

Just do it in the query:

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

If SUM(revusd) is NULL then COALESCE will substitute 0.


>=20
> Best,
>=20
> Hagen Finley
>=20
> Fort Collins, CO


--
Adrian Klaver
adrian.klaver@aklaver.com