def get_works(): query = '''SELECT work.WorkID, work.Title, work.Content, work.AuthorID, work.DynastyID, author.Author, dynasty.Dynasty\n FROM work, author, dynasty\n WHERE work.AuthorID = author.AuthorID\n AND work.DynastyID = dynasty.DynastyID''' cursor.execute(query) return cursor.fetchall()
def get_users_love_work(work_id):
query = '''SELECT user.UserID, user.Name, user.Avatar\n
FROM love, user\n
WHERE love.WorkID = %d\n
AND love.UserID = user.UserID\n''' % work_id
cursor.execute(query)
return cursor.fetchall()
def get_comments_by_review(review_id):
query = '''SELECT comment.CommentID, comment.Comment, comment.Time, user.UserID, user.Name, user.Avatar\n
FROM comment, user\n
WHERE comment.UserID = user.UserID
AND comment.ReviewID = %d''' % review_id
cursor.execute(query)
return cursor.fetchall()
def get_users_love_work(work_id): query = '''SELECT user.UserID, user.Name, user.Avatar\n FROM love, user\n WHERE love.WorkID = %d\n AND love.UserID = user.UserID\n''' % work_id cursor.execute(query) return cursor.fetchall()
def get_hot_reviews():
query = '''SELECT review.ReviewID, review.Title, review.Content, review.Time, user.UserID, user.Name, user.Avatar, work.WorkID, work.Title AS WorkTitle, work.Content AS WorkContent, author.Author\n
FROM review, user, work, author\n
WHERE review.UserID = user.UserID\n
AND review.WorkID = work.WorkID\n
AND work.AuthorID = author.AuthorID\n'''
cursor.execute(query)
return cursor.fetchall()
def get_authors_by_random(authorsNum): query = '''SELECT *\n FROM author, dynasty\n WHERE author.DynastyID = dynasty.DynastyID\n ORDER BY RAND()\n LIMIT %d''' % authorsNum cursor.execute(query) return cursor.fetchall()
def get_authors_by_random(authorsNum):
query = '''SELECT *\n
FROM author, dynasty\n
WHERE author.DynastyID = dynasty.DynastyID\n
ORDER BY RAND()\n
LIMIT %d''' % authorsNum
cursor.execute(query)
return cursor.fetchall()
def get_hot_reviews(): query = '''SELECT review.ReviewID, review.Title, review.Content, review.Time, user.UserID, user.Name, user.Avatar, work.WorkID, work.Title AS WorkTitle, work.Content AS WorkContent, author.Author\n FROM review, user, work, author\n WHERE review.UserID = user.UserID\n AND review.WorkID = work.WorkID\n AND work.AuthorID = author.AuthorID\n''' cursor.execute(query) return cursor.fetchall()
def get_works_by_user_love(user_id): query = '''SELECT work.WorkID, work.Title, work.Content, author.AuthorID, author.Author\n FROM love, user, work, author\n WHERE love.UserID = %d\n AND love.UserID = user.UserID\n AND love.WorkID = work.WorkID\n AND work.AuthorID = author.AuthorID''' % user_id cursor.execute(query) return cursor.fetchall()
def get_works_by_random(worksNum): query = '''SELECT work.WorkID, work.Title, work.Content, work.AuthorID, work.DynastyID, author.Author, dynasty.Dynasty\n FROM work, author, dynasty\n WHERE work.AuthorID = author.AuthorID\n AND work.DynastyID = dynasty.DynastyID\n ORDER BY RAND()\n LIMIT %d''' % worksNum cursor.execute(query) return cursor.fetchall()
def get_works_by_user_love(user_id):
query = '''SELECT work.WorkID, work.Title, work.Content, author.AuthorID, author.Author\n
FROM love, user, work, author\n
WHERE love.UserID = %d\n
AND love.UserID = user.UserID\n
AND love.WorkID = work.WorkID\n
AND work.AuthorID = author.AuthorID''' % user_id
cursor.execute(query)
return cursor.fetchall()
def get_reviews_by_random(reviews_num):
query = '''SELECT review.ReviewID, review.Title, review.Content, review.Time, user.UserID, user.Name, user.Avatar, work.WorkID, work.Title AS WorkTitle, work.Content AS WorkContent, author.Author\n
FROM review, user, work, author\n
WHERE review.UserID = user.UserID\n
AND review.WorkID = work.WorkID\n
AND work.AuthorID = author.AuthorID\n
ORDER BY RAND()\n
LIMIT %d''' % reviews_num
cursor.execute(query)
return cursor.fetchall()
def get_reviews_by_random(reviews_num): query = '''SELECT review.ReviewID, review.Title, review.Content, review.Time, user.UserID, user.Name, user.Avatar, work.WorkID, work.Title AS WorkTitle, work.Content AS WorkContent, author.Author\n FROM review, user, work, author\n WHERE review.UserID = user.UserID\n AND review.WorkID = work.WorkID\n AND work.AuthorID = author.AuthorID\n ORDER BY RAND()\n LIMIT %d''' % reviews_num cursor.execute(query) return cursor.fetchall()
def get_comments_by_review(review_id):
query = (
"""SELECT comment.CommentID, comment.Comment, comment.Time, user.UserID, user.Name, user.Avatar\n
FROM comment, user\n
WHERE comment.UserID = user.UserID
AND comment.ReviewID = %d"""
% review_id
)
cursor.execute(query)
return cursor.fetchall()
def get_authors_by_name(name):
query = "SELECT AuthorID, Author FROM author WHERE Author LIKE '%%%s%%'" % name
cursor.execute(query)
return cursor.fetchall()
def get_authors_by_dynasty(dynastyID): query = '''SELECT *\n FROM author\n WHERE author.DynastyID = %d''' % dynastyID cursor.execute(query) return cursor.fetchall()
def get_authors():
query = '''SELECT *\n
FROM author, dynasty\n
WHERE author.DynastyID = dynasty.DynastyID'''
cursor.execute(query)
return cursor.fetchall()
def get_authors_by_dynasty(dynastyID):
query = '''SELECT *\n
FROM author\n
WHERE author.DynastyID = %d''' % dynastyID
cursor.execute(query)
return cursor.fetchall()
def get_works_by_collection(collectionID): query = "SELECT * FROM work WHERE CollectionID = %d" % collectionID cursor.execute(query) return cursor.fetchall()
def get_authors_by_name(name): query = "SELECT AuthorID, Author FROM author WHERE Author LIKE '%%%s%%'" % name cursor.execute(query) return cursor.fetchall()
def get_works_by_author(authorID): query = "SELECT * FROM work WHERE AuthorID=%d" % authorID cursor.execute(query) return cursor.fetchall()
def get_dynasties(): query = "SELECT * FROM dynasty ORDER BY StartYear ASC"; cursor.execute(query) return cursor.fetchall()
def get_collections_by_author(authorID):
query = "SELECT * FROM collection WHERE AuthorID = %d" % authorID
cursor.execute(query)
return cursor.fetchall()
def get_products(num): query = "SELECT * FROM product LIMIT %d" % num cursor.execute(query) return cursor.fetchall()
def get_authors(): query = '''SELECT *\n FROM author, dynasty\n WHERE author.DynastyID = dynasty.DynastyID''' cursor.execute(query) return cursor.fetchall()
def get_widgets(target_type, target_id): query = "SELECT * FROM widget WHERE Type = '%s' AND TargetID = %d ORDER BY PositionIndex ASC" % (target_type, target_id) cursor.execute(query) return cursor.fetchall()
def get_collections_by_author(authorID): query = "SELECT * FROM collection WHERE AuthorID = %d" % authorID cursor.execute(query) return cursor.fetchall()