CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
meejo57
meejo57

Reputation: 33

Complex SQL query works in Postgresql pgAdmin 4 but not Python

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

Upvotes: 1

Views: 661

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19570

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'

Upvotes: 1

Related Questions