Nested Looping SQL Querys

[Fuzzydave]

I am back developing futher our Python/CGI based web application run by
a Postgres DB
and as per usual I am having some issues. It Involves a lot of Legacy
code. All the actual
SQL Querys are stored in the .py files and run in the .cgi files. I
have the problem that I
need to construct a row from two seprate SQL Querys, I have tried
combining the two
Querys but all that does is create a Query that returns nothing after a
long period running.

the first query results are delimited with [] and the second with {} I
want my result to
return [ val1 ] [ val2 ] [ val3 ] [ val4 ] { valA } { valB }
unfortunatly when i put my second
query in anywhere on the page its crashes and returns a Internal Server
Error.

the functions from the cmi file are below.



def creationSQL(pub ID, productCode, description, suppNo1, all):

validQuery=0

if all:
all=int(all[0])
all = cromwell.toStri ng(all)


sql='SELECT S.product_code, S.description, S.suppno1,
P.discount_fact or, S.status, S.list_price, S.offer_price, P.page_no,
int8(P.oid), S.stock_qty '
sql=sql+'FROM (medusa.cmi_sto ckrec AS S INNER JOIN
medusa.cmi_auxs tockrec AS A ON S.product_code= A.product_code) LEFT JOIN
medusa.cmi_pric ing AS P ON S.product_code= P.product_code AND
P.pub_id='+pubI D+' '
sql=sql+'WHERE '


if productCode!='' :
sql=sql+cromwel l.orSQL('S.prod uct_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Pr oduct Code: <b>'+productCod e+'</b></div>'
validQuery=1
if description!='' :
sql=sql+' (upper(S.descri ption) LIKE upper(\'%'+desc ription+'%\'))
AND '
print '<div class="main">De scription: <b>'+descriptio n+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno 1) LIKE upper(\'%'+supp No1+'%\')) AND '
print '<div class="main">Pa rt No: <b>'+suppNo1+ '</b></div>'
validQuery=1
if all!=pubID:
sql=sql+' (P.product_code IS NULL) AND '

sql=sql[:-4]
sql=sql+' ORDER BY S.product_code'

print '<!-- SQL (Publication Creation):\n'
print sql
print '-->'

if validQuery==1:
return sql
else:
return ''

def creationPubSQL( pubID, productCode, description, suppNo1, all,
pubList, pubPageNo):

validQuery=0

if all:
all=int(all[0])
all = cromwell.toStri ng(all)


sql='SELECT Pl.product_code , S.description, S.suppno1,
P.discount_fact or, S.status, Pl.list_price, Pl.offer_price, P.page_no,
int8(P.oid), Pl.page_no, S.stock_qty '
sql=sql+'FROM ((medusa.cmi_pr icing AS Pl INNER JOIN
medusa.cmi_stoc krec AS S ON S.product_code= Pl.product_code ) INNER JOIN
medusa.cmi_auxs tockrec AS A ON S.product_code= A.product_code) LEFT JOIN
medusa.cmi_pric ing AS P ON S.product_code= P.product_code AND
P.pub_id='+pubI D+' '
sql=sql+'WHERE Pl.pub_id='+pub List+' AND '

