Assigning 'for loop' variable to variable in python program
I'm writing a program at the moment that interacts with a MySQL database and I am having a problem. As you can see, I wrote a query that will search for products in the product table that match the barcode entered by the user.
If the barcode that the user enters is in the product table, I want to increase the amount field by 1 in the inventory table, where the product corresponding to the barcode input is the same as the product in the inventory table.
As you can see, I tried to assign a variable to the for loop to try and make it work that way, but it doesn't work. does anyone have any idea how to do this?
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 to a million.
source to share
The answer depends on what you mean by "assigning a variable to a for loop". This formulation is confusing because a for loop is a tool for controlling the flow of execution - it is usually not considered a value. But I think I know what you mean. Every time the loop runs, it executes print "%s" % (row["product"])
. I am assuming that you want to keep all the lines this does when you start the loop. I'm also going to assume what you meant row[product]
and not row["product"]
because the latter will be the same for the whole cycle. Then you can do this:
mylist = []
for product in result_set:
mylist.append("%s" % (row[product],))
Note that the% operation works even though you are no longer typing a string - this is a surprise to people coming from C. You can also use python list comprehension to make this event more concise:
mylist = ["%s" % (row[product],) for product in result_set]
source to share
I'm not sure how you get the row id from the value obtained from the products table. I would recommend explicitly specifying the required columns and not using idiom select * from
.
I've provided a helper function for finding id to make the code more readable:
def getAnIdFromValue(someValueTuple):
'''This function retrieves some table row identifier from a row tuple'''
returns someValueTuple[0]
I would try the following function body if multiple lines 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'
source to share
There. I also fixed the comma missing on the first line cursor.execute
.
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 can always use sqlalchemy :
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 the nicety: to update just change the object directly:
prod.ammount = prod.ammount + 1
session.flush()
session.commit()
source to share