Nested Looping SQL Querys
Fuzzydave
dav.phillips at ntlworld.com
Wed Sep 20 08:53:26 EDT 2006
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(pubID, productCode, description, suppNo1, all):
validQuery=0
if all:
all=int(all[0])
all = cromwell.toString(all)
sql='SELECT S.product_code, S.description, S.suppno1,
P.discount_factor, S.status, S.list_price, S.offer_price, P.page_no,
int8(P.oid), S.stock_qty '
sql=sql+'FROM (medusa.cmi_stockrec AS S INNER JOIN
medusa.cmi_auxstockrec AS A ON S.product_code=A.product_code) LEFT JOIN
medusa.cmi_pricing AS P ON S.product_code=P.product_code AND
P.pub_id='+pubID+' '
sql=sql+'WHERE '
if productCode!='':
sql=sql+cromwell.orSQL('S.product_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Product Code: <b>'+productCode+'</b></div>'
validQuery=1
if description!='':
sql=sql+' (upper(S.description) LIKE upper(\'%'+description+'%\'))
AND '
print '<div class="main">Description: <b>'+description+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno1) LIKE upper(\'%'+suppNo1+'%\')) AND '
print '<div class="main">Part 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.toString(all)
sql='SELECT Pl.product_code, S.description, S.suppno1,
P.discount_factor, 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_pricing AS Pl INNER JOIN
medusa.cmi_stockrec AS S ON S.product_code=Pl.product_code) INNER JOIN
medusa.cmi_auxstockrec AS A ON S.product_code=A.product_code) LEFT JOIN
medusa.cmi_pricing AS P ON S.product_code=P.product_code AND
P.pub_id='+pubID+' '
sql=sql+'WHERE Pl.pub_id='+pubList+' AND '
if productCode!='':
sql=sql+cromwell.orSQL('Pl.product_code', productCode, 'ILIKE \'',
'%\'', 1)+' AND '
print '<div class="main">Product Code: <b>'+productCode+'</b></div>'
validQuery=1
if description!='':
sql=sql+' (upper(S.description) LIKE upper(\'%'+description+'%\'))
AND '
print '<div class="main">Description: <b>'+description+'</b></div>'
validQuery=1
if suppNo1!='':
sql=sql+' (upper(S.suppno1) LIKE upper(\'%'+suppNo1+'%\')) AND '
print '<div class="main">Part No: <b>'+suppNo1+'</b></div>'
validQuery=1
if pubPageNo!='':
sql=sql+cromwell.orSQL('Pl.page_no', pubPageNo, '=\'', '\'', 1)+' AND
'
print '<div class="main">Publication 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_deliverydue "
sql=sql+'FROM charisma.sk_stklfl 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 at CROMWELL-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.FieldStorage()
# Start assigning submitted form fields to variables.
submit=cgiForm.getvalue('submit')
pubID=cgiForm.getvalue('pubID')
pubName=cgiForm.getvalue('pubName','Unknown Publication')
sqlcheck1 = "SELECT pub_type FROM medusa.cmi_publication WHERE pub_id =
'"+pubID+"'"
overseas1 = conn.query(sqlcheck1)
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=cromwell.unhypCode(cgiForm.getvalue('productCode2', ''))
description=cgiForm.getvalue('description2','')
suppNo1=cgiForm.getvalue('suppNo12', '')
pageNo=cgiForm.getvalue('pageNo2', '')
pubList=cgiForm.getvalue('pubList2', '800')
pubPageNo=cgiForm.getvalue('pubPageNo2', '')
all=cgiForm.getvalue('all2')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]
else:
productCode=cromwell.unhypCode(cgiForm.getvalue('productCode', ''))
description=cgiForm.getvalue('description','')
suppNo1=cgiForm.getvalue('suppNo1', '')
pageNo=cgiForm.getvalue('pageNo', '')
pubList=cgiForm.getvalue('pubList', '800')
pubPageNo=cgiForm.getvalue('pubPageNo', '')
all=cgiForm.getvalue('all')
if (all==None):
all=[]
elif not (type(all) is type([])):
all=[all]
# Return list of checked product codes.
codes=cgiForm.getvalue('codes')
if (codes==None):
codes=[]
elif not (type(codes) is type([])):
codes=[codes]
# Perform a SELECT query to produce publication list.
result = conn.query('SELECT 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((listPubID, 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.printCreateHeader(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=cgiForm.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==800):
sql=cmi.creationSQL(pubID, productCode, description, suppNo1, all)
else:
sql=cmi.creationPubSQL(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.printCreateKey()
# 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>Description</th>'
print '<th>Supp.<br>Part No.</th>'
print '<th>Charisma<br>List</th>'
#print '<th>Charisma<br>Offer</th>'
print '<th>Last Cat<br>Discount</th>'
print '<th>Page<br>No</th>'
if (pubListTmp!=800):
print '<th>Pub<br>Page</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=cromwell.notNone(row[2], '')
discount=row[3]
if discount==None:
discount='0'
else:
discount=cromwell.percentage(discount)
status=cromwell.notNone(row[4], '')
charList=cromwell.price(row[5], pubType)
charOffer=cromwell.price(row[6], pubType)
pageNo=cromwell.toString(row[7])
oid=cromwell.toString(row[8])
if (pubListTmp!=800):
pubPage=cromwell.toString(row[9])
stock=cromwell.toString(row[10])
else:
stock=cromwell.toString(row[9])
# Display a seperator between groups of product codes.
if lastGroup!=productCode[:6]:
if lastGroup!='':
print '<tr height=3><th colspan=8></th></tr>'
lastGroup=productCode[:6]
print '<tr>'
# Print a table row.
print '<td class='+cmi.pageStatusClass(pageNo, status)+'
align=left><a href="#"
onclick=\'javascript:window.open("http://ecatalogue.cromwell-tools.co.uk/details.php?product_code='+productCode+'&location=81","","scrollbars=yes,resizable=Yes,width=650,height=800")\'><b>'+cromwell.hypCode(productCode)+'</b></a></td>'
print '<td class='+cmi.pageStatusClass(pageNo, status)+'
align=left>'+status+'</td>'
print '<td class='+cmi.pageClass(pageNo)+' align=center><input
type=checkbox name=codes value='
print productCode+' '
if (cgiForm.getvalue('submit')=='All') or (productCode in codes):
print ' CHECKED',
print '></td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=left>'+description+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=left>'+suppNo1+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+charList+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+charOffer+'</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+discount+'%</td>'
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+pageNo+'</td>'
if (pubListTmp!=800):
print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+pubPage+'</td>'
###################################################
#
# This is the section that generates the second sql string
#
####################################################
#sqlS=cmi.stockdetailsSQL(productCode)
#print sqlS
#rowsS = cmi.fetch_rows(sqlS)
for rowS in rowsS:
# freestock=cromwell.toString(rowS[0])
# stkqweeks=cromwell.toString(rowS[1])
# allocated=cromwell.toString(rowS[2])
# stkorderq=cromwell.toString(rowS[3])
# orderdate=cromwell.toString(rowS[4])
# print ''+freestock+''
# print ''+stkqweeks+''
# print ''+allocated+''
# print ''+stkorderq+''
# print ''+orderdate+''
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+freestock+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+stkqweeks+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+allocated+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+stkorderq+'</td>'
#print '<td class='+cmi.pageClass(pageNo)+'
align=right>'+orderdate+'</td>'
print '</tr>'
matched=matched+1
print '</table>'
print '<p><b>'+`matched`+'</b> items found</p>'
# End left hand column.
print '</td>'
# Print spacer column.
print '<td class=clear width=50> </td>'
# Print right hand column.
print '<td class=clear valign=top>'
print '<b>Just added:</b><br>'
for code in codes:
print ' '*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>Creation Page</h1></td></tr></table>'
# Close HTML tags.
print '</form>'
print '</body>'
print '</html>'
More information about the Python-list
mailing list