insert two values from an mysql table into another table using a python program

Question

I'm having a small problem with a Python program (below) that I'm writing.

I want to insert two values from a MySQL table into another table from a Python program.

The two fields are priority and product and I have selected them from the shop table and I want to insert them into the products table.

Can anyone help? Thanks a lot. Marc.

import MySQLdb

def checkOut():
    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 are taking out of the fridge: \n')
    cursor.execute('update shops set instock=0, howmanytoorder = howmanytoorder + 1  where barcode = %s', (user_input))
    db.commit()
    cursor.execute('select product, priority from shop where barcode = %s', (user_input))
    rows = cursor.fetchall()
    cursor.execute('insert into products(product, barcode, priority) values (%s, %s)', (rows["product"], user_input, rows["priority"]))
    db.commit()
    print 'the following product has been removed from the fridge and needs to be ordered'

Answer 1

Well, the same thing again:

import MySQLdb

def checkOut():
    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 are taking out of the fridge: \n')
    cursor.execute('update shops set instock=0, howmanytoorder = howmanytoorder + 1  where barcode = %s', (user_input))
    db.commit()
    cursor.execute('select product, priority from shop where barcode = %s', (user_input))
    rows = cursor.fetchall()

1. Do you need fetchall()?? Barcode's are unique I guess and one barcode is to one product I guess. So, fetchone() is enough....isn't it??

2. In any case if you do a fetchall() its a result set not a single result. So rows["product"] is not valid. It has to be

   for row in rows:
       cursor.execute('insert into products(product, barcode, priority) values (%s, %s, %s)', (row["product"], user_input, row["priority"]))
   db.commit()
   print 'the following product has been removed from the fridge and needs to be ordered'
   

or better

import MySQLdb

def checkOut():
    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 are taking out of the fridge: \n')
    cursor.execute('update shops set instock=0, howmanytoorder = howmanytoorder + 1  where barcode = %s', (user_input))
    cursor.execute('insert into products(product, barcode, priority) select product, barcode, priority from shop where barcode = %s', (user_input))
    db.commit()

Edit: Also, you use db.commit() almost like print - anywhere, you need to read and understand the atomicity principle for databases

Answer 2

You don't mention what the problem is, but in the code you show this:

cursor.execute('insert into products(product, barcode, priority) values (%s, %s)', (rows["product"], user_input, rows["priority"]))

where your values clause only has two %s's in it, where it should have three:

cursor.execute('insert into products(product, barcode, priority) values (%s, %s, %s)', (rows["product"], user_input, rows["priority"]))