Another Screwy Problem
J. Clifford Dyer
jcd at sdf.lonestar.org
Fri Jan 8 16:57:35 EST 2010
On Fri, Jan 08, 2010 at 03:32:34PM -0400, Victor Subervi wrote regarding Another Screwy Problem:
> Date: Fri, 8 Jan 2010 15:32:34 -0400
> From: Victor Subervi <victorsubervi at gmail.com>
> To: python-list <python-list at python.org>
> Subject: Another Screwy Problem
>
> 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
> [1]http://logos.13gems.com/
>
> References
>
> 1. http://logos.13gems.com/
> --
> http://mail.python.org/mailman/listinfo/python-list
Sigh. Ignore my last message. I was reading the part that Caersten quoted, not the original message. Well, ignore the part where I say you didn't show us your code. You obviously did. However, you showed us so much code that it's hard to find your problem. Before you ask questions, try to trim your code down to just enough to demonstrate the problem. In this case:
>>> db = MySQLdb.connect(host, user, passwd, db)
>>> cursor = db.cursor()
>>> sql = 'select Name, Price from %sPackages where ID=%s;' % (store, pid)
>>> print cursor.fetchall()
((1,),)
Would have been sufficient. Then you could ask, "why does cursor.fetchall() return ((1,),) instead of the expected results.
(The answer is that you never executed your query.)
More information about the Python-list
mailing list