RE: New user questions

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