def insertCompany(compName): # Given a company name, inserts it into the company table conn = dbi.connect() curs = dbi.cursor(conn) curs.execute('''INSERT INTO company(compName) values (%s);''', [compName]) conn.commit()
def index(): conn = dbi.connect() curs = dbi.cursor(conn) internships = sqlHelper.getInternships(conn) return render_template('main.html', internships = internships)
def removeFavorite(uid, link): # Removes application from users' list of favorites''' conn = dbi.connect() curs = dbi.cursor(conn) sql = '''delete from favorites where uid = %s and link = %s''' curs.execute(sql, [uid, link]) conn.commit()
def register(conn, username, password, email, school): '''Insert movie into database with tt, title, and release year.''' conn = dbi.connect() curs = dbi.cursor(conn) sql = '''insert into user (uid, password1, email, school) values(%s, %s, %s, %s);''' curs.execute(sql, [username, password, email, school]) conn.commit()
def deleteByNM(conn, nm): '''Deletes the person with the given NM. Returns number of affected rows.''' curs = dbi.cursor(conn) nr = curs.execute('''delete from person where nm = %s''' % (nm)) conn.commit() return nr
def deleteReview(uid, compName): #Given the uid and compName, deletes a review from the database. conn = dbi.connect() curs = dbi.cursor(conn) curs.execute('''delete from review where uid = %s and compName = %s;''', [uid, compName]) conn.commit()
def join(): try: username = request.form['username'] passwd1 = request.form['password1'] passwd2 = request.form['password2'] if passwd1 != passwd2: flash('Passwords do not match') return redirect(url_for('index')) hashed = bcrypt.hashpw(passwd1.encode('utf-8'), bcrypt.gensalt()) hashed_str = hashed.decode('utf-8') print(passwd1, type(passwd1), hashed, hashed_str) conn = dbi.connect() curs = dbi.cursor(conn) try: curs.execute( '''INSERT INTO userpass(uid,username,hashed) VALUES(null,%s,%s)''', [username, hashed_str]) conn.commit() except Exception as err: flash('That username is taken: {}'.format(repr(err))) return redirect(url_for('index')) curs.execute('select last_insert_id()') row = curs.fetchone() uid = row[0] flash('You were issued UID {}'.format(uid)) session['username'] = username session['uid'] = uid session['logged_in'] = True return redirect(url_for('user', username=username)) except Exception as err: flash('Form Submission Error ' + str(err)) return redirect(url_for('index'))
def join(): try: username = request.form['username'] passwd1 = request.form['password1'] passwd2 = request.form['password2'] name = request.form['name'] email = request.form['email'] if passwd1 != passwd2: flash('passwords do not match') return redirect(url_for('index')) hashed = bcrypt.hashpw(passwd1.encode('utf-8'), bcrypt.gensalt()) hashed_str = hashed.decode('utf-8') conn = dbi.connect() curs = dbi.cursor(conn) try: curs.execute( '''INSERT INTO employee1(username,password,name,email) VALUES(%s,%s,%s,%s)''', [username, hashed_str, name, email]) conn.commit() except Exception as err: flash('That username is taken: {}'.format(repr(err))) return redirect(url_for('index')) session['username'] = username session['logged_in'] = True session['visits'] = 1 return redirect(url_for('user', username=username)) except Exception as err: flash('form submission error ' + str(err)) return redirect(url_for('index'))
def index(): '''Displays home page with most recent database.''' conn = dbi.connect() curs = dbi.cursor(conn) internships = sqlHelper.getInternships(conn) total = sqlHelper.getTotal(conn)['count(*)'] return render_template('main.html', internships=internships, total=total)
def insert(): if request.method == 'GET': return render_template('dataentry1.html', action=url_for('insert')) elif request.method == 'POST': food_name = request.form.get('food-name') food_date = request.form.get('food-date') food_category = request.form.get('food-type') food_dhall = request.form.get('food-hall') food_id = request.form.get('food-id') error_messages = [] message = handleErrors(food_name, food_date, food_category, food_dhall, food_id) error_messages.append(message) if len(error_messages) > 0: render_template('insert.html', messages=error_messages, title='Add Food!') flash('form submission successful') #insert stuff into database connect = dbi.connect() curs = dbi.cursor(connect) sql = '''insert food(fid,name,lastServed,type,did) values (%s,%s,%s,%s,%s);''' vals = [food_id, food_name, food_date, food_category, food_dhall] curs.execute(sql, vals) connect.commit() success_message = "Food {fname} inserted".format(fname=food_name) print(success_message) return redirect(url_for('insert', messages=success_message))
def handleFavorite(uid, link): # Adds application to users' list of favorites, or removes if needed conn = dbi.connect() curs = dbi.cursor(conn) curs.execute('''insert into favorites(uid, link) values (%s, %s);''', [uid, link]) conn.commit()
def isEboard(conn, username): ''' Returns True if there is an eboard member with the specified username, False otherwise ''' curs = dbi.cursor(conn) res = curs.execute('''select username from eboard where username=%s''', [username]) return res > 0
def lookupLastServed(fid): ''' return tuple of a food's last served location, date last served ''' conn = dbi.connect() curs = dbi.cursor(conn) curs.execute("select diningHall.name, lastServed from food inner join diningHall using (did) where fid = %s;", [fid]) return curs.fetchone()
def insertReview(uid, compName, reviewText): #Given the uid, compName, and review, inserts a review into the database. conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( '''insert into review(uid, compName, reviewText) values (%s, %s, %s);''', [uid, compName, reviewText]) conn.commit()
def numSections(conn, query, kind): curs = dbi.cursor(conn) if (kind == "title" or kind == "dep" or kind == "cnum" or kind == "prof"): curs.execute( '''SELECT count(*) FROM course WHERE {} like %s'''.format(kind), ['%' + query + '%']) num = curs.fetchone() return num[0]
def companyExists(compName): # Given a company name, checks if it's already in the company table, # returns a boolean conn = dbi.connect() curs = dbi.cursor(conn) curs.execute('''select count(*) from company where compName = %s;''',[compName]) result = curs.fetchone() return result[0]==1
def insertApplication(link,compName,uid,role,season,year,experience): #add uid to this once we implement login # Given the link, compName, role, season, yr, experience, inserts an # application into the database. conn = dbi.connect() curs = dbi.cursor(conn) curs.execute('''insert into application(link,compName, uid, role,season,yr,experience) values (%s, %s, %s, %s, %s, %s, %s);''', [link, compName, uid, role, season, year, experience]) conn.commit()
def insertStudent(val): print(val) conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( ''' INSERT into student(bNum, name, major, email) VALUES(%s, %s, %s, %s)''', val) conn.commit()
def insertCourse(val): conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( ''' INSERT into course(title, dep, cnum, crn, web, yr, sem, prof) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)''', val) conn.commit() return [val[0], val[5], val[6], val[7]]
def insertStudent(val): '''Takes all student info as a parameter and uses it to insert the student into the database''' conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( ''' INSERT into student(bNum, name, major, email) VALUES(%s, %s, %s, %s)''', val) conn.commit()
def checkin(conn, wid, username, checkoutdate, checkindate, weaponCondition): ''' Update the checkout request with the checkin date ''' curs = dbi.cursor(conn) curs.execute( ''' update checkedout set checkindate=%s where wid=%s and username=%s and checkoutdate=%s''', [checkindate, wid, username, checkoutdate]) conn.commit() curs2 = dbi.cursor(conn) curs2.execute( ''' update weapons set `condition`=%s where wid=%s''', [weaponCondition, wid]) conn.commit()
def insert_picfile(conn, path, name, album_id): '''Insert name into the picfile table under key album_id.''' curs = dbi.cursor(conn) try: curs.execute('''insert into picfile(album_id,filename) values (%s,%s) on duplicate key update filename = %s''', [album_id,name,name]) conn.commit() except Exception as err: print('Exception on insert of {}: {}'.format(name, repr(err)))
def numSections(conn, query, kind): '''Input: user query and kind. Output: number of sections fitting that query''' curs = dbi.cursor(conn) if (kind in ["title", "dep", "cnum", "prof"]): curs.execute( '''SELECT count(*) FROM course WHERE {} like %s'''.format(kind), ['%' + query + '%']) num = curs.fetchone() return num[0]
def personInsert(conn, nm, name, birthdate=None): '''Inserts the given person''' curs = dbi.cursor(conn) # nr is number of rows affected. Should be 1 nr = curs.execute( ''' insert into person(nm,name,birthdate,addedby) values (%s,%s,%s,%s)''', [nm, name, birthdate, STAFFID]) conn.commit() return nr
def isFavorite(conn, uid, link): # Checks if a link is a favorite curs = dbi.cursor(conn) sql = '''select * from favorites where uid = %s and link = %s''' curs.execute(sql, [uid, link]) result = curs.fetchone() if result == None: return False else: return True
def insertCourse(conn, val): '''Takes all course info as a parameter and uses it to insert the given course into the database''' curs = dbi.cursor(conn) curs.execute( ''' INSERT into course(title, dep, cnum, crn, web, yr, sem, prof) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)''', val) conn.commit() curs.execute('SELECT last_insert_id()') row = curs.fetchone() return row[0]
def isCourseNew(conn, title, professor, semester, year): '''Sees if a course with the same title, professor, semester, and year is already in the databases''' curs = dbi.cursor(conn) curs.execute( 'SELECT cid from course where title = %s and prof = %s and sem = %s and yr = %s', [title, professor, semester, year]) row = curs.fetchone() if row != None: return False return True
def getStudentFromName(name): '''Returns all information about the student given the bNum''' conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( ''' SELECT bNum, name, major, email FROM student WHERE name = %s''', [name]) student = curs.fetchone() return student
def getStudent(bNum): '''Returns all information about the student given the bNum''' conn = dbi.connect() curs = dbi.cursor(conn) curs.execute( ''' SELECT * FROM student WHERE bNum = %s''', [bNum]) student = curs.fetchone() return student
def is_username_unique(conn, username): # Checks if username is unique while user is registering. curs = dbi.cursor(conn) sql = '''select * from user where uid = %s;''' result = curs.execute(sql, [username]) conn.commit() if result == None: #username doesn't exist in db, so we can continue with registration return False else: return True