Date: Wed, 14 Jul 2021 14:20:49 -0700 (PDT)
From: Rich Shepard <rshepard@appl-ecosys.com>
To: psycopg@postgresql.org
Subject: RE: New user questions
In-Reply-To: 
 <AM0PR07MB5700896B94BF1CBB6ED1325987139@AM0PR07MB5700.eurprd07.prod.outlook.com>
Message-ID: <alpine.LNX.2.20.2107141410560.28523@salmo.appl-ecosys.com>
References: <alpine.LNX.2.20.2107131228540.11509@salmo.appl-ecosys.com>
 <36d964b4-e04b-5fdf-9b21-d3bf80a3505d@aklaver.com>
 <alpine.LNX.2.20.2107131457030.11509@salmo.appl-ecosys.com>
 <9b477722-71af-abbb-8db7-1390a9906b07@aklaver.com>
 <alpine.LNX.2.20.2107141029300.12992@salmo.appl-ecosys.com>
 <AM0PR07MB5700896B94BF1CBB6ED1325987139@AM0PR07MB5700.eurprd07.prod.outlook.com>
User-Agent: Alpine 2.20 (LNX 67 2015-01-07)
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
Archived-At: 
 <https://www.postgresql.org/message-id/alpine.LNX.2.20.2107141410560.28523%40salmo.appl-ecosys.com>
Precedence: bulk

On Wed, 14 Jul 2021, David Raymond wrote:

> "The module contains objects and functions useful to generate SQL
> dynamically, in a convenient and safe way."

David,

There are many examples in the doc and I'm just learning which is
appropriate for each of my queries.

> If there's nothing dynamic about the text of the query, then you don't
> really need to go through all the trouble of using all those classes.
> They're there in case you're getting unknown table names from a user, or
> building a query on the fly, etc. If you know the query right now, you can
> just put it into a text string, and call it good.

No, nothing dynamic.

> So your query2 might look something like this:

That's closer to how the psql shell version looks. Revisions made.

> Note that I don't think this query of yours is gonna work as you've got a
> GROUP BY clause, and the SELECT list you have stuff that's not in the
> GROUP BY, and is not an aggregate.

Oops! Fixed it:

query = """select
       p.person_nbr, p.lname,p.fname,p.loc_nbr,p.job_title,p.direct_phone,p.active,
       c.org_name,
       l.loc_nbr,l.loc_name,
       a.act_date,a.act_type,a.notes,a.next_contact
       from
       people as p,
       inner join companies as c on c.org_nbr = p.org_nbr,
       inner join locations as l on l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr,
       inner join contacts as a on a.person_nbr = p.person_nbr
       where
       p.lname = (%s) and p.fname = (%s)
       group by p.person_nbr order by a.act_date;"""

cur.execute(query, (lname_value, fname_value))

Thanks,

Rich