Assigning a 'for loop' to a variable in a python program

Question

hi, Im writing a program at the moment(below) that interacts with a MySQL database and im having a problem. As you can see ive written a query that will look for products in the products table that corresponds to the barcode that the user has inputted. If the barcode that is inputted by the user is found in the products table, I want to increase the 'amount' field by 1 in the stocks table where the product that corresponds to the barcode inputted, is the same as the product in the stocks table. as you can see ive tried to assign a variable to the for loop to try and get it to work that way but its not working. does anyone have any idea of how to do it. thanks.

import MySQLdb

def look_up_product():
    db = MySQLdb.connect(host='localhost', user = 'root', passwd='$$', db='fillmyfridge')
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    user_input=raw_input('please enter the product barcode that you wish to checkin to the fridge: \n')
    if cursor.execute("""select * from products where product = %s""", (user_input)):
     db.commit()
     result_set = cursor.fetchall ()
     #i want here to assign a variable to this for loop and the line below = for product in result_set: 
      print "%s" % (row["product"])
     cursor.execute('update stocks set amount = amount + 1 where product = %s', (#here i want the result of the for loop))
     db.commit()
    else:
     print 'no not in products table'

thanks a million. lincoln

Answer 1

Are you expecting a single row as a result? If so, try this:

row = cursor.fetchone()
print row["product"]
cursor.execute('update stocks set amount = amount + 1 where product = %s', row["product"])

Answer 2

I'm not sure how you get a row id from value fetched from products table. I'd recommend explicitely specifying needed columns and not using the select * from idiom.

I introduced the helper function for the id retrieval to make code more readable:

def getAnIdFromValue(someValueTuple):
    '''This function retrieves some table row identifier from a row tuple'''
    returns someValueTuple[0]

I'd try the following function body if multiple rows are expected:

db = MySQLdb.connect(...)
cursor = db.cursor()
ids = []
cursor.execute("""select * from products where product = %s""", (user_input))
for value in cursor.fetchall():
    #value is a tuple. len(value) == number of columns in products table
    ids.append(getAnIdFromValue(value))
if len(ids):
    cursor.executemany("update stocks set amount = amount + 1 where product =%s", tuple(ids))
    db.commit()
else:
    print 'no not in products table'

Answer 3

I think you need to indent the "update stocks..." line so that it's inside the for loop.

Answer 4

There there. I also fixed a comma you were missing on the first cursor.execute line.

import MySQLdb

def look_up_product():
    db = MySQLdb.connect(host='localhost', user = 'root',
                         passwd='$$', db='fillmyfridge')
    cursor = db.cursor (MySQLdb.cursors.DictCursor)
    user_input=raw_input('please enter the product barcode '
                         'that you wish to checkin to the fridge: \n')
    cursor.execute("""select * from products where product = %s""",
                   (user_input,))
    for row in iter(cursor.fetchone, None):
        print row["product"]
        cursor.execute('update stocks set amount = amount + 1' 
                       ' where product = %s', (row["product"],))
    db.commit()

Of course, you could always use sqlalchemy instead:

import sqlalchemy as sa
import sqlalchemy.orm

# Prepare high-level objects:
class Product(object): pass
engine = sa.create_engine('mysql://root:$$@localhost/fillmyfridge')
session = sa.orm.create_session(bind=engine)
product_table = sa.Table('products', sa.MetaData(), autoload=True)
sqlalchemy.orm.mapper(Product, product_table)

def look_up_product():
    user_input=raw_input('please enter the product barcode '
                         'that you wish to checkin to the fridge: \n')
    for prod in session.query(Product).filter(Product.product == user_input):
        print prod.product
        # Here's the nicety: to update just change the object directly:
        prod.ammount = prod.ammount + 1
    session.flush()
    session.commit()

Answer 5

The answer depends on what you mean by "assign a variable to a for loop." This wording is confusing because a for loop is a tool for controlling the flow of execution -- it's not normally thought of as having a value. But I think I know what you mean. Every time the loop runs, it will execute print "%s" % (row["product"]). I'm guessing that you want to store all of the strings that this makes as the loop runs. I'm also going to guess you meant row[product] and not row["product"], because the latter will be the same for the whole loop. Then you can do this:

mylist = []
for product in result_set: 
    mylist.append("%s" % (row[product],))

Notice that the % operation works even though you're not printing the string anymore -- this is a surprise for people coming from C. You can also use python list comprehensions to make this event more succinct:

mylist = ["%s" % (row[product],) for product in result_set]