if productCode!='' :
sql=sql+cromwel l.orSQL('Pl.pro duct_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Pr oduct Code: <b>'+productCod e+'</b></div>'
validQuery=1
if description!='' :
sql=sql+' (upper(S.descri ption) LIKE upper(\'%'+desc ription+'%\'))
AND '
print '<div class="main">De scription: <b>'+descriptio n+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno 1) LIKE upper(\'%'+supp No1+'%\')) AND '
print '<div class="main">Pa rt No: <b>'+suppNo1+ '</b></div>'
validQuery=1
if pubPageNo!='':
sql=sql+cromwel l.orSQL('Pl.pag e_no', pubPageNo, '=\'', '\'', 1)+' AND
'
print '<div class="main">Pu blication Page No:
<b>'+pubPageNo+ '</b></div>'
validQuery=1
if all!=pubID:
sql=sql+' (P.product_code IS NULL) AND '

sql=sql[:-4]
sql=sql+' ORDER BY Pl.product_code '

print '<!-- SQL (Publication Creation):\n'
print sql
print '-->'

if validQuery==1:
return sql
else:
return ''


def stockdetailsSQL (productCode):

validQuery=0

sql="SELECT (stkphys - stkalloc) as free_stock, stk_qty_wk, stkalloc,
stkordq, r.fd_deliverydu e "
sql=sql+'FROM charisma.sk_stk lfl LEFT JOIN
progress.report _firstdelivery as r ON stkl_stockno = r.fd_sordstk '
sql=sql+'WHERE stkl_stockno = \''+productCode +'\' AND stkl_location =
\'081\' ORDER BY stkl_stockno'
validQuery=1

sql=sql[:-4]

print '<!-- SQL (stock details):\n'
print sql
print '-->'


if validQuery==1:
return sql
else:
return ''


The page code for the CGI file that genereates the tables

#!/usr/bin/python

# Creation Screen
# MeDuSa - Marketing Data System

# $Id: creation.cgi 54 2006-02-16 11:32:12Z
gibsonm@CROMWEL L-TOOLS.CO.UK $


print 'Content-Type: text/html\n\n'


import sys
sys.stderr = sys.stdout

from pyPgSQL import libpq
import cgi
import string
import os
import cmi
import cromwell

import hermes
conn = hermes.db()


# This will allow us to retrieve submitted form fields.
cgiForm=cgi.Fie ldStorage()



# Start assigning submitted form fields to variables.
submit=cgiForm. getvalue('submi t')
pubID=cgiForm.g etvalue('pubID' )
pubName=cgiForm .getvalue('pubN ame','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publ ication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlc heck1)
pubType = cmi.fetch_rows( overseas1)

print pubType

# Check to find out which Search button was pressed ('Search' or
'Search ' )
# before assigning submitted form fields to variables.
if submit=='Search ':
productCode=cro mwell.unhypCode (cgiForm.getval ue('productCode 2', ''))
description=cgi Form.getvalue(' description2',' ')
suppNo1=cgiForm .getvalue('supp No12', '')
pageNo=cgiForm. getvalue('pageN o2', '')
pubList=cgiForm .getvalue('pubL ist2', '800')
pubPageNo=cgiFo rm.getvalue('pu bPageNo2', '')

all=cgiForm.get value('all2')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]

else:
productCode=cro mwell.unhypCode (cgiForm.getval ue('productCode ', ''))
description=cgi Form.getvalue(' description','' )
suppNo1=cgiForm .getvalue('supp No1', '')
pageNo=cgiForm. getvalue('pageN o', '')
pubList=cgiForm .getvalue('pubL ist', '800')
pubPageNo=cgiFo rm.getvalue('pu bPageNo', '')

all=cgiForm.get value('all')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]



# Return list of checked product codes.
codes=cgiForm.g etvalue('codes' )
if (codes==None):
codes=[]
elif not (type(codes) is type([])):
codes=[codes]




