FAQ
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
&#34;back&#34; 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.htm>

Search Discussions

  • Carsten Haese at Jan 8, 2010 at 9:06 pm

    Victor Subervi wrote:
    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,),)
    The only logical explanation is that this is not the output from
    executing the above-mentioned query. Maybe you should ask yourself what
    it actually is.

    -Carsten
  • J. Clifford Dyer at Jan 8, 2010 at 9:44 pm

    Victor Subervi wrote:
    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,),)

    First, never use string formatting to pass parameters to your database. Read the MySQLdb documentation (or sqlite, or psycopg2) documentation for reasons why, and how to do it right.

    Second, in the same documentation, look up anything having the word "fetch" in it. That should show you how to get the data you want

    Third, please be more specific in your questions. You say "In my script for some reason *it* returns this: ((1,),)," but you don't show us what "it" is. How did you get that from your script? So far we've got a variable called sql with your query in it. How do I get the same wrong result you got? I don't know. I could pass it to a function that looks like this:

    def botch_sql_query(sql):
    return ((1,),)

    I could furthermore "fix" it, so that it looks right, by doing this:

    def fixed_sql_query(sql):
    return(('pkg', 123.45),)

    But that probably doesn't help. Give us enough code to be clear, but not so much as to be overwhelming. There's an essay called "How to ask smart question" by Eric Raymond that should help.

    Cheers,
    Cliff
  • J. Cliff Dyer at Jan 9, 2010 at 2:07 pm

    On Sat, 2010-01-09 at 07:59 -0500, Victor Subervi wrote:
    On Fri, Jan 8, 2010 at 4:44 PM, J. Clifford Dyer
    wrote:
    Victor Subervi wrote:
    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,),)


    First, got your other email. I thought I had executed the statement.
    Oops. Works fine now. Sorry.

    First, never use string formatting to pass parameters to your
    database. Read the MySQLdb documentation (or sqlite, or
    psycopg2) documentation for reasons why, and how to do it
    right.

    The only thing I found, which collaborates with something someone else
    taught me on this list about entering binary data, is that one must
    pass the parameters in the execute statement. Is that what you mean?
    If so, I find that for all purposes thus far other than binary data,
    the way I've been doing it seems to work just fine. I would prefer to
    keep doing it that way, because I find putting a print statement
    between the sql= line and the execute statement gives me a good
    opportunity to review the sql statement and catch errors. Is this not
    good practice?


    Thanks.
    beno
    This is a horrendous practice. You leave yourself vulnerable not only
    to attacks, but to simple absent-mindedness as well. Using parameters
    in your execute statement will handle all necessary quoting for you,
    which eliminates the possibility of a bad query sneaking in. For more
    information, as I mentioned, look up SQL injection. Also, read this:
    http://xkcd.com/327/

    Cheers,
    Cliff
  • Victor Subervi at Jan 9, 2010 at 2:14 pm

    On Sat, Jan 9, 2010 at 9:07 AM, J. Cliff Dyer wrote:
    On Sat, 2010-01-09 at 07:59 -0500, Victor Subervi wrote:
    On Fri, Jan 8, 2010 at 4:44 PM, J. Clifford Dyer
    wrote:
    Victor Subervi wrote:
    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,),)


    First, got your other email. I thought I had executed the statement.
    Oops. Works fine now. Sorry.

    First, never use string formatting to pass parameters to your
    database. Read the MySQLdb documentation (or sqlite, or
    psycopg2) documentation for reasons why, and how to do it
    right.

    The only thing I found, which collaborates with something someone else
    taught me on this list about entering binary data, is that one must
    pass the parameters in the execute statement. Is that what you mean?
    If so, I find that for all purposes thus far other than binary data,
    the way I've been doing it seems to work just fine. I would prefer to
    keep doing it that way, because I find putting a print statement
    between the sql= line and the execute statement gives me a good
    opportunity to review the sql statement and catch errors. Is this not
    good practice?


    Thanks.
    beno
    This is a horrendous practice. You leave yourself vulnerable not only
    to attacks, but to simple absent-mindedness as well. Using parameters
    in your execute statement will handle all necessary quoting for you,
    which eliminates the possibility of a bad query sneaking in. For more
    information, as I mentioned, look up SQL injection. Also, read this:
    http://xkcd.com/327/

    Thanks :)
    beno
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: <http://mail.python.org/pipermail/python-list/attachments/20100109/a8404329/attachment-0001.htm>
  • J. Clifford Dyer at Jan 8, 2010 at 9:57 pm

    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
    &#34;back&#34; 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.)

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppython-list @
categoriespython
postedJan 8, '10 at 7:32p
activeJan 9, '10 at 2:14p
posts6
users3
websitepython.org

People

Translate

site design / logo © 2022 Grokbase