def likesList(conn, pid):
'''lists the users that liked a particular post'''
curs = dbi.dictCursor(conn)
curs.execute('''select profile_id from Likes where post_id=%s''', [pid])
users = curs.fetchall()
if len(users) == 0:
return 0
else:
usersList = []
for user in users:
curs2 = dbi.dictCursor(conn)
id = user['profile_id']
curs2.execute('''select username from Users where uid=%s''', [id])
usersList.append(curs2.fetchone())
return usersList
def selectIncomplete(conn):
'''Returns a list of titles of movies where either the director or release year is null'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select title, tt from movie where director is null or `release` is null'''
)
return curs.fetchall()
def calculateUserFootprint(conn, UID):
'''given a user's UID, get their info from the database and uses the
carbon footprint calculator (calculator.py) to calculate and return
a total footprint'''
#TODO: this works but the numbers seem to be slightly off. look into this more
curs = dbi.dictCursor(conn)
curs.execute(
''' select
miles_flown,
miles_driven,
servings_lamb,
servings_beef,
servings_cheese,
servings_pork,
servings_turkey,
servings_chicken,
laundry
from user where UID = %s
''', [UID])
userData = curs.fetchone()
total = calculator.plane_emissions(userData['miles_flown']) \
+ calculator.car_emissions(userData['miles_driven']) \
+ calculator.meat_emissions(userData['servings_lamb'], \
userData['servings_beef'], userData['servings_cheese'], \
userData['servings_pork'], userData['servings_turkey'], \
userData['servings_chicken']) \
+ calculator.washer_emissions(userData['laundry']) \
+ calculator.dryer_emissions(userData['laundry'])
return total
def getUser(conn, UID):
'''Returns user information, as a dictionary.
'''
curs = dbi.dictCursor(conn)
curs.execute('''select * from user
where UID=%s''', [UID])
return curs.fetchone()
def update(author, price, professor, year, id):
CONN = getConn('textbooks_db')
curs = dbi.dictCursor(CONN)
curs.execute(
'''update books set author=%s, price=%s, professor=%s, year=%s
where id=%s''', [author, price, professor, year, id])
def getAuthor(conn, sid):
'''given an sid, gets the username'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select username from works inner join users using (uid)
where sid=%s''', [sid])
return curs.fetchone()
def getFeedCategory(conn, category):
'''Return info of all posts in feed for given category'''
curs = dbi.dictCursor(conn)
curs.execute(
'select * from post where category = %s order by dateCreated DESC',
[category])
return curs.fetchall()
def insertPic(conn, pid, filename):
'''inserts a photo into the database paired with the id of the place the image is of'''
curs = dbi.dictCursor(conn)
curs.execute(
'''insert into pic(pid, filename) values (%s, %s)
on duplicate key update filename = %s''',
[pid, filename, filename])
def checkIsBookmarked(conn, uid, pid):
'''Given post id and user id, check if user already bookmarked post
Returns true if post is already bookmarked'''
curs = dbi.dictCursor(conn)
curs.execute('select * from bookmark where uid = %s and pid = %s',
[uid, pid])
return len(curs.fetchall()) != 0
def getInterestedIn(conn, uid):
'''Returns items the given user is interested in'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select * from item where iid in
(select iid from interested where uid = %s)''', [uid])
return curs.fetchall()
def checkIsInterestedIn(conn, uid, iid):
'''Given item id and user id, check if user already marked item as interested in
Returns true if user is already interested in item'''
curs = dbi.dictCursor(conn)
curs.execute('select * from interested where uid = %s and iid = %s',
[uid, iid])
return len(curs.fetchall()) != 0
def searchBook(search_term):
curs = dbi.dictCursor(CONN)
curs.execute('''select * from S_books where title like %s''',
['%' + search_term + '%'])
return curs.fetchall()
def searchWorks(conn, kind, searchterm, filters):
'''finds works with title including searchterm or tag = searchterm
takes chosen filters into acct'''
curs = dbi.dictCursor(conn)
dofilter = ("where sid not in (select sid from taglink where tid in %s)"
if filters else "")
searchParam = (['%' + searchterm +
'%'] if kind == "work" else [searchterm])
params = ([searchParam, filters] if filters else [searchParam])
if kind == "work":
curs.execute(
'''select * from (select sid, uid, title, updated,
summary, stars, wip, count(sid) from
(select * from works where title like %s) as q1
left outer join chapters using(sid) group by sid) as q2
left outer join (select uid, username from users) as q3
using(uid)''' + dofilter, params)
else:
curs.execute(
'''select * from (select sid, uid, title, updated,
summary, stars, wip, count(sid) from
(select tid from tags where tname = %s) as q1
left outer join taglink using(tid)
left outer join works using(sid)
left outer join chapters using(sid) group by sid) as q3
left outer join (select uid, username from users) as q4
using(uid)''' + dofilter, params)
return curs.fetchall()
def calcAvgRating(conn, sid):
curs = dbi.dictCursor(conn)
curs.execute(
'''select avg(rating) from ratings
inner join works using(sid)
where sid=%s''', [sid])
return curs.fetchone()
def searchWorks(conn, kind, searchterm):
'''finds works with title including searchterm or tag = searchterm'''
curs = dbi.dictCursor(conn)
if kind == "work":
curs.execute(
''' select * from
(select sid, uid, title, updated,
summary, stars, count(sid) from
(select * from works where title like %s)
as q1 left outer join chapters using(sid) group by sid)
as q2 left outer join
(select uid, username from users) as q3 using(uid)''',
['%' + searchterm + '%'])
else:
curs.execute(
'''select * from (select sid, uid, title, updated,
summary, stars, count(sid) from
(select tid from tags where tname = %s) as q1
left outer join (select tid, sid from taglink) as q2
using(tid)
left outer join works using(sid)
left outer join chapters using(sid) group by sid) as q3
left outer join (select uid, username from users) as q4
using(uid)''', [searchterm])
return curs.fetchall()
def editAvailability(conn, aid, newAvailability):
'''updates the availability with the new information stored in dict'''
curs = dbi.dictCursor(conn)
curs.execute('''update availability set start=%s, end=%s where aid=%s''',
[newAvailability['start'], newAvailability['end'], aid])
curs.execute('''select pid from availability where aid=%s''', [aid])
return curs.fetchone()
def uploadProfilePic(pathname, username):
CONN = getConn('textbooks_db')
curs = dbi.dictCursor(CONN)
curs.execute(
'''update users set pic=%s
where username=%s''', [pathname, username])
def searchAuthors(conn, author):
'''finds authors matching name'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select uid, username from users where
username like %s''', ['%' + author + '%'])
return curs.fetchall()
def updateBio(bio, username):
CONN = getConn('textbooks_db')
curs = dbi.dictCursor(CONN)
curs.execute(
'''update users set bio=%s
where username=%s''', [bio, username])
def getStory(conn, sid):
'''Returns a work with given sid'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select * from works inner join users
on users.uid=works.uid where sid=%s''', [sid])
return curs.fetchone()
def getSinglePost(conn, rid):
curs = dbi.dictCursor(conn)
curs.execute(
'''select rmID, building, rating, review, imgPath, time
from Reviews inner join Users on Users.uid=Reviews.uid where Reviews.rid = %s''',
[rid])
return curs.fetchone()
def followingUsers(conn, uid):
'''lists the users who are the current profile is following'''
curs = dbi.dictCursor(conn)
curs.execute('''select followee_id from Follows where follower_id=%s''', [uid])
following = curs.fetchall()
if len(following) == 0:
return 0
else:
usersList = []
for user in following:
curs2 = dbi.dictCursor(conn)
id = user['followee_id']
curs2.execute('''select username from Users where uid=%s''', [id])
usersList.append(curs2.fetchone())
return usersList
def getComments(conn, uid, cid):
curs = dbi.dictCursor(conn)
curs.execute(
'''select reviewText from reviews inner join reviewCredits using(rid)
where commenter=%s and cid=%s
''', [uid, cid])
return curs.fetchall()
def changeHelpful(conn, rid, helpful):
'''sets a review as helpful or not helpful'''
curs = dbi.dictCursor(conn)
curs.execute('lock tables reviews write')
curs.execute('''update reviews set ishelpful=%s where rid=%s''',
[helpful, rid])
curs.execute('unlock tables')
def calcAvgRating(conn, sid):
'''calculates the average rating of a story'''
curs = dbi.dictCursor(conn)
curs.execute(
'''select avg(rating) from ratings
inner join works using(sid)
where sid=%s''', [sid])
return curs.fetchone()
def updateProfile(conn, uid, dob):
curs = dbi.dictCursor(conn)
curs.execute('lock tables users write')
curs.execute(
'''update users
set dob=%s
where uid=%s''', [dob, uid])
curs.execute('unlock tables')
def removeBookmark(conn, sid, uid):
'''removes a bookmark'''
curs = dbi.dictCursor(conn)
curs.execute('lock tables bookmarks write')
curs.execute(
'''delete from bookmarks
where sid=%s and uid=%s''', [sid, uid])
curs.execute('unlock tables')
def addBookmark(conn, sid, uid):
'''adds a bookmark'''
curs = dbi.dictCursor(conn)
curs.execute('lock tables bookmarks write')
curs.execute(
'''insert into bookmarks(sid, uid)
values (%s,%s)''', [sid, uid])
curs.execute('unlock tables')
def getLogin(conn, username):
'''gets hashed password to check for login'''
curs = dbi.dictCursor(conn)
curs.execute(
'''SELECT uid,passhash
FROM users
WHERE username = %s''', [username])
return curs.fetchone()
def updateAvgRating(conn, sid, avg):
'''updates the average rating for a story'''
curs = dbi.dictCursor(conn)
curs.execute('lock tables works write')
curs.execute(
'''update works set avgRating=%s
where sid=%s''', [avg, sid])
curs.execute('unlock tables')
