473,216 Members | 2,215 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 software developers and data experts.

Nested Looping SQL Querys

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
6 2458
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: chad | last post by:
I am writing a program to do some reliability calculations that require several nested for-loops. However, I believe that as the models become more complex, the number of required for-loops will...
2
by: Brian | last post by:
Hello I am using version 4.0.12-nt of MySQL and when I hit the enter key rapidly I can't connect to the database. The result is a message is returned to me from mysql that says I can't connect...
7
by: Alfonso Morra | last post by:
I have a class that contains a nested class. The outer class is called outer, and the nested class is called inner. When I try to compile the following code, I get a number of errors. It is not...
2
by: tony collier | last post by:
Hi i want to get a user to input x which then creates an nested loop with x levels. each level of the loop has the same number of iterations e.g. for the case where x=3, the following code is...
0
by: Andrew Warren | last post by:
I have a need for a non-recursive technique to determine whether a user is a member of a specified group or any of its nested sub-groups I have read some of the posts regarding enumerating members...
77
by: Peter Olcott | last post by:
http://www.tommti-systems.de/go.html?http://www.tommti-systems.de/main-Dateien/reviews/languages/benchmarks.html The above link shows that C# is 450% slower on something as simple as a nested loop....
3
by: Chifo | last post by:
hello. i have a problem with a populate html table with data from table here it's the problem two querys retrieving data from table, one of querys show me a colletion of data from 6:00 am to...
5
by: BMeyer | last post by:
I have been losing my mind trying to parse an XML document (with nested child elements, not all of which appear in each parent node) into a DataGrid object. What I want to do is "flatten" the XML...
11
by: boker | last post by:
i try to do nested looping like this for (i=0;i<=640;i++) { for(j=0;j<=640;j++) { if(getpixel(j,i)!=0) { printf("this a color pixel); } }
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.