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
Re: New user questions
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
Re: New user questions
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
Re: New user questions
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
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
cur.execute() syntax error
This MWE (test_combobox.py) produces a syntax error and I'm not seeing why:
-
import tkinter as tk
from tkinter import ttk
import psycopg2
class ContactDataForm(tk.Frame):
# set up postgres
# the connection
con = psycopg2.connect(database='bustrac')
# the cursor
cur = con.cursor()
def __init__(self, parent, *args, **kwargs):
super().__init__(parent, *args, **kwargs)
# A dict to keep track of input widgets
self.inputs = {}
self.inputs['Contact Type'] = LabelInput(
ContactDataForm, 'contact_type',
# query to fetch data from contacttypes table
fetch_all = "SELECT * from contacttypes"
cur.execute(fetch_all)
# fetching all rows
rows = cur.fetchall()
input_class=ttk.Combobox([values = rows])
input_var=tk.StringVar()
# get selected value and bind it to a method
cont_type = self.get() # selected value by mouse click
con.close()
)
self.inputs['Contact Type'].grid(row0, column1)
ContactDataForm.grid(row=0, column=0, sticky='we')
-
When run from the shell:
$ python test_combobox.py
File "test_combobox.py", line 24
cur.execute(fetch_all)
^
SyntaxError: invalid syntax
Here python is python3 by default.
What am I not seeing?
Rich
Re: cur.execute() syntax error [RESOLVED]
On Mon, 4 Apr 2022, Christophe Pettus wrote: It looks like the LabelInput constructor includes some code. Did you mean something like: self.inputs['Contact Type'] = LabelInput( ContactDataForm, 'contact_type',) # query to fetch data from contacttypes table fetch_all = "SELECT * from contacttypes" cur.execute(fetch_all) # fetching all rows rows = cur.fetchall() input_class=ttk.Combobox([values = rows]) input_var=tk.StringVar() # get selected value and bind it to a method cont_type = self.get() # selected value by mouse click con.close() Christophe, Sure looks like it. Thanks, Rich
Re: Psycopg 2.8.1, 2.8.2 released
On Sun, Apr 14, 2019 at 06:42:26PM +0100, Daniele Varrazzo wrote: We have just released Psycopg 2.8.2; a few days ago Psycopg 2.8.1 was released. Daniele, 2.8.2 is not yet available on the web site, only 2.8.1. I'll try later today. Regards, Rich
Re: Psycopg 2.8.1, 2.8.2 released
On Sun, 14 Apr 2019, Rich Shepard wrote: 2.8.2 is not yet available on the web site, only 2.8.1. I'll try later today. Later has arrived. :-) Thanks! Rich