# Perform a SELECT query to produce publication list.
result = conn.query('SEL ECT pub_name, pub_status, pub_id, pub_type FROM
cmi_publication WHERE (pub_status < 4) AND (pub_id 0) ORDER BY
pub_status, pub_type, pub_name')
rows = cmi.fetch_rows( result)

pubs=[(800, 'Charisma')]


# Create a publication list array.
for row in rows:
listPubName=row[0]
listPubID=row[2]

pubs.append((li stPubID, listPubName))



# Start printing the HTML page.
print '<html>'
print '<head>'
print '<title>MEDUSA </title>'
print '<LINK REL ="stylesheet " TYPE="text/css"
HREF="/styles/medusa.css" TITLE="Style">'
print '</head>'

print '<body link="#000080" alink="#000080" vlink="#000080"
topmargin=0>'
print '<form method=post>'



# Produce the search form at the top of the page and the publication
title below.
banner=cmi.prin tCreateHeader(1 , 'creation', pubID, ['ProductCode',
'Description', 'PartNo', 'All'], pubName, productCode, description,
suppNo1, pageNo, pubList, all, pubs, pubPageNo)


# If a button other than Search was pressed then perform the related
query.
add_page=cgiFor m.getvalue('add _page')
if (submit=='Add') or (add_page):

for row in codes:
list_code=row[0:11]
list_code = "\'" + list_code + "\'"

sql=cmi.addSQL( pubID, list_code, add_page)
if sql:
conn.query(sql)



pubListTmp = int(pubList)


# Perform the SELECT query to produce the page content based on whether
a publication has been selected or not.
if (pubListTmp==80 0):
sql=cmi.creatio nSQL(pubID, productCode, description, suppNo1, all)
else:
sql=cmi.creatio nPubSQL(pubID, productCode, description, suppNo1, all,
pubList, pubPageNo)



print '</p>'

# If a valid SELECT query has been created then display the results.
if sql:

# Execute the SELECT query.
result = conn.query(sql)
rows = cmi.fetch_rows( result)


# If the query has returned any results.
if rows:

# Create Edit form elements.
print '<p>Page No. <input type=text name=add_page size=5<input
type=submit name=submit value=Add><hr></p>'


# Print key.
cmi.printCreate Key()


# Print structure table.
print '<table class=clear>'
print '<tr>'



# Print left hand column.
print '<td class=clear>'

print '<table cellpadding=3 cellspacing=1>'
print '<tr>'

# Print the table headers.
print '<th>Product<br >Code</th>'
print '<th>S</th>'
print '<th><input type=submit name=submit value=All ></th>'
print '<th>Descriptio n</th>'
print '<th>Supp.<br>P art No.</th>'
print '<th>Charisma<b r>List</th>'
#print '<th>Charisma<b r>Offer</th>'
print '<th>Last Cat<br>Discount </th>'
print '<th>Page<br>No </th>'
if (pubListTmp!=80 0):
print '<th>Pub<br>Pag e</th>'
print '<th>Stock Qty<br>Loc 81</th>'

print '</tr>'

matched=0
lastGroup=''


# Loop to print one line for each return from the database.
for row in rows:


# Assign the column values to named variables.
productCode=row[0]
description=row[1]
suppNo1=cromwel l.notNone(row[2], '')
discount=row[3]
if discount==None:
discount='0'
else:
discount=cromwe ll.percentage(d iscount)
status=cromwell .notNone(row[4], '')
charList=cromwe ll.price(row[5], pubType)
charOffer=cromw ell.price(row[6], pubType)
pageNo=cromwell .toString(row[7])
oid=cromwell.to String(row[8])
if (pubListTmp!=80 0):
pubPage=cromwel l.toString(row[9])
stock=cromwell. toString(row[10])
else:
stock=cromwell. toString(row[9])



# Display a seperator between groups of product codes.
if lastGroup!=prod uctCode[:6]:
if lastGroup!='':
print '<tr height=3><th colspan=8></th></tr>'
lastGroup=produ ctCode[:6]



print '<tr>'

# Print a table row.
print '<td class='+cmi.pag eStatusClass(pa geNo, status)+'
align=left><a href="#"
onclick=\'javas cript:window.op en("http://ecatalogue.crom well-tools.co.uk/details.php?pro duct_code='+pro ductCode+'&loca tion=81","","sc rollbars=yes,re sizable=Yes,wid th=650,height=8 00")\'><b>'+cro mwell.hypCode(p roductCode)+'</b></a></td>'
print '<td class='+cmi.pag eStatusClass(pa geNo, status)+'
align=left>'+st atus+'</td>'
print '<td class='+cmi.pag eClass(pageNo)+ ' align=center><i nput
type=checkbox name=codes value='
print productCode+' '
if (cgiForm.getval ue('submit')==' All') or (productCode in codes):
print ' CHECKED',
print '></td>'
print '<td class='+cmi.pag eClass(pageNo)+ '
align=left>'+de scription+'</td>'
print '<td class='+cmi.pag eClass(pageNo)+ '
align=left>'+su ppNo1+'</td>'
print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+c harList+'</td>'
#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+c harOffer+'</td>'
print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+d iscount+'%</td>'
print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+p ageNo+'</td>'
if (pubListTmp!=80 0):
print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+p ubPage+'</td>'

############### ############### ############### ######
#
# This is the section that generates the second sql string
#
############### ############### ############### #######


#sqlS=cmi.stock detailsSQL(prod uctCode)
#print sqlS
#rowsS = cmi.fetch_rows( sqlS)
for rowS in rowsS:
# freestock=cromw ell.toString(ro wS[0])
# stkqweeks=cromw ell.toString(ro wS[1])
# allocated=cromw ell.toString(ro wS[2])
# stkorderq=cromw ell.toString(ro wS[3])
# orderdate=cromw ell.toString(ro wS[4])

# print ''+freestock+''
# print ''+stkqweeks+''
# print ''+allocated+''
# print ''+stkorderq+''
# print ''+orderdate+''

#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+f reestock+'</td>'
#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+s tkqweeks+'</td>'
#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+a llocated+'</td>'
#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+s tkorderq+'</td>'
#print '<td class='+cmi.pag eClass(pageNo)+ '
align=right>'+o rderdate+'</td>'

print '</tr>'

matched=matched +1

print '</table>'

print '<p><b>'+`match ed`+'</bitems found</p>'


# End left hand column.
print '</td>'


# Print spacer column.
print '<td class=clear width=50>&nbsp</td>'


# Print right hand column.
print '<td class=clear valign=top>'

print '<b>Just added:</b><br>'
for code in codes:
print '&nbsp'*5, code, '<br>'

# End right hand column.
print '</td>'
print '</tr>'



# Close the table.
print '</table>'



# If no results are returned by the query.
else:
print '<p align="center"> <font color=red><b>No items
found</b></font></p>'




# If a valid query has not been created (No search details entered).
else:

print '<table width="100%" height="500"
class="clear">< tr><td><h1>Crea tion Page</h1></td></tr></table>'



# Close HTML tags.
print '</form>'
print '</body>'
print '</html>'

[Bruno Desthuilliers]

Re: Nested Looping SQL Querys

Fuzzydave wrote:

(snip)
"""
pubID=cgiForm.g etvalue('pubID' )
pubName=cgiForm .getvalue('pubN ame','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publ ication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlc heck1)
pubType = cmi.fetch_rows( overseas1)
"""

May we have the url where we can see this application in action ? I know
some crackers that would be really pleased to mess with your production
database...


--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'onurb@xiludom. gro'.split('@')])"

