def index(): conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels') cursor = data.create_cursor(conn) cursor.execute(""" select * from `products` """) return render_template('index.template.html', cursor=cursor)
def process_create_product(): # extract out all the variables from the form product_code = request.form.get('productCode') product_name = request.form.get('productName') product_line = request.form.get('productLine') product_vendor = "default vendor" product_scale = 100 product_description = "Desc" quantity_in_stock = 10 buy_price = 19.99 MSRP = 25.00 # prepare the SQL sql = f""" insert into `products` (`productCode`, `productName`, `productLine`, `productScale`, `productVendor`, `productDescription`, `quantityInStock`, `buyPrice`, `MSRP`) VALUES ('{product_code}', '{product_name}', '{product_line}', '{product_scale}', '{product_vendor}','{product_description}', '{quantity_in_stock}', '{buy_price}', '{MSRP}' ) """ conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels') cursor = data.create_cursor(conn) cursor.execute(sql) conn.commit() return redirect(url_for('search'))
def get_employees_ln(conn): sql = " select `lastName` from `employees`" cursor_ln = data.create_cursor(conn) cursor_ln.execute(sql) return cursor_ln
def search(): conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels') product_line_cursor = data.get_product_lines(conn) # create the query sql = """select * from `products` where 1""" # we use request.args to get whatever is in the query string selected_product_line = request.args.get('selected_product_line') if selected_product_line: print("Filtering by", selected_product_line) sql = sql + f" and `productLine` = '{selected_product_line}'" search_terms = request.args.get('search-by') # if search_terms is not None if search_terms: sql = sql + f" and `productName` like '%{search_terms}%'" products_cursor = data.create_cursor(conn) products_cursor.execute(sql) return render_template('search.template.html', product_lines=product_line_cursor, products=products_cursor, sql=sql)
def get_employees(conn, office_code): sql =f""" select * from `employees` where 1 and `officeCode` = {office_code} """ cursor = data.create_cursor(conn) cursor.execute(sql) return cursor
def get_employees_filtered(conn, user_input_fn, user_input_ln, user_input_ct, user_input_jt): sql = f"""select * from `employees` join `offices` on `employees`.`officeCode` = `offices`.`officeCode` where 1""" cursor = data.create_cursor(conn) print('user_input_fn : ', user_input_fn) if user_input_fn != "": sql = sql + f" and `firstName`='{user_input_fn}' " print(sql) print('user_input_ln : ', user_input_ln) if user_input_ln != "": sql = sql + f" and `lastName`='{user_input_ln}' " print('user_input_ct :', user_input_ct) if user_input_ct != "": sql = sql + f" and `country`='{user_input_ct}' " print('user_input_jt :', user_input_jt) if user_input_jt !="": sql = sql + f" and `jobTitle`='{user_input_jt}' " print('-------sql---------') print(sql) cursor.execute(sql) return cursor
def get_offices(conn): sql = """select * from `offices` where 1""" cursor = data.create_cursor(conn) cursor.execute(sql) return cursor
def new_employee(conn,employeeNumber,lastName,firstName,extension,email,officeCode,reportsTo,jobTitle ): cursor=data.create_cursor(conn) sql=f""" insert into `employees` (`employeeNumber`,`lastName`,`firstName`,`extension`,`email`,`officeCode`,`reportsTo`,`jobTitle`) values('{employeeNumber}','{lastName}','{firstName}','{extension}','{email}','{officeCode}','{reportsTo}','{jobTitle}') """ cursor.execute(sql) return cursor # def get_next_employee_number(conn): # cursor = data.create_cursor(conn) # sql = "select max(employeeNumber) from `employees` " # cursor.execute(sql) # next_employee_number = cursor.fetchone()['max(employeeNumber)'] +1 # print('next_employee_number: ', next_employee_number) # return next_employee_number
def get_employees_only(conn): sql = "select * from `employees`" cursor = data.create_cursor(conn) cursor.execute(sql) return cursor
def get_customer_by_customer_number(conn, customer_number): sql = f"""select * from `customers` where `customerNumber` = '{customer_number}'""" cursor = data.create_cursor(conn) cursor.execute(sql) customer = cursor.fetchone( ) #only one customer is associated with a customer_number return customer
def get_employees_jt(conn): sql = " select `jobTitle` from `employees` " cursor_jt = data.create_cursor(conn) cursor_jt.execute(sql) return cursor_jt
def get_offices(conn): sql = "select * from `offices` ORDER BY `officeCode` asc" cursor_offices= data.create_cursor(conn) cursor_offices.execute(sql) return cursor_offices
def get_reports_to(conn): cursor =data.create_cursor(conn) sql= "select `reportsTo` from `employees`" cursor.execute(sql) return cursor
def new_offices(conn, officeCode, city, phone, addressLine1, addressLine2, state, country, postalCode, territory): sql = f"""insert into `offices` (`officeCode`,`city`,`phone`,`addressLine1`,`addressLine2`,`state`,`country`,`postalCode`,`territory`) values('{officeCode}','{city}','{phone}','{addressLine1}','{addressLine2}','{state}','{country}','{postalCode}','{territory}') """ cursor = data.create_cursor(conn) cursor.execute(sql) conn.commit()
def get_order_details(conn, order_number): sql = f""" select * from `orderdetails` join `products` on `orderdetails`.`productCode` = `products`.`productCode` where `orderNumber` = '{order_number}' order by `orderLineNumber` """ cursor = data.create_cursor(conn) cursor.execute(sql) return cursor
def get_office_code_country(conn): cursor = data.create_cursor(conn) sql = f""" select `country`, `city` from `employees` join `offices` on `employees`.`officeCode` = `offices`.`officeCode` """ cursor.execute(sql) return cursor
def get_employees_ct(conn): sql = f"""select `country` from `employees` join `offices` on `employees`.`officeCode` =`offices`.`officeCode` """ cursor_ct = data.create_cursor(conn) cursor_ct.execute(sql) cursor_ct = cursor_ct.fetchall() print(cursor_ct) return cursor_ct
def get_customers(conn): sql = """select * from `customers` where 1""" cursor = data.create_cursor(conn) cursor.execute(sql) return cursor
import data import os conn = data.get_connection('localhost', os.environ.get('C9_USER'), '', 'classicmodels') cursor = data.create_cursor(conn) product_line = input("Please enter the product line: ") text_description = input("Please enter the description: ") sql = f""" insert into `productlines` (`productLine`, `textDescription`) VALUES ('{product_line}', '{text_description}') """ # remember to execute the query cursor.execute(sql) #commit the changes (i.e confirm the changes) conn.commit()
def get_orders_for_customer(conn, customer_number): # there is only one customer associated with an order, so the query below should return only one result. sql = f"""select * from `orders` where `customerNumber` = '{customer_number}'""" cursor = data.create_cursor(conn) cursor.execute(sql) return cursor