复杂SQL查询可以在Postgresql pgAdmin 4中工作，但不能在Python中工作

Question

此查询：

    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

在Postgresql pgAdmin4中工作

此查询的简化版本适用于pgAdmin和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”是上述bg0025的变量替换

问题是，我无法解决如何在Python查询中包含“and product.prodtempindex=2”子句的问题。我得到语法错误或参数太多

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

Answer 1

每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'