[Bruno Desthuilliers]

Re: Nested Looping SQL Querys

Fuzzydave wrote:

I am back developing futher our Python/CGI based web application run by
a Postgres DB
and as per usual I am having some issues. It Involves a lot of Legacy
code.

s/Legacy/Norwegian Blue/


--
bruno desthuilliers
python -c "print '@'.join(['.'.join([w[::-1] for w in p.split('.')]) for
p in 'onurb@xiludom. gro'.split('@')])"

[Steve Holden]

Re: Nested Looping SQL Querys

Bruno Desthuilliers wrote:

Fuzzydave wrote:
>
(snip)
"""
pubID=cgiForm.g etvalue('pubID' )
pubName=cgiForm .getvalue('pubN ame','Unknown Publication')
>
sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publ ication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlc heck1)
pubType = cmi.fetch_rows( overseas1)
"""
>
May we have the url where we can see this application in action ? I know
some crackers that would be really pleased to mess with your production
database...
>
>

In case Bruno's posting didn't make it obvious, your code is wide open
to SQL injection exploits. Google is your friend.

The correct way to remove such vulnerabilities is to use parameterized
queries, giving the parameters as a tuple second argument to
cursor.execute( ).

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Steve Holden]

Re: Nested Looping SQL Querys

Dennis Lee Bieber wrote:
[...]

