def book(): print("Fetching book information table") db_connection = connect_to_database() query = "SELECT * FROM books" result = execute_query(db_connection, query) print(result) query = "SELECT bookID FROM books" books = execute_query(db_connection, query) print(books) ids = books.fetchall() for id in ids: print(id) book_id = request.args.get('book_id') print("book id: ", book_id) if (book_id is not None): print("Selecting author with id ", book_id) query = "SELECT title FROM books where bookID=%s" % book_id titles = execute_query(db_connection, query) for n in titles: print(n) return render_template('book.html', rows=result, book_id=books, search=titles) # state = {'id': author_id, 'search_id': search_id} return render_template('book.html', rows=result, book_id=books)
def create_palace(): """ process the create a palace query sent from new_palace() and displays a confirmation message. """ db_connection = connect_to_database() # create palace inputs = request.form.to_dict(flat=True) print("Creating a new Palace...") query = "INSERT INTO `palaces` (`name`, `description`) " \ "VALUES('{}', '{}');".format(inputs['name'], inputs['desc']) # successfully created message and instructions message = 'Palace successfully created.' instruction = 'Select the palace in the dropdown above and click "VIEW" to add locus to it.' # error handler for mysql. Set error message to be displayed on webapp try: execute_query(db_connection, query).fetchall() except db_connection.IntegrityError as err: message = 'Could not create palace.' instruction = 'Palace name must be unique. Error: ' + str(err) context = { 'list_palaces': list_palaces(), 'message': message, 'instruction': instruction } return render_template('createPalace.html', context=context)
def admin_update_product(product_id): '''Update a product that exists in the database.''' db_connection = connect_to_database() # Get the existing product info and pass it into a template if request.method == 'GET': # Create and execute query to get all product info get_prod_info_query = 'SELECT * FROM `products` WHERE product_id = %s;' % ( product_id) get_prod_info_result = execute_query(db_connection, get_prod_info_query).fetchone() return render_template('admin-update-product.html', product=get_prod_info_result) # Update the product using the submitted form if request.method == 'POST': # Gather form data product_name = request.form['product-name'] category = request.form['category'] vendor = request.form['vendor'] price = request.form['price'] qty_available = request.form['qty-available'] image = request.form['image'] # Create and execute query to update the product update_prod_query = 'UPDATE `products` SET product_name = %s, category = %s, vendor = %s, price = %s, image = %s, quantity_available = %s \ WHERE product_id = %s;' data = (product_name, category, vendor, price, image, qty_available, product_id) update_prod_result = execute_query(db_connection, update_prod_query, data) return redirect( url_for('admin')) # Redirect user back to the admin page
def update_staff(id): print('in the update_staff function') db_connection = connect_to_database() #display existing data about a staff if request.method == 'GET': staff_query = "SELECT firstName, lastName, staffType, staffID from Staff WHERE staffID = %s" % ( id) staff_result = execute_query(db_connection, staff_query).fetchone() # when staff is not part of Staff table if staff_result == None: return "No such Staff found!" return render_template('update_staff.html', staff=staff_result) # update staff and show the change in the browse staff page elif request.method == 'POST': staffID = request.form['staffID'] firstName = request.form['firstName'] lastName = request.form['lastName'] staffType = request.form['staffType'] query = "UPDATE Staff SET firstName = %s, lastName = %s, staffType = %s WHERE staffID = %s" data = (firstName, lastName, staffType, staffID) result = execute_query(db_connection, query, data) flash("Updated! " + " Staff ID #" + str(staffID)) return redirect('/browse_staff')
def add_staff_patients(): db_connection = connect_to_database() if request.method == 'GET': patient_query = 'SELECT patientID, firstName, lastName FROM Patients' patient_result = execute_query(db_connection, patient_query) staff_query = 'SELECT staffID, firstName, lastName FROM Staff' staff_result = execute_query(db_connection, staff_query) return render_template('add_staff_patients.html', staff=staff_result, patients=patient_result) elif request.method == 'POST': print("Adding new staff-patient relationship") staf_id = request.form['staf_id'] pat_id = request.form['pat_id'] # check if relation already exists qo = 'SELECT staffID, patientID FROM Staff_Patients WHERE staffID = %s and patientID = %s' do = (staf_id, pat_id) ro = execute_query(db_connection, qo, do).fetchone() if ro is None: query = 'INSERT INTO Staff_Patients (staffID, patientID) VALUES (%s,%s)' data = (staf_id, pat_id) execute_query(db_connection, query, data) flash('Relationship Added!') else: flash('Error Relation Already Exits!') return redirect('add_staff_patients')
def updateProduct(id): print('In the function') db_connection = connect_to_database() #display existing data if request.method == 'GET': print('The GET request') product_query = 'SELECT productID, productName, brandName, price, category, sale, color from Products WHERE productID = %s' % ( id) product_result = execute_query(db_connection, product_query).fetchone() if product_result == None: return "No product found!" return render_template('updateProduct.html', product=product_result) elif request.method == 'POST': print('The POST request') productID = request.form['productID'] productName = request.form['productName'] brandName = request.form['brandName'] price = request.form['price'] category = request.form['category'] sale = request.form['sale'] color = request.form['color'] query = "UPDATE Products SET productName = %s, brandName = %s, price = %s, category = %s, sale = %s, color = %s WHERE productID = %s" data = (productName, brandName, price, category, sale, color, productID) result = execute_query(db_connection, query, data) return redirect(url_for('products'))
def updateCustomer(id): print('In the function') db_connection = connect_to_database() #display existing data if request.method == 'GET': print('The GET request') customer_query = 'SELECT customerID, email, firstName, lastName, address, dob, phone, city, state, zipcode from Customers WHERE customerID = %s' % ( id) customer_result = execute_query(db_connection, customer_query).fetchone() if customer_result == None: return "No customer found!" return render_template('updateCustomer.html', customer=customer_result) elif request.method == 'POST': print('The POST request') customerID = request.form['customerID'] email = request.form['email'] firstName = request.form['firstName'] lastName = request.form['lastName'] address = request.form['address'] dob = request.form['dob'] phone = request.form['phone'] city = request.form['city'] state = request.form['state'] zipcode = request.form['zipcode'] query = "UPDATE Customers SET email = %s, firstName = %s, lastName = %s, address = %s, dob = %s, phone = %s, city = %s, state = %s, zipcode = %s WHERE customerID = %s" data = (email, firstName, lastName, address, dob, phone, city, state, zipcode, customerID) result = execute_query(db_connection, query, data) return redirect(url_for('customers'))
def browse_counselors(): print("Fetching and rendering counselors web page") db_connection = connect_to_database() #display all counselors if request.method == 'GET': query = "SELECT `counselorID`, `first_name`, `last_name`, `name` FROM `Counselors` INNER JOIN Shelters ON Counselors.shelterID = Shelters.shelterID ORDER BY `first_name`" result = execute_query(db_connection, query).fetchall() print(result) #query to show dropdown shelter options in counselor form query = 'SELECT shelterID, name from Shelters' shelters = execute_query(db_connection,query).fetchall() print(shelters) return render_template('counselors.html', rows=result, shelters=shelters) elif request.method == 'POST': #add counselor to table print("Adding an counselor") fname = request.form['firstName'] lname = request.form['lastName'] shelterID = request.form['shelterID'] #query to add counselor query = 'INSERT INTO Counselors (first_name, last_name, shelterID) VALUES (%s,%s,%s)' data = (fname, lname, shelterID) db_connection = connect_to_database() execute_query(db_connection, query, data) return redirect('/counselors')
def update_counselor(id): db_connection = connect_to_database() #display existing data if request.method == 'GET': #query to populate form for counselor with given id people_query = 'SELECT counselorID, first_name, last_name, shelterID FROM Counselors where counselorID = %s' % (id) people_result = execute_query(db_connection, people_query).fetchone() if people_result == None: return "No such person found!" #query to poulate shelter dropdown on form shelters_query = 'SELECT shelterID, name from Shelters' shelters_results = execute_query(db_connection, shelters_query).fetchall() print('Returning') return render_template('update_counselor.html', shelters = shelters_results, person = people_result) elif request.method == 'POST': print('The POST request') #grabbing info from the form character_id = request.form['character_id'] fname = request.form['fname'] lname = request.form['lname'] shelter = request.form['shelter'] #query to update counselors query = "UPDATE Counselors SET first_name= %s, last_name= %s, shelterID = %s WHERE counselorID = %s" data = (fname, lname, shelter, character_id) result = execute_query(db_connection, query, data) print(str(result.rowcount) + " row(s) updated") return redirect('/counselors')
def classesedit(Class_ID): #If a GET request was the method by which the page was requested, then we will display the page containing an empty textform for inputting a new value for the Class_Name attribute for this particular row in the Class table if request.method == 'GET': return render_template('classesedit.html') #If a POST request was the method by which the page was requested, then the user arrives to this view after having used the forms involved in updating a class's name. elif request.method == 'POST': #Connect to your database and create a new database object db_connection = connect_to_database() #Obtain the user supplied strings from the forms in the Edit Class Page and assign them to the correspondingly named variable below. New_ClassName = request.form['EditClassNameInput'] #Create a string representation of an UPDATE query into the Class Table query = 'UPDATE Class SET Class_Name = %s WHERE Class_ID = %s;' #Use the variable containing user input from the form and store it as a tuple assigned to var data. data = ( New_ClassName, Class_ID, ) #Execute the query using the query's string representation, tuple of user "selected" data, and the database object. execute_query(db_connection, query, data) #Redirect the user back to the '/students' view to show the newly updated Class's name. return redirect('/classes')
def schoolsadd(): #If a GET request was the method by which the page was requested, then we will display the page containing empty forms for adding a new School. if request.method == 'GET': return render_template('schoolsadd.html') #If a POST request was the method by which the page was requested, then the user arrives to this view after having used the forms involved in adding a new School. elif request.method == 'POST': #Connect to your database and create a new database object db_connection = connect_to_database() #Obtain the user supplied strings from the forms in the Add Schools Page and assign them to the correspondingly named variables below. School_Name = request.form['SchoolNameInput'] School_Address = request.form['SchoolAddressInput'] School_City = request.form['SchoolCityInput'] School_Zipcode = request.form['SchoolZipcodeInput'] #Create a string representation of an INSERT query into the School Table query = 'INSERT INTO School(School_Name, Street_Address, City, Zip_Code) VALUES (%s, %s, %s, %s);' #Use the variables containing user input from the forms and store them together as a tuple assigned to var data. data = (School_Name, School_Address, School_City, School_Zipcode) #Execute the query using the query's string representation, tuple of user supplied data, and the database object. execute_query(db_connection, query, data) return redirect('/schools')
def schools(): #Connect to your database and create a new database object db_connection = connect_to_database() #If a GET request was the method by which the page was requested, then we will display all the user-relevant information from the School table if request.method == 'GET': query = 'SELECT School_Name, Street_Address, City, Zip_Code FROM School;' result = execute_query(db_connection, query).fetchall() return render_template('schools.html', SELECT_School_Rows=result) #If a POST request was the method by which the page was requested, then the user arrives to this page after having used the Search form/submit button elif request.method == 'POST': #Take the user supplied string they are interested in using for their search criteria Search_String = request.form['FindSchoolName'] #Add MariaDB compliant wildcard SQL operators on either side of the user's search string and save the result as a string object assigned to var pattern. pattern = '%' + Search_String + '%' #Execute a SELECT query using the user's search term concatenated with the SQL wildcards to the database object. Store the result of the query in var result. The values correspond to the variable SELECT_School_Rows in the Jinja template. result = execute_query( db_connection, 'SELECT School_Name, Street_Address, City, Zip_Code FROM School WHERE School_Name LIKE %s;', (pattern, )).fetchall() #Populate the table in schools.html template with the values stored in var result. return render_template('schools.html', SELECT_School_Rows=result)
def order(): print("Fetching order information table") db_connection = connect_to_database() query = "SELECT * FROM orders" result = execute_query(db_connection, query) print(result) query = "SELECT orderID FROM orders" order = execute_query(db_connection, query) print(order) ids = order.fetchall() for id in ids: print(id) order_id = request.args.get('order_id') print("order id: ", order_id) if (order_id is not None): print("Selecting customer with id ", order_id) query = "SELECT customerID, orderDate FROM orders where orderID=%s" % order_id orderInfo = execute_query(db_connection, query) for n in orderInfo: print(n) return render_template('order.html', rows=result, order_id=order, order=orderInfo) return render_template('order.html', rows=result, order_id=order)
def customer(): print("Fetching customer information table") db_connection = connect_to_database() query = "SELECT * FROM customers" result = execute_query(db_connection, query) print(result) query = "SELECT customerID FROM customers" customer = execute_query(db_connection, query) print(customer) ids = customer.fetchall() for id in ids: print(id) customer_id = request.args.get('customer_id') print("customer id: ", customer_id) if (customer_id is not None): print("Selecting customer with id ", customer_id) query = "SELECT phoneNumber FROM customers where customerID=%s" % customer_id customerInfo = execute_query(db_connection, query) for n in customerInfo: print(n) return render_template('customer.html', rows=result, customer_id=customer, customer=customerInfo) return render_template('customer.html', rows=result, customer_id=customer)
def browse_drinks(): db_connection = connect_to_database() # Adding a new drink from form if request.method == 'POST': print("Add new drink!") price = request.form['price'] name = request.form['name'] inventory = request.form['inventory'] secret_ingredient = request.form['sec_ing'] if(price != '' and inventory != '' and name != ''): query = 'INSERT INTO drinks (price, inventory, secret_ingredient, name) VALUES (%s,%s,%s, %s)' if secret_ingredient == '0': data = (price, inventory, None, name) else: data = (price, inventory, secret_ingredient, name) execute_query(db_connection, query, data) print("drink added!") # Getting current drinks print("Fetching and rendering drinks web page") query = "SELECT drinks.id, price, inventory, ingredients.ingredient_name as 'Secret Ingredient', drinks.name from drinks LEFT JOIN ingredients ON drinks.secret_ingredient = ingredients.id;" drink_result = execute_query(db_connection, query).fetchall() # Getting ingredients for add new drink dropdown query = 'SELECT id, ingredient_name FROM ingredients' ing_result = execute_query(db_connection, query).fetchall() ing_result = list(ing_result) ing_result.append((0,'None')) ing_result = tuple(ing_result) return render_template('browse_drinks.html', rows=drink_result, ingredients=ing_result, links=links)
def browse_dogs(): db_connection = connect_to_database() if request.method == 'GET': #query to display dogs table query = 'SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID' result = execute_query(db_connection, query).fetchall() return render_template('dogs.html', rows=result) elif request.method == 'POST': status = request.form['dogstatus'] search = '%' + request.form['dogSearch'] + '%' #if filter chosen- if status != "any": #filter dogs by given status query = "SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID WHERE adoption_status=%s" data = (status,) result = execute_query(db_connection, query, data).fetchall() #if search criteria entered elif search != None: #search for dog by name query = "SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID WHERE Dogs.name LIKE %s" data = (search,) result = execute_query(db_connection, query, data).fetchall() #default- display dog table per usual else: query = 'SELECT petID, Shelters.name, Dogs.name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok FROM Dogs INNER JOIN Shelters ON Dogs.shelterID = Shelters.shelterID' result = execute_query(db_connection, query).fetchall() return render_template('dogs.html', rows=result)
def modifyCustomers(): db_connection = connect_to_database() if request.method == 'GET': query = 'SELECT customerID from Customers' result = execute_query(db_connection, query).fetchall() print(result) return render_template('modifyCustomers.html', rows=result) elif request.method == 'POST': print("Add new customer") email = request.form['email'] firstName = request.form['firstName'] lastName = request.form['lastName'] address = request.form['address'] dob = request.form['dob'] phone = request.form['phone'] city = request.form['city'] state = request.form['state'] zipcode = request.form['zipcode'] query = 'INSERT INTO Customers (email, firstName, lastName, address, dob, phone, city, state, zipcode) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)' data = (email, firstName, lastName, address, dob, phone, city, state, zipcode) execute_query(db_connection, query, data) return redirect(url_for('customers'))
def add_new_dog(): db_connection = connect_to_database() if request.method == 'GET': return render_template('add_dog.html') elif request.method == 'POST': print("Add new dog!") shelterID = '1' name = request.form['dogname'] birthday = request.form['birthday'] breed = request.form['breed'] gender = request.form['doggender'] size = request.form['dogsize'] status = request.form['dogstatus'] energy = request.form['energy'] coat = request.form['coat'] color = request.form['color'] dogsOK = request.form['dogsOK'] catsOK = request.form['catsOK'] kidsOK = request.form['kidsOK'] #query to add dog query = 'INSERT INTO Dogs (shelterID, name, birthday, gender, breed, size, adoption_status, energy_level, coat_type, color, dogs_ok, cats_ok, kids_ok) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' data = (shelterID, name, birthday, gender, breed, size, status, energy, coat, color, dogsOK, catsOK, kidsOK) execute_query(db_connection, query, data) return redirect('/dogs')
def updateOrder(id): print('In the function') db_connection = connect_to_database() #display existing data if request.method == 'GET': print('The GET request') order_query = 'SELECT orderID, customerID, productID, dateOrdered, dateDelivered, totalPrice from Orders WHERE orderID = %s' % ( id) order_result = execute_query(db_connection, order_query).fetchone() if order_result == None: return "No order found!" return render_template('updateOrder.html', order=order_result) elif request.method == 'POST': print('The POST request') orderID = request.form['orderID'] customerID = request.form['customerID'] productID = request.form['productID'] dateOrdered = request.form['dateOrdered'] dateDelivered = request.form['dateDelivered'] totalPrice = request.form['totalPrice'] query = "UPDATE Orders SET customerID = %s, productID = %s, dateOrdered = %s, dateDelivered = %s, totalPrice = %s WHERE orderID = %s" data = (customerID, productID, dateOrdered, dateDelivered, totalPrice, orderID) result = execute_query(db_connection, query, data) return redirect(url_for('orders'))
def update_people(id): db_connection = connect_to_database() #display existing data if request.method == 'GET': people_query = 'SELECT character_id, fname, lname, homeworld, age from bsg_people WHERE character_id = %s' % (id) people_result = execute_query(db_connection, people_query).fetchone() if people_result == None: return "No such person found!" planets_query = 'SELECT planet_id, name from bsg_planets' planets_results = execute_query(db_connection, planets_query).fetchall(); return render_template('people_update.html', planets = planets_results, person = people_result) elif request.method == 'POST': print("Update people!"); character_id = request.form['character_id'] fname = request.form['fname'] lname = request.form['lname'] age = request.form['age'] homeworld = request.form['homeworld'] print(request.form); query = "UPDATE bsg_people SET fname = %s, lname = %s, age = %s, homeworld = %s WHERE character_id = %s" data = (fname, lname, age, homeworld, character_id) result = execute_query(db_connection, query, data) print(str(result.rowcount) + " row(s) updated"); return redirect('/browse_bsg_people')
def updateStore(id): print('In the function') db_connection = connect_to_database() #display existing data if request.method == 'GET': print('The GET request') store_query = 'SELECT storeID, address, city, state, daysOpen, hours FROM Stores WHERE storeID = %s' % ( id) store_result = execute_query(db_connection, store_query).fetchone() if store_result == None: return "No such person found!" return render_template('updateStore.html', store=store_result) elif request.method == 'POST': print('The POST request') storeID = request.form['storeID'] address = request.form['address'] city = request.form['city'] state = request.form['state'] daysOpen = request.form['daysOpen'] hours = request.form['hours'] query = "UPDATE Stores SET address = %s, city = %s, state = %s, daysOpen = %s, hours = %s WHERE storeID = %s" data = (address, city, state, daysOpen, hours, storeID) result = execute_query(db_connection, query, data) return redirect(url_for('stores'))
def add_reservations(): db_connection = connect_to_database() if request.method == 'GET': query = "SELECT memberID, memberFirst, memberLast FROM Members;" result = execute_query(db_connection, query).fetchall() return render_template('reservations_add.html', members = result) elif request.method == 'POST': memberID = request.form['memberID'] isbn = request.form['isbn'] dateIssued = request.form['dateIssued'] dateDue = request.form['dateDue'] isReturned = request.form['isReturned'] # Error handle try: query = ''' INSERT INTO Reservations (memberID, bookID, dateIssued, dateDue, isReturned) VALUES (%s, (SELECT bi.bookID FROM Book_Items AS bi LEFT JOIN Books AS b ON bi.isbn=b.isbn WHERE b.isbn = %s), %s,%s,%s) ''' data = (memberID, isbn, dateIssued, dateDue, isReturned) execute_query(db_connection, query, data) except: print("Invalid reservation input") query = "SELECT memberID, memberFirst, memberLast FROM Members;" result = execute_query(db_connection, query).fetchall() return render_template('reservations_add.html', members = result)
def add_results(): db_connection = connect_to_database() q1 = 'SELECT Orders.orderID, Patients.lastName, Orders.orderType from Orders JOIN Patients ON Orders.patientID = Patients.patientID' r1 = execute_query(db_connection, q1).fetchall() # pre-fill form with order details if request.method == 'GET': return render_template('add_results.html', orders=r1) elif request.method == 'POST': print("Add new Result!") orderID = request.form['orderID'] access = request.form['access'] status = request.form['status'] date = request.form['date'] # check if order already has a result qo = 'SELECT orderID FROM Results WHERE orderID = %s' do = (orderID, ) ro = execute_query(db_connection, qo, do).fetchall() co = len(ro) # cannot add result to order that already has a result (one result per order) if co > 0: flash( "Failed: Result for Order Already Exits! Please Update Result!" ) return render_template('add_results.html', orders=r1) else: query = 'INSERT INTO Results (status, orderID, date, accessedByDoctor) VALUES (%s,%s,%s,%s)' data = (status, orderID, date, int(access)) execute_query(db_connection, query, data) flash('Result Added!') return render_template('add_results.html', orders=r1)
def update_reservations(id): db_connection = connect_to_database() if request.method == 'GET': reservation_query = ''' SELECT DISTINCT r.reservationID, m.memberFirst, m.memberLast, b.isbn, r.dateIssued, r.dateDue, (SELECT IF(isReturned, \'Yes\', \'No\')) FROM Reservations AS r LEFT JOIN Members AS m ON r.memberID=m.memberID LEFT JOIN Book_Items AS bi ON r.bookID=bi.bookID LEFT JOIN Books AS b ON bi.isbn=b.isbn WHERE reservationID = %s ''' % (id) reservation_result = execute_query(db_connection, reservation_query).fetchone() return render_template('reservations_update.html', reservation = reservation_result) elif request.method == 'POST': reservationID = request.form['reservationID'] memberFirst = request.form['memberFirst'] memberLast = request.form['memberLast'] isbn = request.form['isbn'] dateIssued = request.form['dateIssued'] dateDue = request.form['dateDue'] isReturned = request.form['isReturned'] query = ''' UPDATE Reservations SET memberID = (SELECT memberID FROM Members WHERE memberFirst = %s AND memberLast = %s), bookID = (SELECT bi.bookID FROM Book_Items AS bi LEFT JOIN Books AS b ON bi.isbn=b.isbn WHERE bi.isbn = %s LIMIT 1), dateIssued = %s, dateDue = %s, isReturned = %s WHERE reservationID = %s ''' data = (memberFirst, memberLast, isbn, dateIssued, dateDue, isReturned, reservationID) execute_query(db_connection, query, data) return redirect('/reservations_browse')
def add_loci(): """ Route to execute the query to add a locus to the database after user submits the form in the bottom of the view_palace route """ db_connection = connect_to_database() inputs = request.form.to_dict(flat=True) print("Creating a new Locus...") # calls a stored procedure in the database query = "CALL add_loci('{}', '{}', '{}', '{}');".format( inputs['palace_id'], inputs['locus_name'], inputs['digitsA'], inputs['digitsB']) # error handler for mysql try: execute_query(db_connection, query).fetchall() except db_connection.IntegrityError as err: message = 'Could not create Loci.' instruction = 'Loci name must be unique. Error: ' + str(err) context = { 'list_palaces': list_palaces(), 'message': message, 'instruction': instruction } # using createPalace template just to display the error msg. return render_template('createPalace.html', context=context) return redirect("/view/" + inputs['palace_id'])
def select_dealership(dealership_id): db_connection = connect_to_database() search_emp = '' search_veh = '' if request.method == 'POST': if 'search_emp' in request.form: search_emp = request.form['search_emp'] if 'search_veh' in request.form: search_veh = request.form['search_veh'] dealership_query = "SELECT dealership_name, hours, address_line_1, address_line_2, city, zip, country FROM dealership INNER JOIN dealership_address USING (address_id) WHERE dealership_id = %s;" % ( dealership_id) dealership = execute_query(db_connection, dealership_query).fetchall() employees_query = "SELECT num_employees, f_name, l_name, position, employee_id FROM (SELECT COUNT(employee_id) AS num_employees, dealership_id FROM employees WHERE dealership_id = %s) AS tabl1 INNER JOIN (SELECT f_name, l_name, position, employee_id, dealership_id FROM employees WHERE dealership_id = %s) AS tabl2 USING (dealership_id) WHERE INSTR(f_name, '%s') OR INSTR(l_name, '%s');" % ( dealership_id, dealership_id, search_emp, search_emp) employees = execute_query(db_connection, employees_query).fetchall() vehicles_query = "SELECT num_vehicles, vehicle_id, type_name, vin FROM (SELECT vehicle_id, type_name, vin, dealership_id FROM vehicle INNER JOIN vehicle_type ON type=type_id WHERE dealership_id = %s) AS tabl1 INNER JOIN (SELECT COUNT(vehicle_id) AS num_vehicles, dealership_id FROM vehicle WHERE dealership_id = %s) AS tabl2 USING (dealership_id) WHERE INSTR(vin, '%s');" % ( dealership_id, dealership_id, search_veh) vehicles = execute_query(db_connection, vehicles_query).fetchall() types_query = 'SELECT type_name FROM vehicle_type' types = execute_query(db_connection, types_query).fetchall() return render_template('selectDealership.html', employees=employees, vehicles=vehicles, dealership=dealership, dealership_id=dealership_id, types=types)
def view_palace(palace_id=1): """ Route to view a selected palace. Default set to 1 only used if user clicks link in homepage """ db_connection = connect_to_database() # get palace name and description query = "SELECT `name`, `description` FROM `palaces` WHERE `palace_id` ='{}'".format( palace_id) rtn = execute_query(db_connection, query).fetchall() # create context dictionary context = { 'list_palaces': list_palaces(), 'palace_name': rtn[0][0], 'palace_desc': rtn[0][1], 'palace_id': palace_id } # get loci data query = "SELECT loci.loci_id, `name`, `first_chunk_value`, `second_chunk_value`, m_person.person, m_action.action "\ "FROM `loci` JOIN `chunks` ON chunks.loci_id = loci.loci_id "\ "JOIN `chunks_mnemo` ON chunks.chunk_id = chunks_mnemo.chunk_id "\ "JOIN `mnemonics` AS m_person ON chunks_mnemo.first_mnemo = m_person.mnemo_id "\ "JOIN `mnemonics` AS m_action ON chunks_mnemo.second_mnemo = m_action.mnemo_id "\ "WHERE loci.palace_id = '{}' "\ "ORDER BY loci.loci_id;".format(palace_id) rtn = execute_query(db_connection, query).fetchall() context['rows'] = rtn # rows = loci data return render_template('view.html', context=context)
def select_vehicle(dealership_id, vehicle_id): db_connection = connect_to_database() search = '' if request.method == 'POST': search = request.form['search'] dealership_query = "SELECT dealership_name, hours, address_line_1, address_line_2, city, zip, country FROM dealership INNER JOIN dealership_address USING (address_id) WHERE dealership_id = %s;" % ( dealership_id) dealership = execute_query(db_connection, dealership_query).fetchall() num_emp_query = "SELECT COUNT(employee_id) FROM employees WHERE dealership_id = %s" % ( dealership_id) num_emp = execute_query(db_connection, num_emp_query).fetchall() vehicles_query = "SELECT num_vehicles, vehicle_id, type_name, vin FROM (SELECT vehicle_id, type_name, vin, dealership_id FROM vehicle INNER JOIN vehicle_type ON type=type_id WHERE vehicle_id = %s) AS tabl1 INNER JOIN (SELECT COUNT(vehicle_id) AS num_vehicles, dealership_id FROM vehicle WHERE dealership_id = %s) AS tabl2 USING (dealership_id);" % ( vehicle_id, dealership_id) vehicles = execute_query(db_connection, vehicles_query).fetchall() features_query = "SELECT feature_name, feature_value, feature_description, feature_id FROM vehicle INNER JOIN vehicle_feature USING (vehicle_id) INNER JOIN feature USING (feature_id) WHERE vehicle_id = %s AND INSTR(feature_name, '%s')" % ( vehicle_id, search) features = execute_query(db_connection, features_query).fetchall() allfeatures_query = "SELECT feature_id, feature_name, feature_description FROM feature" allfeatures = execute_query(db_connection, allfeatures_query).fetchall() return render_template('selectVehicle.html', dealership=dealership, dealership_id=dealership_id, employees=num_emp, vehicles=vehicles, features=features, allfeatures=allfeatures)
def signin(): '''Allow the user to sign in or register as a new customer.''' # Handle customer registration if request.method == 'POST': db_connection = connect_to_database() # Gather all user data fname = request.form['sign-up-fname-field'] lname = request.form['sign-up-lname-field'] email = request.form['sign-up-email-field'] password = request.form['sign-up-password-field'] phone = request.form['sign-up-phone-number-field'] # Create and execute query to add a new customer to the database query = 'INSERT INTO `customers` (fname, lname, email, password, phone_number) \ VALUES (%s, %s, %s, %s, %s);' customer_data = (fname, lname, email, password, phone) execute_query(db_connection, query, customer_data) # Redirect the user to the home page after registering return redirect(url_for('home')) elif request.method == 'GET': return render_template("signin.html")
def update_list(list_id): """ Display list update form and process any updates using the same function """ db_connection = connect_to_database() # display current data if request.method == 'GET': query = "SELECT * FROM `lists` WHERE `list_id` ='{}'".format( list_id) # get info of list cursor = execute_query(db_connection, query) rtn = cursor.fetchall() cursor.close() context = { 'list_id': rtn[0][0], 'list_name': rtn[0][2], 'list_desc': rtn[0][3] } db_connection.close() # close connection before returning return render_template('update_list.html', context=context) elif request.method == 'POST': query = "UPDATE `lists` SET `name` = %s, `description` = %s WHERE `list_id` = %s" data = (request.form['list_name'], request.form['list_desc'], list_id) cursor = execute_query(db_connection, query, data) cursor.close() db_connection.close() # close connection before returning webapp.logger.info( "Update list. userid: %s, list_id: %s, list_name: %s, list_desc: %s", current_user.id, list_id, request.form['list_name'], request.form['list_desc']) return redirect('/home')