By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,053 Members | 1,615 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,053 IT Pros & Developers. It's quick & easy.

Nested Looping SQL Querys

P: n/a
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
gi*****@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('p roductCode2', ''))
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('p roductCode', ''))
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,hei ght=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`+'</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>Creation Page</h1></td></tr></table>'

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

Sep 20 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Fuzzydave wrote:

(snip)
"""
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)
"""

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 'o****@xiludom.gro'.split('@')])"
Sep 20 '06 #2

P: n/a
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 'o****@xiludom.gro'.split('@')])"
Sep 20 '06 #3

P: n/a
Bruno Desthuilliers wrote:
Fuzzydave wrote:

(snip)
"""
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)
"""

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

Sep 20 '06 #4

P: n/a
Dennis Lee Bieber wrote:
[...]
# not conn.execute() ? That's what all the DB-API compliant adapters
use

result = conn.execute(sql, 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

Sep 20 '06 #5

P: n/a
On Thu, 2006-09-21 at 01:12, Dennis Lee Bieber wrote:
On Wed, 20 Sep 2006 13:21:54 -0400, Steve Holden <st***@holdenweb.com>
declaimed the following in comp.lang.python:
.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.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)
"""

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
Sep 21 '06 #6

P: n/a
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 <st***@holdenweb.com>
declaimed the following in comp.lang.python:

>>>.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.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)
"""

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

Sep 21 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.