def get_author(authorID): query = '''SELECT *\n FROM author, dynasty\n WHERE author.DynastyID = dynasty.DynastyID\n AND author.AuthorID = %d''' % authorID cursor.execute(query) return cursor.fetchone()
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_collection(collectionID):
query = '''SELECT * FROM collection, author, dynasty\n
WHERE collection.AuthorID = author.AuthorID\n
AND author.DynastyID = dynasty.dynastyID
AND collectionID = %d''' % collectionID
cursor.execute(query)
return cursor.fetchone()
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_author(authorID):
query = '''SELECT *\n
FROM author, dynasty\n
WHERE author.DynastyID = dynasty.DynastyID\n
AND author.AuthorID = %d''' % authorID
cursor.execute(query)
return cursor.fetchone()
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_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_collection(collectionID): query = '''SELECT * FROM collection, author, dynasty\n WHERE collection.AuthorID = author.AuthorID\n AND author.DynastyID = dynasty.dynastyID AND collectionID = %d''' % collectionID cursor.execute(query) return cursor.fetchone()
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_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 add_author(author, quote, introduction, birthYear, deathYear,
dynastyID):
query = '''INSERT INTO author (Author, Quote, Introduction, BirthYear, DeathYear, DynastyID) VALUES\n
('%s', '%s', '%s', %d, %d, %d)''' % (author, quote, introduction, birthYear,
deathYear, dynastyID)
cursor.execute(query)
conn.commit()
return cursor.lastrowid
def edit_author(author, quote, introduction, birthYear, deathYear,
dynastyID, authorID):
query = '''UPDATE author\n
SET Author='%s', Quote='%s', Introduction='%s', BirthYear=%d, DeathYear=%d, DynastyID=%d\n
WHERE AuthorID = %d''' % (author, quote, introduction, birthYear, deathYear,
dynastyID, authorID)
cursor.execute(query)
return conn.commit()
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_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_work(workID): query = '''SELECT work.WorkID, work.Title, work.Content, work.Foreword, work.Introduction AS WorkIntroduction, work.Type, work.AuthorID, work.DynastyID, work.CollectionID, author.Author, author.Introduction AS AuthorIntroduction, dynasty.Dynasty, collection.Collection, collection.Introduction\n FROM work, author, dynasty, collection\n WHERE work.workID = %d\n AND work.AuthorID = author.AuthorID\n AND work.DynastyID = dynasty.DynastyID\n AND work.collectionID = collection.CollectionID\n''' % workID cursor.execute(query) return cursor.fetchone()
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 add_comment(user_id,
review_id,
comment,
is_root=0,
parent_comment_id=0):
query = '''INSERT INTO comment (UserID, ReviewID, Comment, IsRoot, ParentCommentID)\n
VALUES (%d, %d, '%s', %d, %d)''' % (user_id, review_id, comment, is_root,
parent_comment_id)
cursor.execute(query)
return conn.commit()
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 add_comment(user_id, review_id, comment, is_root=0, parent_comment_id=0):
query = """INSERT INTO comment (UserID, ReviewID, Comment, IsRoot, ParentCommentID)\n
VALUES (%d, %d, '%s', %d, %d)""" % (
user_id,
review_id,
comment,
is_root,
parent_comment_id,
)
cursor.execute(query)
return conn.commit()
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 edit_product(product_id, product, url, image_url, introduction): query = '''UPDATE product SET Product = '%s', Url = '%s', ImageUrl = '%s', Introduction = '%s' WHERE ProductID = %d''' % (product, url, image_url, introduction, product_id) cursor.execute(query) return conn.commit()
def get_name(user_id):
query = "SELECT Name FROM user WHERE UserID = %d" % user_id
cursor.execute(query)
return cursor.fetchone()['Name']
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 active_user(userID): query = "UPDATE user SET IsActive = 1 WHERE UserID = %d" % userID cursor.execute(query) return conn.commit()
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 edit_widget(widget_id, title, content, position_index): query = '''UPDATE widget SET Title = '%s', Content = '%s', PositionIndex = %d\n WHERE WidgetID = %d''' % (title, content, position_index, widget_id) cursor.execute(query) return conn.commit()
def get_widget(widget_id): query = "SELECT * FROM widget WHERE WidgetID = %d" % widget_id cursor.execute(query) return cursor.fetchone()
def add_review(work_id, user_id, title, content): query = '''INSERT INTO review (WorkID, UserID, Title, Content)\n VALUES (%d, %d, '%s', '%s')''' % (work_id, user_id, title, content) cursor.execute(query) conn.commit() return cursor.lastrowid
def add_work(title, content, foreword, introduction, authorID, dynastyID, collectionID, type):
query = '''INSERT INTO work (Title, Content, Foreword, Introduction, AuthorID, DynastyID, CollectionID, Type)\n
VALUES ('%s', '%s', '%s','%s', %d, %d, %d, '%s')''' % (title, content, foreword, introduction, authorID, dynastyID, collectionID, type)
cursor.execute(query)
conn.commit()
return cursor.lastrowid
def add_review(work_id, user_id, title, content):
query = '''INSERT INTO review (WorkID, UserID, Title, Content)\n
VALUES (%d, %d, '%s', '%s')''' % (work_id, user_id, title, content)
cursor.execute(query)
conn.commit()
return cursor.lastrowid
def get_product(product_id): query = "SELECT * FROM product WHERE ProductID = %d" % product_id cursor.execute(query) return cursor.fetchone()
def add_user(userID, name, avatar, signature, desc, locationID, location): query = '''INSERT INTO user (UserID, Name, Avatar, Signature, Description, LocationID, Location)\n VALUES (%d, '%s', '%s', '%s', '%s', %d, '%s')''' % (userID, name, avatar, signature, desc, locationID, location) cursor.execute(query) return conn.commit()
def check_user_active(user_id):
query = "SELECT * FROM user WHERE UserID = %d AND IsActive = 1" % user_id
cursor.execute(query)
return cursor.rowcount > 0
def check_user_active(user_id): query = "SELECT * FROM user WHERE UserID = %d AND IsActive = 1" % user_id cursor.execute(query) return cursor.rowcount > 0
def edit_review(review_id, title, content):
query = '''UPDATE review SET Title = '%s', Content = '%s' WHERE ReviewID = %d''' % (
title, content, review_id)
cursor.execute(query)
return conn.commit()
def edit_review(review_id, title, content): query = '''UPDATE review SET Title = '%s', Content = '%s' WHERE ReviewID = %d''' % (title, content, review_id) cursor.execute(query) return conn.commit()
def edit_work(title, content, foreword, introduction, authorID, dynastyID, collectionID, type, workID): query = '''UPDATE work SET Title = '%s', Content = '%s', Foreword = '%s', Introduction = '%s', AuthorID = %d, DynastyID = %d, CollectionID = %d, Type = '%s' WHERE WorkID=%d''' % (title, content, foreword, introduction, authorID, dynastyID, collectionID, type, workID) cursor.execute(query) return conn.commit()
def add_widget(target_type, target_id, title, content, position_index):
query = '''INSERT INTO widget (Type, TargetID, Title, Content, PositionIndex)\n
VALUES ('%s', %d, '%s', '%s', %d)''' % (target_type, target_id, title, content, position_index)
cursor.execute(query)
conn.commit()
return cursor.lastrowid
def delete_work(workID): query = "DELETE FROM work WHERE WorkID = %d" % workID cursor.execute(query) return conn.commit()
def delete_widget(widget_id): query = "DELETE FROM widget WHERE WidgetID = %d" % widget_id cursor.execute() return conn.commit()
def get_people(user_id):
query = "SELECT * FROM user WHERE UserID = %d" % user_id
cursor.execute(query)
return cursor.fetchone()
def get_people(user_id): query = "SELECT * FROM user WHERE UserID = %d" % user_id cursor.execute(query) return cursor.fetchone()
def active_user(userID):
query = "UPDATE user SET IsActive = 1 WHERE UserID = %d" % userID
cursor.execute(query)
return conn.commit()
def add_email(user_id, email): query = "UPDATE user SET Email = '%s' WHERE UserID = %d" % (email, user_id) cursor.execute(query) return conn.commit()
def add_email(user_id, email):
query = "UPDATE user SET Email = '%s' WHERE UserID = %d" % (email,
user_id)
cursor.execute(query)
return conn.commit()
def check_user_exist(user_id): query = "SELECT * FROM user WHERE UserID = %d" % user_id #return query cursor.execute(query) return cursor.rowcount > 0
def add_user(userID, name, avatar, signature, desc, locationID, location):
query = '''INSERT INTO user (UserID, Name, Avatar, Signature, Description, LocationID, Location)\n
VALUES (%d, '%s', '%s', '%s', '%s', %d, '%s')''' % (
userID, name, avatar, signature, desc, locationID, location)
cursor.execute(query)
return conn.commit()
def get_name(user_id): query = "SELECT Name FROM user WHERE UserID = %d" % user_id cursor.execute(query) return cursor.fetchone()['Name']
def check_user_exist(user_id):
query = "SELECT * FROM user WHERE UserID = %d" % user_id
#return query
cursor.execute(query)
return cursor.rowcount > 0
