def add_username(conn, name, username, password, favoriteDH, classYear):
curs = dbi.dict_cursor(conn)
curs2 = dbi.dict_cursor(conn)
curs.execute(
'INSERT INTO student(username, name, favoriteDH, favoriteFood, classYear, password) \
VALUES (%s, %s, %s, %s, %s, %s);',
[username, name, favoriteDH, 1, classYear, password])
conn.commit()
def getPeople(conn):
'''Returns the name and birthdate of all the entries in
the person table, as a list of dictionaries.
'''
curs = dbi.dict_cursor(conn)
curs.execute('select name,birthdate from person')
return curs.fetchall()
def insertCustomer(cname,conn):
curs=dbi.dict_cursor(conn)
curs.execute('''insert into parents(p_name) values(%s)''',cname)
conn.commit()
searched="%"+cname+"%"
curs.execute('''select person_id from parents where p_name like %s''',searched)
return curs.fetchone()
def getRecommended():
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute('''SELECT course.cid, course.title
FROM course LIMIT 3''')
results = curs.fetchall()
return results
def addtolist(conn,username,person_id):
curs=dbi.dict_cursor(conn)
curs.execute('''select name from politicians where person_id=%s''',(person_id))
name=curs.fetchone()
name=name['name']
curs.execute('''insert into user_favs(username,person_id,name,feeling) values (%s,%s,%s,%s)''', (username,person_id,name,'null'))
conn.commit()
def addpos(info,conn):
curs=dbi.dict_cursor(conn)
curs.execute('''select * from offices where heldby=%s''',(info["person_id"]))
temp = curs.fetchone()
info['offname'] = temp['oname']
info['offlink'] = url_for('office', num = temp['o_id'])
return info
def getByCompany(conn, compName):
'''Returns the link, cid, uid, role, season, experience, city, state, and country
of all applications for a specified company, as a list of dictionaries.'''
curs = dbi.dict_cursor(conn)
curs.execute('''select * from application
where compName = %s;''', [compName])
return curs.fetchall()
def loginA():
try:
username = request.form['username']
passwd = request.form['password']
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute(
'''SELECT username,password
FROM admin
WHERE username = %s''', [username])
row = curs.fetchone()
if row is None:
# Same response as wrong password,
# so no information about what went wrong
flash('login incorrect. Try again or join')
return redirect(url_for('index'))
hashed = row['password']
hashed2 = bcrypt.hashpw(passwd.encode('utf-8'), hashed.encode('utf-8'))
hashed2_str = hashed2.decode('utf-8')
if hashed2_str == hashed:
flash('successfully logged in as ' + username)
session['username'] = username
session['logged_in'] = True
session['visits'] = 1
return redirect(url_for('user', username=username))
else:
flash('login incorrect. Try again or join')
return redirect(url_for('index'))
except Exception as err:
flash('form submission error ' + str(err))
return redirect(url_for('index'))
def upload():
if request.method == 'POST': #if they are uploading a new picture
try:
username2 = session['username']
f = request.files['pic']
user_filename = f.filename
ext = user_filename.split('.')[-1]
filename = secure_filename('{}.{}'.format(username2, ext))
pathname = os.path.join(app.config['UPLOADS'], filename)
f.save(pathname)
curs = dbi.dict_cursor(conn)
curs.execute(
'''insert into picfile1(username,filename) values (%s,%s)
on duplicate key update filename = %s''',
[username2, filename, filename])
conn.commit()
flash('Upload successful')
return redirect(url_for('profile'))
except Exception as err:
flash('Upload failed {why}'.format(why=err))
return render_template('profile.html',
src='',
nm='',
title="Profile")
def getFavorites(conn, uid):
# Gets list of all favorited internships
curs = dbi.dict_cursor(conn)
sql = '''select link,compName,role,season,yr,experience
from application inner join favorites using (link) where favorites.uid = %s;'''
curs.execute(sql, [uid])
return curs.fetchall()
def lookupShift(conn, shift_id):
curs = dbi.dict_cursor(conn)
sql = 'select * from shift1 where shift_id = %s'
vals = [shift_id]
curs.execute(sql, vals)
info = curs.fetchone()
return info
def commit_transaction(conn):
'''
End the transaction
:param conn: connection to database
'''
curs = dbi.dict_cursor(conn)
curs.execute('commit')
def shiftExists(conn, permanent, day, time, endtime, employee):
curs = dbi.dict_cursor(conn)
curs.execute(
'''select * from shift1 where day = %s and time = %s and endtime = %s and employee = %s''',
(day, time, endtime, employee))
data = curs.fetchall()
return data
def start_transaction(conn):
'''
Begin the transaction to ensure thread safety
:param conn: connection to database
'''
curs = dbi.dict_cursor(conn)
curs.execute('start transaction')
def getAllWeapons(conn):
'''
Returns the wid, type, and condition of all weapons
'''
curs = dbi.dict_cursor(conn)
curs.execute('''select wid,type,`condition` from weapons''')
return curs.fetchall()
def getByExperience(conn, exp):
# Returns the link, cid, uid, role, season, experience, city, state, and country
# of all applications needing specified experience/year, as a list of dictionaries.
curs = dbi.dict_cursor(conn)
curs.execute('''select * from application
where experience like %s;''', ['%' + exp+ '%'])
return curs.fetchall()
def getByRole(conn, role):
# Returns the link, cid, uid, role, season, experience, city, state, and country
# of all applications for a specified role, as a list of dictionaries.
curs = dbi.dict_cursor(conn)
curs.execute('''select * from application
where role = %s;''', [role])
return curs.fetchall()
def findAllAvailabilities(conn):
curs = dbi.dict_cursor(conn)
curs.execute(
'''select * from person_availability order by day asc, time asc, endtime desc'''
)
info = curs.fetchall()
return info
def login():
try:
username = request.form['username']
passwd = request.form['password']
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute(
'''SELECT uid,hashed
FROM userpass
WHERE username = %s''', [username])
row = curs.fetchone()
if row is None:
# Same response as wrong password,
# so no information about what went wrong
flash('Login incorrect. Try again or create account')
return redirect(url_for('index'))
hashed = row['hashed']
print('database has hashed: {} {}'.format(hashed, type(hashed)))
print('form supplied passwd: {} {}'.format(passwd, type(passwd)))
hashed2 = bcrypt.hashpw(passwd.encode('utf-8'), hashed.encode('utf-8'))
hashed2_str = hashed2.decode('utf-8')
print('rehash is: {} {}'.format(hashed2_str, type(hashed2_str)))
if hashed2_str == hashed:
print('Passwords match!')
flash('Successfully logged in as ' + username)
session['username'] = username
session['uid'] = row['uid']
session['logged_in'] = True
return redirect(url_for('user', username=username))
else:
flash('Login incorrect. Try again or join')
return redirect(url_for('index'))
except Exception as err:
flash('Form submission error ' + str(err))
return redirect(url_for('index'))
def deletePost(conn, postID):
'''Deletes post with specified postID.'''
curs = dbi.dict_cursor(conn)
curs.execute('''
DELETE from post WHERE postID=%s
''', [postID])
conn.commit()
def getSpecEmployeeShifts(conn, employee_id):
curs = dbi.dict_cursor(conn)
curs.execute(
'''select * from shift1 where employee = %s order by day asc, time asc, endtime''',
[employee_id])
info = curs.fetchall()
return info
def lookupEmployee(conn, username):
curs = dbi.dict_cursor(conn)
sql = 'select * from employee1 where username = %s'
vals = [username]
curs.execute(sql, vals)
info = curs.fetchone()
return info
def get_genres(conn):
'''
Gets all the genres (for both songs and playlists).
:param conn: connection to database
:returns: a list of all the genres in the database
'''
curs = dbi.dict_cursor(conn)
curs.execute('''select distinct genre from coda_song union (
select distinct playlist_genre from coda_playlist)''')
genreDictList = curs.fetchall()
genresDB = [
genreDict['genre'] for genreDict in genreDictList
if genreDict['genre'].strip() != ''
] # don't include empty genres
# collect distinct genre names
genres = []
for genre in genresDB:
# some songs/playlists have multiple genres, separated by | or ,
# separate genres and strip any leading/trailing whitespace
genres += [
oneGenre.strip().lower() for oneGenre in re.split('\||,', genre)
if oneGenre.strip().lower() not in genres
]
return sorted(genres)
def login():
if request.method == 'GET':
return render_template(
'create.html'
)
# form gets user inputs and stores into variables
else:
username = request.form['username']
password = request.form['password']
conn = dbi.connect()
# helper function checks to make sure username exists in database
if query.username_exists(conn, username):
curs = dbi.dict_cursor(conn)
# query finds password saved in database to compare with user input
curs.execute ('''select username, password
from student
where username = %s''', [username])
user = curs.fetchone()
# checks if user input matches password on file
check_pass = user['password']
if check_pass == password:
flash('Successfully logged in.')
# print(check_pass, password)
return redirect(url_for('profile', username=username))
else:
flash('Incorrect login. Please try again.')
return redirect(url_for('login'))
# if username doesn't exist in database, user is let known
else:
flash('This username does not exist. Please create an account.')
return redirect(url_for('create'))
def available(conn):
curs = dbi.dict_cursor(conn)
curs.execute(
'''select coverage.request_id, coverage.req_employee, shift1.time, shift1.day, shift1.endtime, coverage.shift
from coverage, shift1
where coverage.shift = shift1.shift_id AND coverage.covered = 0''')
info = curs.fetchall()
return info
def lookupFoodItem(fid):
'''
return dictionary of a food's name, type, description, preference, label given an id
'''
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute("select name, ingredients, preference, allergen, type from food inner join labels using (fid) where fid = %s;", [fid])
return curs.fetchone()
def addFavorite(conn, bNum, cid):
'''adds a course to a students "favorites"'''
curs = dbi.dict_cursor(conn)
query = curs.execute(
'''
INSERT INTO favorites(bNum, cid)
VALUES (%s, %s)''', [bNum, cid])
conn.commit()
def updateFoodItem(fid, ingredients):
'''
edit food item and commit changes
'''
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute("update labels set ingredients = %s where fid = %s;", [ingredients, fid])
conn.commit()
def getRecommended():
'''Gets recommended courses to display on the home page'''
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute('''SELECT course.cid, course.title
FROM course LIMIT 3''')
results = curs.fetchall()
return results
def lookupComments(fid):
'''
return a list of dictionaries for each comment for a given food item and with the comment's rating and user
'''
conn = dbi.connect()
curs = dbi.dict_cursor(conn)
curs.execute("select username, rating, comment from feedback where fid = %s;", [fid])
return curs.fetchall()