Clever Geek Handbook

Insert two values ​​from mysql table into another table using python program

I have a small problem with a Python program (below) I am writing.

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

Two fields are priority and product, and I selected them from the store table, and I want to insert them into the product table.

Can anyone please help? Many thanks. Mark.

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'
-2


source to share


2 answers


Well, 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()

  • Do you need fetchall () ?? I believe the barcode is unique and one barcode is specific to one product that I am assuming. So fetchone () is enough .... isn't it?

  • In any case, if you do fetchall (), its result will have no effect. Therefore it is rows["product"]

    not valid. It should 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 should read and understand the atomicity principle for databases

+1


source


You didn't mention what the problem is, but in the code you show it:

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


where there are only two% s in your values ​​clause, where there should be three:

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


source






More articles:


All Articles