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