# not conn.execute() ? That's what all the DB-API compliant adapters
use
>
result = conn.execute(sq l, params)
>

..execute() is a cursor method, not a connection method. Some DB API
modules do implement it as a connection method, but that makes it
impossible for several cursors to share the same connection (which is
allowed by some modules).

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden

[Carsten Haese]

Re: Nested Looping SQL Querys

On Thu, 2006-09-21 at 01:12, Dennis Lee Bieber wrote:

On Wed, 20 Sep 2006 13:21:54 -0400, Steve Holden <steve@holdenwe b.com>
declaimed the following in comp.lang.pytho n:
>

.execute() is a cursor method, not a connection method. Some DB API
modules do implement it as a connection method, but that makes it
impossible for several cursors to share the same connection (which is
allowed by some modules).

It struck me that the original wasn't using a cursor just after the
messages posted.

It doesn't look like the OP is using anything even remotely DB-API
compliant:

"""
import cmi
<snip>
import hermes
conn = hermes.db()
<snip>

pubID=cgiForm.g etvalue('pubID' )
pubName=cgiForm .getvalue('pubN ame','Unknown Publication')

sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publ ication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlc heck1)
pubType = cmi.fetch_rows( overseas1)
"""

hermes is apparently some wrapper that magically can connect to a
well-known database (via the argumentless db()) call. Who knows what
kind of animal the 'conn' object is that comes out of that db() call.
Apparently it's an object with a query() method that returns something
like a cursor that can be passed into cmi.fetch_rows( ). At this point I
wonder why the responsibility of fetching rows is in a module separate
from the responsibility of establishing a database connection.

Legacy code, indeed.

-Carsten

[Steve Holden]

Re: Nested Looping SQL Querys

Carsten Haese wrote:

On Thu, 2006-09-21 at 01:12, Dennis Lee Bieber wrote:
>

>>On Wed, 20 Sep 2006 13:21:54 -0400, Steve Holden <steve@holdenwe b.com>
>>declaimed the following in comp.lang.pytho n:
>>
>>

>>>.execute() is a cursor method, not a connection method. Some DB API
>>>modules do implement it as a connection method, but that makes it
>>>impossible for several cursors to share the same connection (which is
>>>allowed by some modules).
>>>

>>
>> It struck me that the original wasn't using a cursor just after the
>>messages posted.

>
>
It doesn't look like the OP is using anything even remotely DB-API
compliant:
>
"""
import cmi
<snip>
import hermes
conn = hermes.db()
<snip>
>
pubID=cgiForm.g etvalue('pubID' )
pubName=cgiForm .getvalue('pubN ame','Unknown Publication')
>
sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publ ication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlc heck1)
pubType = cmi.fetch_rows( overseas1)
"""
>
hermes is apparently some wrapper that magically can connect to a
well-known database (via the argumentless db()) call. Who knows what
kind of animal the 'conn' object is that comes out of that db() call.
Apparently it's an object with a query() method that returns something
like a cursor that can be passed into cmi.fetch_rows( ). At this point I
wonder why the responsibility of fetching rows is in a module separate
from the responsibility of establishing a database connection.
>
Legacy code, indeed.
>

Dog's breakfast, more like. I'm not surprised the OP is having trouble
refactoring the database govne the code that accesses it at the moment.

regards
Steve
--
Steve Holden +44 150 684 7255 +1 800 494 3119
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://holdenweb.blogspot.com
Recent Ramblings http://del.icio.us/steve.holden