Using results of sql query in python program in another sql query
Sorry for my previous question, which was very controversial, but I think if I get an answer to this question, I can work it out. In the program below, I have selected product barcodes that are less than quantity. What I mean is that if the barcodes (in the refrigerator table) match the barcodes in another table (products) then set the stock field to 0. Im get problem is that the program is trying to match all the barcodes it found in the request for individual barcodes in the product table (this is what I think). Does anyone know what to do. forever grateful. 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)
source to share
UPDATE products SET stock = 0 WHERE barcode IN (
SELECT fridge.barcode FROM fridge WHERE fridge.amount < fridge.quantity );
I know this doesn't exactly answer the question, but it doesn't require two SQL statements.
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])
source to share
This is more of a SQL query than Python, but I'll try to answer this: (I haven't worked with MySQL, but PostgreSQL, so there might be slight differences in interpretation of things here).
when did you do
cursor.execute('select barcode from fridge where amount < quantity')
db.commit()
row = cursor.fetchall()
the variable 'row' is now a result set (to understand: a list of rows from the database) something like [(barcode1), (barcode2), (barcode3) ..]
when you execute 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 valid 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, an 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 db.commit () after the select request and not after the update request, this is a major error. Select is idempotent does not require commit, whereas Update does. I highly recommend that you go through the SQL before continuing.
source to share