making the value of a table equal to another value in a different table

Question

hi, i have a small problem with a program that im writing. I have a table - stocks which contains information(products, barcodes etc.) about items stored in a fridge. I then have another table - shop which acts like a shop,containing loads of products and their barcodes.some of the products in the shop table are in the stock table at the moment, and there is a boolenan field called stock which tells us if that product is in the stock table or not, if its equal to 1, it is in the fridge, if it is equal to 0, it is not in the fridge. Two fields in the stocks table are amount and quantity. The amount is what is in the fridge at the moment, the quantity is what has to be in the fridge at all times. When something is taken out of the fridge, that product's amount amount drops by 1. Each barcode in the stocks table has a matching one in the shop table. I need to make a query to the database from a python program which will order products from the shops table when the amount(whats in the fridge) is less than the quantity(whats meant to be in the fridge at all times). So you need to take the barcode of a row in the stocks table where the amount is less than the quantity and match that up to the barcode in the shops table. Then in the row of that matching barcode you need to set stock = 1. I'would be really happy if somebody could help me with this as i really am finding it difficult to write this function. below is the checkin and checkout functions if that will help. thanks a million lincoln

checkin

def check_in(): 
    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("""update shop set stock = 1 where barcode = %s""", (user_input))
    db.commit()
    numrows = int(cursor.rowcount)
    if numrows >= 1:
     row = cursor.fetchone()
     print row["product"]
     cursor.execute('update stock set amount = amount + 1 where product = %s', row["product"])
     db.commit()
     cursor.execute('udpate shop set stock = 1 where barcode = user_input')
     db.commit()
    else:
     new_prodname = raw_input('what is the name of the product and press enter: \n')
     cursor.execute('insert into shop (product, barcode, category) values (%s, %s, %s)', (new_prodname, user_input, new_prodname))
     cursor = db.cursor()
     query = ('select * from shop where product = %s', (new_prodname))
     cursor.execute(query):
     db.commit()
     numrows = int(cursor.rowcount)
     if numrows<1:
      cursor.execute('insert into atock (barcode, quantity, amount, product) values (%s, 1, 1, %s)', (user_input, new_prodname))
      db.commit()
      cursor.execute('insert into shop (product, barcode, category, stock) values (%s, %s, %s, 1)', (new_prodname, user_input, new_prodname))
      print new_prodname 
      print 'has been added to the fridge stock'
     else:
      cursor.execute('update atock set amount = amount + 1 where product = %s', (new_prodname))
      db.commit()
      cursor.execute('insert into shop (product, barcode, category, stock) values (%s, %s, %s, 1)', (new_prodname, user_input, new_prodname))
      print new_prodname 
      print 'has been added to the fridge stock'

checkout

import MySQLdb

def check_out():
    db = MySQLdb.connect(host='localhost', user='root', passwd='$$', db='fillmyfridge')
    cursor=db.cursor()
    user_input=raw_input('please enter the product barcode you wish to remove from the fridge: \n')
    query = cursor.execute('update stock set instock=0, howmanytoorder=howmanytoorder + 1, amount = amount - 1 where barcode = %s', (user_input))
    if cursor.execute(query):
     db.commit()
     print 'the following product has been removed from the fridge nd needs to be ordered'
     cursor.execute('update shop set stock = 0 where barcode = %s' (user_input)
     db.commit()
    else:
     return 0

thanks again. lincoln

Answer 1

Try to make the question shorter. That will generate more responses, I guess.

Answer 2

So, if I got that right you have the following tables:

Stock with at least a barcode, amount and quantity column

Shop with at least a barcode and a stock column

I don't understand why you need that stock column in the shop table, because you could easily get the products which are in stock by using a join like this:

SELECT barcode, ... FROM Stock ST JOIN Shop SH ON ST.barcode = SH.barcode;

Obviously you should also select some other columns from the Shop table or otherwise you could just select everything from the Stock table.

You can get a list of products which need to be ordered (where the amount is less than the quantity):

SELECT barcode FROM Stock ST WHERE ST.amount < ST.quantity;