using results from a sql query in a python program in another sql query

Question

hi, sorry for my previous question which was very ambiguous, but i think if i get the answer to this question I can work it out. In the program below i have selected the barcodes of products where the amount is less than the quantity. I want to say, that if the barcodes(in the fridge table) match barcodes in another table(products), set the stock field equal to 0. The problem Im getting is that the program is trying to match all the barcodes that it found in the query against single barcodes in the products table(thats what I think). does anyone know what to do. thanks a million. lincoln.

import MySQLdb

def order():
    db = MySQLdb.connect(host='localhost', user='root', passwd='$$', db='fillmyfridge')
    cursor = db.cursor()
    cursor.execute('select barcode from fridge where amount < quantity')
    db.commit()
    row = cursor.fetchall()
    cursor.execute('update products set stock = 0 where barcode = %s', row)

Answer 1

UPDATE products SET stock = 0 WHERE barcode IN ( 
    SELECT fridge.barcode FROM fridge WHERE fridge.amount < fridge.quantity );

I know this doesn't answer the question exactly but two SQL statements are not required.

To do it in python:

import MySQLdb

def order():
    db = MySQLdb.connect(host='localhost', user='root', passwd='$$', db='fillmyfridge')
    cursor = db.cursor()
    cursor.execute('select barcode from fridge where amount < quantity')
    db.commit()
    rows = cursor.fetchall()
    for row in rows
        cursor.execute('update products set stock = 0 where barcode = %s', row[0])

Answer 2

This is more of SQL query than Python, but still I will try to answer that: (I haven't worked with MySQL but PostgreSQL, so there might slight variation in interpretation of things here).

when you did

cursor.execute('select barcode from fridge where amount < quantity')
db.commit()
row = cursor.fetchall()

the variable 'row' now is a resultset (to understand: a list of rows from the database) something like [(barcode1), (barcode2), (barcode3)..]

when you do the update statement

cursor.execute('update products set stock = 0 where barcode = %s', row)

this turns into something like:

update products set stock = 0 where barcode = [(barcode1), (barcode2), (barcode3)..]

which is not a correct SQL statement.

you should do something like this:

cursor.execute('update products set stock = 0 where barcode in (%s)', ','.join([each[0] for each in row]))

or better, the optimized thing:

import MySQLdb

def order():
    db = MySQLdb.connect(host='localhost', user='root', passwd='$$', db='fillmyfridge')
    cursor = db.cursor()
    cursor.execute('update products set stock = 0 where barcode in (select barcode from fridge where amount < quantity)')
    db.commit()

Well, to add more you have a db.commit() after a select query and not after an update query, thats a basic fault. Select is idempotent, doesn't need commit, whereas Update does. I will strongly recommend you to go through some SQL before continuing.