Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import mysql.connector
- try:
- cnx = mysql.connector.connect(host='localhost',database='checklist',user='root',password='')
- cursor = cnx.cursor()
- except:
- print("There is no connection with server")
- raise SystemExit()
- print(" Analysis of checks")
- print(" MENU")
- print("1: Top 10 products by turnover, number of sales, the number of checks")
- print("2: Number of checks in the shop")
- print("3: Number of checks certain product")
- print("4: The average check of certain products")
- print("5: The turnover, number of checks, sales in shops")
- print("6: Number of checks on certain product by day the of week")
- print("7: Number of checks on certain product by dayS the of week")
- print("0: Exit")
- while (True):
- n = int(input("Input number of query\n"))
- if (n==1):
- query = ("SELECT product__name, total_price * COUNT( product_id)FROM products GROUP BY product_id ORDER BY (total_price * COUNT( product_id )) DESC LIMIT 0 , 10")
- cursor.execute(query)
- result = cursor.fetchall()
- print("Top 10 products by turnover")
- for i in result:
- print(i)
- query =("SELECT product__name, qty*COUNT( product__name) FROM products GROUP BY product_id ORDER BY (qty*COUNT( product__name )) DESC LIMIT 0 , 10 ")
- cursor.execute(query)
- result = cursor.fetchall()
- print("Top 10 products by number of sales")
- for i in result:
- print(i)
- query = ("SELECT product__name,COUNT( product__name ) FROM products GROUP BY product_id ORDER BY COUNT(product__name) DESC LIMIT 0,10 ")
- cursor.execute(query)
- result = cursor.fetchall()
- print("Top 10 products by number of checks")
- for i in result:
- print(i)
- elif n==2:
- shopname = input("Enter Shop Name, for example Shop №01\n")
- query = ("SELECT shop__name, COUNT( shop_id ) FROM checkinfo WHERE (shop__name='%s') GROUP BY shop_id ORDER BY (COUNT( shop_id )) DESC")%shopname
- print("Number of checks in the shop")
- cursor.execute(query)
- result = cursor.fetchall()
- print(result)
- elif n==3:
- productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
- query = ("SELECT product__name, COUNT( product__name )FROM products WHERE (product__name ='%s')ORDER BY COUNT( product_id ) DESC ")%productname
- print("Number of checks certain product")
- cursor.execute(query)
- result = cursor.fetchall()
- print(result)
- elif n==4:
- productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
- query = ("SELECT product__name, SUM( checkinfo.total_price )/COUNT( products.product__name ) FROM products, checkinfo WHERE (product__name ='%s' AND checkinfo.id = products.id)")%productname
- print("The average check of certain products")
- cursor.execute(query)
- result = cursor.fetchall()
- print(result)
- elif n==5:
- query = ("SELECT shop__name, SUM( total_price ) FROM checkinfo GROUP BY shop__name ")
- cursor.execute(query)
- result = cursor.fetchall()
- print("The turnover of the shops")
- for i in result:
- print(i)
- query =("SELECT shop__name, COUNT( shop_id ) FROM checkinfo GROUP BY shop_id ORDER BY ( COUNT( shop_id ) ) DESC ")
- cursor.execute(query)
- result = cursor.fetchall()
- print("Number of checks in the shops")
- for i in result:
- print(i)
- query = ("SELECT shop__name, SUM( items_qty ) FROM checkinfo GROUP BY shop_id ORDER BY ( COUNT( items_qty ) ) DESC ")
- cursor.execute(query)
- result = cursor.fetchall()
- print("Sales in the shops")
- for i in result:
- print(i)
- elif n==6:
- day = input("Enter day of the week: 0:Monday .. 6:Sunday\n")
- productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
- query = ("SELECT product__name, COUNT(product__name)FROM products,checkinfo WHERE (product__name='%s' AND checkinfo.week_day= '%s' AND products.id = checkinfo.id)")%(productname,day)
- print("Number of checks on certain product by day the of week")
- cursor.execute(query)
- result = cursor.fetchall()
- print(result)
- elif n==7:
- productname = input("Enter product, for example, Kent Nanotek Silver 2.0\n")
- query = ("SELECT week_day, COUNT( product__name ) FROM products, checkinfo WHERE (product__name = '%s' AND products.id = checkinfo.id)GROUP BY (week_day)")%productname
- print("Number of checks on certain product by dayS the of week: 0:Monday .. 6:Sunday")
- cursor.execute(query)
- result = cursor.fetchall()
- for i in result:
- print(i)
- elif n==0:
- print("Good bye!")
- break
- raise SystemExit()
- cursor.close()
- cnx.close()
- elif n not in [0,1,2,3,4,5,6,7]:
- print("This command does not exist")
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement