Complex SQL query works in Postgresql pgAdmin 4 but not Python

Question

This query:

    select product.width, product.height 
    from product 
    inner join product_template on product.prodtempindex = product_template.prodtempindex 
    inner join painting on painting.pntindex = product.pntindex 
    where painting.catalognumber = 'bg0025' and product.prodtempindex = 2

works in Postgresql pgAdmin4.

A simplified version of this query works in pgAdmin and Python:

cur.execute("select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = %s",[number])

'number' is a variable substitution for bg0025 above.

The problem is, I can't solve how to include the "and product.prodtempindex = 2" clause in the Python query. I get syntax errors or too many parameters.

import psycopg2
import csv

csv_file = "C:/BG/business/images/master_sizes.csv"
conn = psycopg2.connect(dbname="bgartwork", user="postgres", host="billgiacalone.com", password="Jg116162!")
cur = conn.cursor()

with open(csv_file,'r') as csvfile:
    imagesizes = csv.reader(csvfile)
    for row in imagesizes:
        number = row[0][0:6:1]
        dimension = row[1]
        inches = row[2]
   
        cur.execute("select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = %s",[number]) "and product.prodtempindex = 2"
        dbrow = cur.fetchall()
        for drow in dbrow:
            print(number,drow)

ERROR:
  File "C:\Users\xxx\product_gen\check_prod_size.py", line 19
    cur.execute("select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = %s",[number]) "and product.prodtempindex = 2"
                                                                                                                                                                                                                                                                  ^
SyntaxError: invalid syntax

Check out postgresql bind syntax. https://stackoverflow.com/a/1471178/1394353 You’re using wrong format and param syntax. A different database say mysql, might work with what you gave. Postgresql does not. — JL Peyret, Oct 03 '21 at 17:08
@JLPeyret. The format and syntax is correct, the issue is, I'm guessing, with the manner in which the OP is adding in "and product.prodtempindex = 2". — Adrian Klaver, Oct 03 '21 at 17:40
@AdrianKlaver Hmmm, I was going to say that you're incorrect, but I guess psycopg does support both positionals and named. Learned something. If only the OP would post their actual code and the error message... — JL Peyret, Oct 03 '21 at 18:07
Here is the code: cur.execute("select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = %s",[number]) "and product.prodtempindex = 2" dbrow = cur.fetchall() for drow in dbrow: print(number,drow) Error: syntax error — meejo57, Oct 04 '21 at 22:35

score 1 · Accepted Answer · answered Oct 03 '21 at 16:30

Per Parameters:

import psycopg2
con = psycopg2.connect(dbname="test", host='localhost', user='postgres')
cur = con.cursor()
number = 1

#mogrify returns an adapted query string. Used here to show that the
#query is correctly built. Substitute execute for actual usage.
cur.mogrify("select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = %s and product.prodtempindex = %s",[number, 2])

'select product.width, product.height from product inner join product_template on product.prodtempindex = product_template.prodtempindex inner join painting on painting.pntindex = product.pntindex where painting.catalognumber = 1 and product.prodtempindex = 2'

Can you upvote or accept the answer? – Adrian Klaver Oct 04 '21 at 23:51 — Adrian Klaver, Oct 04 '21 at 23:51

Complex SQL query works in Postgresql pgAdmin 4 but not Python

1 Answers1