Another Screwy Problem

[Victor Subervi]

Hi;
I have this line of code:
 sql = 'select Name, Price from %sPackages where ID=%s;' % (store, pid)
which prints to this:
 select Name, Price from productsPackages where ID=1;
which when I enter it into the MySQL interpreter gives me this:
mysql> select Name, Price from productsPackages where ID=1;
+------+--------+
| Name | Price  |
+------+--------+
| pkg  | 123.45 |
+------+--------+
1 row in set (0.00 sec)

exactly what I expect. However, in my script for some reason it returns
this:
((1,),)
Why would it do that? I guess there's some foolish thing I did in my code
somewhere, but I'll be darned if I know where. Here's the whole script:

#! /usr/bin/python

import cgitb; cgitb.enable()
import cgi
import MySQLdb
import sys,os
sys.path.append(os.getcwd())
from login import login
from template import top, bottom
from sets import Set
import fpformat

form = cgi.FieldStorage()
store = form.getfirst('store')
cat = form.getfirst('cat', '')

user, passwd, db, host = login()
db = MySQLdb.connect(host, user, passwd, db)
cursor = db.cursor()

def displayProducts(patientID=''):
  try: # These are stores with categories where ordering by price is
important
    sql = 'select ID from %s where Category="%s" order by Price desc;' %
(store, cat)
    cursor.execute(sql)
  except: # Stores, like prescriptions, where ordering by price is not
important
    sql = 'select ID from %s;' % (store)
    cursor.execute(sql)
  ids = [itm[0] for itm in cursor]
  cursor.execute('describe %s;' % store)
  colFields, colFieldValues = [itm[0] for itm in cursor], [itm[1] for itm in
cursor]
  i = 0
  if len(ids) > 0:
    for id in ids:
#      print '<tr>\n'
      print '<form method="post" action="displayOneProduct.py">'
      print "<input type='hidden' name='store' value='%s' />" % store
      print "<input type='hidden' name='id' value='%s' />" % id
      j = 0
      for col in colFields:
        sql = 'select %s from %s where ID="%s";' % (col, store, str(id))
        cursor.execute(sql)
        colValue = cursor.fetchone()
        if col == 'SKU':
          print "<input type='hidden' name='sku' value='%s' />" %
colValue[0]
          print '<b>%s: </b>%s<br />\n' % (col, colValue[0])
        elif col == 'Category':
          print "<input type='hidden' name='cat' value='%s' />" %
colValue[0]
          print '<b>%s: </b>%s<br />\n' % (col, colValue[0])
        elif col == 'OutOfStock':
          if colValue[0] == '1': # This product is out of stock
            outOfStockFlag = 'yes'
          else:
            outOfStockFlag = 'no'
        elif col[:3] != 'pic':
          notSet = 1
          if isinstance(colValue[0], (str, int, float, long, complex,
unicode, list, buffer, xrange, tuple)):
            pass
          else:
            try:
              html = "<b>%s</b>: <select name='%s'>" % (col, col)
              notSet = 0
              for itm in colValue[0]:
                try:
                  color, number = itm.split('$')
                  html += "<option name='%s'>%s</option>" % (itm, color)
                except:
                  html += "<option name='%s'>%s</option>" % (itm, itm)
              html += "</select><br />"
              print html
            except:
              pass
          if notSet == 1:
            if len(col) > 49:
              colX = col[:50] + '...'
            else:
               colX = col
            print '<b>%s: </b>%s<br />\n' % (colX, colValue[0])
        elif col == 'pic1':
          try:
            if (colValue[0] != None):
#            if (colValue[0] != None) and (len(colValue[0] > 0)):
              print '<a href="getpic.py?store=%s&pic=%s&id=%s"
class="highslide" href="getpic.py?store=%s&pic=%s&id=%s" onclick="return
hs.expand(this)"><img src="getpic.py?store=%s&pic=%s&id=%s" width="100"
height="80" alt="" align="left" border="0" title="Click to enlarge"
style="border: 0px"></a><br clear="all" />\n' % (store, col[3:], id, store,
col[3:], id, store, col[3:], id)
          except TypeError:
            raise
          except:
            raise
#          i += 1
#          try:
#            content = colValues[0][x].tostring()
#            pic = "tmp" + str(i) + ".jpg"
#            try:
#              os.remove(pic)
#            except:
#              pass
#            img = open(pic, "w")
#            img.write(content)
#            print '<img src="%s"><br /><br />' % pic
#            img.close()
#          except:
#            pass
        j += 1
      if store != 'prescriptions':
        if outOfStockFlag == 'yes':
          print '<font color="red">This item is currently <b>out of
stock</b>.</font>'
        else:
          print "<input type='submit' value=' More Info ' />"
      else:
        print "<input type='hidden' name='patientID' value='%s' />" %
patientID
        print "<input type='submit' value=' More Info ' />"
      print '</form><br /><br />'
    print '</td></tr></table>\n'
    print '</table>\n'

def cgiFieldStorageToDict(fieldStorage):
  params = {}
  for key in fieldStorage.keys():
    params[key] = fieldStorage[key].value
  return params

def display():
  top()
  dict = cgiFieldStorageToDict(cgi.FieldStorage())
  print dict
  if store == 'prescriptions':
    password = form.getfirst('password')
    email = form.getfirst('email')
    sql = 'select ID, FirstName, LastName from %s where PW="%s" and
Email="%s";' % ('patientsPersonalData', password, email)
    try:
      cursor.execute(sql)
      patientID = [itm[0] for itm in cursor][0]
      print "<h2>Welcome, %s %s!</h2>" % ([itm[1] for itm in cursor][0],
[itm[2] for itm in cursor][0])
      displayProducts(patientID)
    except:
      print "We're sorry. The email address and password you entered do not
correspond with what is recorded in our database. Please click the
"back" button and try again.<br /><br />"
  else:
    displayProducts()
    cursor.execute('show tables like "%sPackages";' % store)
    if cursor.fetchone() is not None:
      sql = 'select ID from categories%s where Category="%s";' %
(store[0].upper() + store[1:], cat)
      cursor.execute(sql)
      categoryID = cursor.fetchone()[0]
      sql = 'select p.ID from %sPackages p join %sCategoriesPackages c where
c.CategoryID=%s;' % (store, store, categoryID)
      cursor.execute(sql)
      packageIDs = [itm[0] for itm in cursor]
      for pid in packageIDs:
        sql = 'select ProductID from %sProductsPackages where PackageID=%s;'
% (store, pid)
        cursor.execute(sql)
        productIDs = [itm[0] for itm in cursor]
        sql = 'select Name, Price from %sPackages where ID=%s;' % (store,
pid)
#        print sql
        print cursor.fetchall()
        name = [itm[0] for itm in cursor]
        price = [itm[1] for itm in cursor]
        print '<b>Package Name: %s</b><br />' % name
        print 'Price: %s<br />' % price
        allPrices = 0.00
        for pid in productIDs:
          sql = 'select Name, Price from %s;' % store
#          print sql
          cursor.execute(sql)
          pName, pPrice = cursor.fetchone()
          pPrice = float(pPrice)
          allPrices += pPrice
          print 'Product Name: %s<br />' % pName
        print 'All products together usually cost: $%f<br /><br />' %
str(fpformat.fix(round(int(allPrices * 100))/100, 2))
  cursor.close()
  bottom()

display()

TIA,
beno

-- 
The Logos has come to bear
http://logos.13gems.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20100108/a448f895/attachment.html>