Subject: New user questions

From: Rich Shepard <[email-masked]>
Date: Tue, 13 Jul 2021 13:09:24 -0700 (PDT)

I'm starting to learn psycopg2 and have read the basic module usage doc. Two
questions on syntax:

1) I've seen a number of psycopg2 cursor execute examples where the select
or insert statement is enclosed in a pydoc pair of three double quotes. Most
examples in the basic module usage examples have a single pair of double
quotes enclosing the statement; the named variable examples are exceptions.
Is there a general rule when to use triple double quotes and when to use
single double quotes?

2) How do I write a cursor execute UPDATE statement where the columns and
values to be updated can be single or multiple and the values passed into
the method from the tkinter widgets? Are there examples from which I can
learn?

Regards,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 13 Jul 2021 14:51:46 -0700

> Is there a general rule when to use triple double quotes and when to use

Triple quoting is used when the statement extends across more then one 
line. Pair of double quotes is used for single line statement, 
especially when there are single quoted values in the statement.

> the method from the tkinter widgets? Are there examples from which I can

There are several ways to read the above.

Send some examples of what you want to do.

-- 
Adrian Klaver
[email-masked]

---

From: Rich Shepard <[email-masked]>
Date: Tue, 13 Jul 2021 15:02:43 -0700 (PDT)

On Tue, 13 Jul 2021, Adrian Klaver wrote:

> line. Pair of double quotes is used for single line statement, especially
> when there are single quoted values in the statement.

Adrian,

That makes sense: the statements are treated as comments. I hadn't thought
of them that way.

> Send some examples of what you want to do.

Here's a template:

update contacts
set 
where
;

The contacts table has these attributes:
person_nbr   | integer
act_date     | date
act_type     | character varying(12)
notes        | text
next_contact | date

Could be a changed next_contact date, or notes, or both.

Other tables need updating; for example, a new job title for a person or a
new web site for a company.

Thanks,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 13 Jul 2021 15:23:11 -0700

> That makes sense: the statements are treated as comments. I hadn't thought

To be clear triple quoting is just one of the ways you can quote things 
in Python. When it is done in a specific location in a function, class 
or class method then it becomes a docstring(comment). It does not turn 
other things into a comment.

> set where

Then you want to take a look at this:

https://www.psycopg.org/docs/sql.html

For an UPDATE example see my answer to this Stack Overflow question:

https://stackoverflow.com/questions/68321746/how-to-create-a-dynamic-update-query-in-sql-using-python/68323019#comment120800261_68323019

---

From: Rich Shepard <[email-masked]>
Date: Wed, 14 Jul 2021 10:35:54 -0700 (PDT)

Adrian,

Based on pages 1-2 of that doc I've re-written one each of an insert and
select query. When they are correct I'll do the rest of the insert and
select queries.

Query 1:
cur.execute(
     sql.SQL("insert into {} values (%s, %s, %s, %s, %s)"
         .format(sql.identifier('contacts')),
         [c.nbr, a.date, a.type, a.notes, a.next]))

Query 2:
query = sql.SQL("select {fields} from {tables}").format(
       fields = sql.SQL(',').join([
       sql.identifier('p.lname'),
       sql.identifier('p.fname'),
       sql.identifier('p.loc_nbr'),
       sql.identifier('p.job_title'),
       sql.identifier('p.direct_phone'),
       sql.identifier('p.active'),
       sql.identifier('c.org_name'),
       sql.identifier('l.loc_nbr'),
       sql.identifier('l.loc_name'),
       sql.identifier('a.act_date'),
       sql.identifier('a.act_type'),
       sql.identifier('a.notes'),
       sql.identifier('a.next_contact'),
       ])
       tables = sql.SQL(',').join([
       sql.identifier('people as p'),
       sql.identifier('companies as c on c.org_nbr = p.org_nbr'),
       sql.identifier('locations as l on l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr'),
       sql.identifier('contacts as a on a.person_nbr = p.person_nbr'),
       ])
       where p.lname = (%s) and p.fname = (%s)
       group by a.act_date order by a.act_date", (lname,fname))

> https://stackoverflow.com/questions/68321746/how-to-create-a-dynamic-update-query-in-sql-using-python/68323019#comment120800261_68323019

I'll read this again in close detail after I understand how to write insert
and select queries.

Regards,

Rich

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 14 Jul 2021 10:57:47 -0700

>          [c.nbr, a.date, a.type, a.notes, a.next]))

Where are c.nbr and a.* coming from? If they are supposed to be table 
qualified values this will not work.

Also it is sql.Identifier.

>        group by a.act_date order by a.act_date", (lname,fname))

This will not work, if for no other reason that 'companies as c on 
c.org_nbr = p.org_nbr', etc are not identifiers and it is sql.Identifier.

Also it would be simpler for this case just to create a quoted 
statement, something like:
"""SELECT
    fld_1, fld_2, fld_3
FROM
    some_table AS st
JOIN
    another_table AS a
ON
     st.id = a.a_id
WHERE
     st.fld_1 = 'something'
"""

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 14 Jul 2021 11:01:56 -0700

> statement, something like:

That should have been:

    st.fld_1 = %s

---

From: David Raymond <[email-masked]>
Date: Wed, 14 Jul 2021 19:09:04 +0000

From https://www.psycopg.org/docs/sql.html

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

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.

So your query2 might look something like this:

query = """select
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 a.act_date order by a.act_date;"""

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

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.

---

From: Rich Shepard <[email-masked]>
Date: Wed, 14 Jul 2021 14:10:46 -0700 (PDT)

Adrian,

---

From: Rich Shepard <[email-masked]>
Date: Wed, 14 Jul 2021 14:20:49 -0700 (PDT)

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;"""

Thanks,

Rich