Thread: New user questions
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
On 7/13/21 1:09 PM, Rich Shepard wrote: > 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? 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. > > 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? There are several ways to read the above. Send some examples of what you want to do. > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 13 Jul 2021, Adrian Klaver wrote: > 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. Adrian, That makes sense: the statements are treated as comments. I hadn't thought of them that way. >> 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? > > There are several ways to read the above. > 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
On 7/13/21 3:02 PM, Rich Shepard wrote: > On Tue, 13 Jul 2021, Adrian Klaver wrote: > >> 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. > > Adrian, > > That makes sense: the statements are treated as comments. I hadn't thought > of them that way. 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. >> There are several ways to read the above. >> 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. 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 > > Thanks, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On Tue, 13 Jul 2021, Adrian Klaver wrote: > Then you want to take a look at this: > https://www.psycopg.org/docs/sql.html 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)) > 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 I'll read this again in close detail after I understand how to write insert and select queries. Regards, Rich
On 7/14/21 10:35 AM, Rich Shepard wrote: > On Tue, 13 Jul 2021, Adrian Klaver wrote: > >> Then you want to take a look at this: >> https://www.psycopg.org/docs/sql.html > > 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])) 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. > > 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)) 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' """ > >> 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 >> > > I'll read this again in close detail after I understand how to write insert > and select queries. > > Regards, > > Rich > > -- Adrian Klaver adrian.klaver@aklaver.com
On 7/14/21 10:57 AM, Adrian Klaver wrote:
> On 7/14/21 10:35 AM, Rich Shepard wrote:
> 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'
> """
That should have been:
st.fld_1 = %s
--
Adrian Klaver
adrian.klaver@aklaver.com
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 usingall those classes. They're there in case you're getting unknown table names from a user, or building a query on thefly, 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 stuffthat's not in the GROUP BY, and is not an aggregate.
On Wed, 14 Jul 2021, Adrian Klaver wrote: > Where are c.nbr and a.* coming from? If they are supposed to be table > qualified values this will not work. Adrian, From the psql shell version. > Also it is sql.Identifier. Thanks; I certainly missed that. > 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' > """ Got it, thanks. Sorting out the proper format for my queries will take a bit of time. You've helped shorten the time frame. Regards, Rich
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