from dbconfig import * # store your db credential in an ini file and parse and use it. import pymysql # connect to MySQL db. import sys # sys: command line arguments. # Get command line argument of product line id. # sys.argv[0]: h8q1.py # sys.argv[1]: 1 major = sys.argv[1] if len(sys.argv) > 1: pl_id = sys.argv[1] else: # major = 'ITEC' p1_id = 1 # Connect to big_pvfc of the MySQL server and # set up SQL statement to obtain product information # in the product line db = get_mysql_param() cnx = pymysql.connect(user=db['user'], password=db['password'], host=db['host'], database=db['database']) cursor = cnx.cursor() query = ''' select distinct p.productId, p.productDescription, p.ProductFinish, sum(o.OrderedQuantity) as `ordered` from product_t p join orderline_t o on (p.productId = o.productId) where p.ProductLineID = %s group by p.productId, p.productDescription, p.ProductFinish; ''' cursor.execute(query,(int(pl_id),)) # Print product report. print('Products in Line #' + str(pl_id) + ':') print('--------------------') for (pid, desc, finish, ordered) in cursor: print('[' + desc + ' (id #' + str(pid) + '), finish: ' + finish + ']:' + str(ordered) + ' ordered.') cursor.close() cnx.close()