def InsertUser(): if request.method == 'POST': executionState: bool = False cur = get_db(DATABASE).cursor() data = request.get_json(force=True) try: date_created = datetime.datetime.now() is_active = 1 password = pwd_context.hash(data['password']) cur.execute( "INSERT INTO users ( username, password, display_name, date_created, is_active ) VALUES (:username, :password, :display_name, :date_created, :is_active )", { "username": data['username'], "password": password, "display_name": data['display_name'], "date_created": date_created, "is_active": is_active }) if (cur.rowcount >= 1): executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState == True: return jsonify(message="Data Inserted Sucessfully"), 201 else: return jsonify(message="Failed to insert data"), 409
def addTagsToExistingArticle(): if request.method == 'PUT': data = request.get_json(force=True) tags =data['tags'] #return 204 if not found print(tags) executionState:bool = False try: for tag in tags: cur = get_db(DATABASE).cursor() cur.execute("SELECT tag_id FROM tags WHERE tag_name=:tag_name",{"tag_name":tag}) result = cur.fetchone() if str(result)!="None": tag_id =str(result[0]) #add the new tag here #insert the relation if not exists cur.execute("INSERT INTO tag_article_mapping(tag_id, article_id) SELECT (:tag_id),(:article_id) WHERE NOT EXISTS(SELECT 1 FROM tag_article_mapping WHERE tag_id= :tag_id AND article_id = :article_id)", {"tag_id":tag_id, "article_id":data['article_id']}) elif str(result)=="None": cur.execute("INSERT INTO tags(tag_name) VALUES( :tag_name )",{"tag_name":tag}) new_tag_inserted_id =cur.lastrowid cur.execute("INSERT INTO tag_article_mapping (tag_id, article_id) VALUES (:tag_id, :article_id)",{"tag_id":new_tag_inserted_id,"article_id":data['article_id']}) if (cur.rowcount >=1): executionState =True get_db(DATABASE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState: return jsonify(message="Added Tags to an existing article"),201 else: return jsonify(message="Failed to add tags to the article"),409
def deleteTagFromArticle(): if request.method == 'DELETE': data = request.get_json(force=True) #article_id = request.args.get('article_id') #print(tag_name+article_id) executionState: bool = False cur = get_db().cursor() try: cur.execute( "SELECT article_id FROM article WHERE article_id=:article_id", {"article_id": data['article_id']}) result = cur.fetchone() if str(result) != "None": #check if tag name exists or not cur.execute( "DELETE from tag_article_mapping where tag_id IN ( Select tag_id from tags WHERE tag_name =:tag_name) AND article_id=:article_id", { "tag_name": data['tag_name'], "article_id": data['article_id'] }) #check for query result if (cur.rowcount >= 1): executionState = True get_db().commit() except: get_db().rollback() print("Error") finally: if executionState: return jsonify(message="Deleted Tag SucessFully"), 200 else: return jsonify( message="Failed to delete tags from article"), 409
def updateArticle(): if request.method == 'PUT': executionState: bool = False cur = get_db().cursor() try: data = request.get_json(force=True) tmod = datetime.datetime.now() uid = request.authorization["username"] pwd = request.authorization["password"] cur.execute("select * from article where article_id=?", (data['article_id'], )) res = cur.fetchall() if len(res) > 0: cur.execute( "UPDATE article set content=?,date_modified=? where article_id=? and author =?", (data['content'], tmod, data['article_id'], uid)) if (cur.rowcount >= 1): executionState = True get_db().commit() else: return jsonify(message="Article does not exist"), 409 except: get_db().rollback() print("Error in update") finally: if executionState: return jsonify(message="Updated Article SucessFully"), 201 else: return jsonify(message="Failed to update Article"), 409
def addTagstoArticle(): if request.method == 'POST': data = request.get_json(force=True) executionState: bool = False print(str(data)) cur = get_db().cursor() try: #check if tag exists or not #check if the article exists or not cur.execute("SELECT * FROM article WHERE article_id=:article_id", {"article_id": data['article_id']}) articleExists = cur.fetchall() if articleExists != (): cur.execute("INSERT INTO tags(tag_name) VALUES (:tag_name)", {"tag_name": data['tag_name']}) tag_id = cur.lastrowid cur.execute( "INSERT INTO tag_article_mapping(tag_id, article_id) VALUES(:tag_id, :article_id) ", { "tag_id": tag_id, "article_id": data['article_id'] }) if (cur.rowcount >= 1): executionState = True get_db().commit() if articleExists == (): return jsonify(message="Article does not exist"), 204 except: get_db().rollback() print("Error") finally: if executionState: return jsonify(message="Tag inserted successfully \n"), 201 else: return jsonify(message="Failed to insert tag"), 409
def deleteArticle(): if request.method == 'DELETE': cur = get_db().cursor() executionState: bool = False try: data = request.get_json(force=True) uid = request.authorization["username"] pwd = request.authorization["password"] cur.execute("select * from article where article_id=?", (data['article_id'], )) res = cur.fetchall() if len(res) > 0: cur.execute( "update article set is_active_article=0 where article_id= :article_id and author= :author AND EXISTS(SELECT 1 FROM article WHERE author=:author AND is_active_article=1)", { "article_id": data['article_id'], "author": uid }) row = cur.fetchall() if cur.rowcount >= 1: executionState = True get_db().commit() except: get_db().rollback() print("Error") finally: if executionState: return jsonify(message="Deleted Article SucessFully"), 200 else: return jsonify(message="Failed to delete Article"), 409
def deleteArticle(id): if request.method == 'DELETE': cur = get_db(DATABASE).cursor() executionState: bool = False try: data = request.get_json(force=True) cur.execute("select * from articles where article_id=?", (data['article_id'], )) res = cur.fetchall() if len(res) > 0: cur.execute( "UPDATE articles where article_id= :article_id and author= :author AND EXISTS(SELECT 1 FROM articles WHERE author=:author)", { "article_id": data['article_id'], "author": author }) row = cur.fetchall() if cur.rowcount >= 1: executionState = True get_db(DATABSE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState == True: return jsonify(message="Deleted article successfully"), 200 else: return jsonify(message="Failed to delete Article"), 409
def insertArticle(): if request.method == 'POST': data = request.get_json(force=True) executionState: bool = False try: cur = get_db(DATABASE).cursor() current_time = datetime.datetime.now() # uid = request.authorization["username"] Not required # pwd = request.authorization["password"] Not required cur.execute( "INSERT INTO articles(title,author,content,date_created,date_modified) VALUES (:title, :author, :content, :date_created, :date_modified)", { "title": data['title'], "author": data['author'], "content": data['content'], "date_created": current_time, "date_modified": current_time }) last_inserted_row = cur.lastrowid url_article = ("http://127.0.0.1:5200/articles/" + str(last_inserted_row) + "") cur.execute("UPDATE articles set url=? where article_id=?", (url_article, last_inserted_row)) if (cur.rowcount >= 1): executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState: return jsonify(message="Data insert sucessfully"), 201 else: return jsonify(message="Failed to insert data"), 409
def updateArticle(id): if request.method == 'PUT': cur = get_db(DATABASE).cursor() executionState: bool = True try: data = request.get_json(force=True) tmod = datetime.datetime.now() cur.execute("select * from articles where article_id=?", (data['article_id'], )) res = cur.fetchall() if len(res) > 0: cur.execute( "UPDATE articles set content=?,date_modified=? where article_id=? and author=?", (data['content'], tmod, data['article_id'], id)) if (cur.rowcount >= 1): executionState = True get_db(DATABASE).commit() else: return jsonify(message="Article does not exist"), 409 except: get_db(DATABASE).rollback() print("Error in update") finally: if executionState == True: return jsonify(message="Updated article successfully"), 201 else: return jsonify(message="Failed to update Article"), 409
def metaList(): if request.method == 'GET': # try except limit = request.args.get('limit') if request.args.get('limit') else 10 executionState: bool = True cur = get_db(DATABASE).cursor() try: if limit is not None: cur.execute( "select title,author,date_created,date_modified from articles order by date_created desc limit :limit", {"limit": limit}) row = cur.fetchall() if len(row) == 0: return "No such value exists\n", 204 return jsonify(row), 200 except: get_db(DATABASE).rollback() executionState = False finally: if executionState == False: return jsonify(message="Fail to retrieve from db"), 409 else: return jsonify(row), 200
def latestArticle(): if request.method == 'GET': # try except limit = request.args.get('limit') article_id = request.args.get('article_id') metadata = request.args.get('metadata') executionState: bool = True cur = get_db().cursor() print(metadata) try: if limit is not None: cur.execute( "select * from article where is_active_article = 1 order by date_created desc limit :limit", {"limit": limit}) row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 if limit is None and article_id is None and metadata is None: cur.execute('''Select * from article''') row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 if article_id is not None: cur.execute("SELECT * from article WHERE article_id=" + article_id) row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 if metadata is not None: cur.execute( "select title,author,date_created,date_modified from article where is_active_article = 1 order by date_created desc limit :metadata", {"metadata": metadata}) row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 except: get_db().rollback() executionState = False finally: if executionState == False: return jsonify(message="Fail to retrive from db"), 409 else: return jsonify(row), 200
def getTagsFromArticle(article_id): if request.method == 'GET': cur = get_db().cursor() cur.execute( "SELECT tag_name from tags WHERE tag_id IN (SELECT tag_id from tag_article_mapping WHERE article_id=:article_id )", {"article_id": article_id}) row = cur.fetchall() return jsonify(row), 200
def retriveComments(): if request.method == 'GET': executionState: bool = False cur = get_db().cursor() try: #move the try block after the below for test case if the data is none or not then only try db connection data = request.args.get('article_id') data1 = request.args.get('number') executionState = True if data is not None and data1 is not None: cur.execute( "SELECT timestamp, comment FROM(SELECT * FROM comments WHERE article_id=" + data + " ORDER BY timestamp DESC LIMIT :data1)", {"data1": data1}) retriveNcomments = cur.fetchall() get_db().commit() if list(retriveNcomments) == []: return "No such value exists\n", 204 return jsonify(retriveNcomments), 200 if data is not None and data1 is None: cur.execute("SELECT comment from comments WHERE article_id=" + data) retriveAllComments = cur.fetchall() get_db().commit() if list(retriveAllComments) == []: return "No such value exists\n", 204 return jsonify(len(retriveAllComments)), 200 except: get_db().rollback() #if it fails to execute rollback the database executionState = False finally: if executionState == False: return jsonify(message="Fail"), 204
def AddComment(): if request.method == 'POST': executionState: bool = False cur = get_db(DATABASE).cursor() data = request.get_json(force=True) try: # if isAuthenticated == False: # time_created = datetime.now() # cur.execute("SELECT * FROM article WHERE article_id=?",(data['article_id'],)) # count = len(cur.fetchall()) # if count >=1: # cur.execute("INSERT INTO comments (comment, user_name, article_id, timestamp) VALUES (:comment, :user_name,:article_id, :timestamp) ",{"comment":data['comment'], "user_name":"Anonymous Coward", "article_id":data['article_id'], "timestamp": time_created}) # get_db().commit() # if cur.rowcount >= 1: # executionState = True # else: #uid = request.authorization["username"] #pwd = request.authorization["password"] time_created = datetime.now() #cur.execute("SELECT * FROM articles WHERE article_id=?",(data['article_id'],)) #count = len(cur.fetchall()) if True: cur.execute( "INSERT INTO comments (article_id, comment, username, display_name, timestamp) VALUES (:article_id, :comment, :display_name, :username, :timestamp) ", { "article_id": data['article_id'], "comment": data['comment'], "username": data['username'], "display_name": data['display_name'], "timestamp": time_created }) get_db(DATABASE).commit() if cur.rowcount >= 1: executionState = True except: get_db(DATABASE).rollback( ) #if it fails to execute rollback the database executionState = False finally: if executionState: return jsonify(message="Passed"), 201 else: return jsonify( message="Fail" ), 409 #use 409 if value exists and send the message of conflict
def deleteComment(): if request.method == 'DELETE': executionState: bool = False cur = get_db(DATABASE).cursor() try: data = request.args.get('comment_id') cur.execute("SELECT author FROM comments WHERE comment_id=" + data) row = cur.fetchall() if row[0][0] == "Anonymous Coward": cur.execute( "DELETE from comments WHERE author ='Anonymous Coward' AND comment_id=" + data) if cur.rowcount >= 1: executionState = True get_db(DATABASE).commit() #if isAuthenticated == True: #uid = #request.authorization["username"] #pwd = request.authorization["password"] #if row[0][0] == uid: cur.execute("DELETE from comments WHERE author=? AND comment_id=?", (uid, data)) if cur.rowcount >= 1: executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback( ) #if it fails to execute rollback the database executionState = False finally: if executionState: return jsonify(message="Passed"), 201 else: return jsonify(message="Fail"), 409
def show(): cur = get_db(DATABASE).cursor() article_id = request.args.get('article_id') try: if article_id is not None: #cur.execute("SELECT * from articles WHERE article_id = " + article_id) cur.execute("SELECT * from articles WHERE article_id=" + article_id) row = cur.fetchone() except: get_db(DATABASE).rollback() return jsonify(message="Fail to retrieve from db"), 409 finally: if row is None: return jsonify(message="Article not found"), 404 return jsonify(row), 200
def check_auth(username, password): cur = get_db().cursor().execute( "SELECT user_name, hashed_password from users WHERE user_name=?", (username, )) row = cur.fetchall() if row[0][0] == username and pwd_context.verify(password, row[0][1]): return True else: return False
def list(): if request.method == 'GET': # try except limit = request.args.get('limit') if request.args.get('limit') else 5 executionState: bool = True cur = get_db(DATABASE).cursor() print(limit) try: # Get the n most recent articles if limit is not None: cur.execute( "select * from articles order by date_created desc limit :limit", {"limit": limit}) row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 if limit is None and article_id is None: cur.execute('''Select * from articles''') row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 if article_id is not None: cur.execute("SELECT * from articles WHERE article_id=" + article_id) row = cur.fetchall() if list(row) == []: return "No such value exists\n", 204 return jsonify(row), 200 except: get_db(DATABASE).rollback() executionState = False finally: if executionState == False: return jsonify(message="Fail to retrieve from db"), 409 else: return jsonify(row), 200
def getArticlesFromTag(): if request.method == 'GET': data = request.args.get('tag') cur = get_db().cursor() cur.execute( "Select * from article where article_id IN(Select article_id from tag_article_mapping where tag_id in (Select tag_id from tags WHERE tag_name =:tag_name ))", {"tag_name": data}) row = cur.fetchall() if len(row) == 0: return "No articles containing the tags", 204 else: return jsonify(row), 200
def DeleteUser(): if request.method == "DELETE": executionState: bool = False cur = get_db(DATABASE).cursor() try: uid = request.authorization["username"] pwd = request.authorization["password"] cur.execute( "UPDATE users SET is_active =? WHERE username=? AND EXISTS(SELECT 1 FROM users WHERE username=? AND is_active=1)", (0, uid, uid)) if cur.rowcount >= 1: executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState == True: return jsonify(message="Data SucessFully deleted"), 200 else: return jsonify(message="Failed to delete data"), 409
def UpdateUser(): if request.method == 'PATCH': executionState: bool = False cur = get_db(DATABASE).cursor() try: data = request.get_json(force=True) uid = request.authorization["username"] pwd = request.authorization["password"] hash_password = pwd_context.hash(data['hashed_password']) cur.execute( "UPDATE users SET password=? WHERE username=? AND EXISTS(SELECT 1 FROM users WHERE username=? AND is_active=1)", (hash_password, uid, uid)) if (cur.rowcount >= 1): executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback() print("Error") finally: if executionState == True: return jsonify(message="Updated SucessFully"), 201 else: return jsonify(message="Failed to update the data"), 409
def UpdateComments(): if request.method == 'PUT': executionState: bool = False cur = get_db(DATABASE).cursor() try: data = request.get_json(force=True) cur.execute("SELECT author FROM comments WHERE comment_id=?", (data['comment_id'])) row = cur.fetchall() timeCreated = datetime.now() if row[0][0] == "Anonymous Coward": cur.execute( "UPDATE comments set comment = ?,timestamp=? where author = 'Anonymous Coward' AND comment_id =?", (data['comment'], timeCreated, data['comment_id'])) if cur.rowcount >= 1: executionState = True get_db(DATABASE).commit() # if isAuthenticated == True: # uid = request.authorization["username"] # pwd = request.authorization["password"] if row[0][0] == uid: cur.execute( "UPDATE comments set comment = ?,timestamp=? where author =? AND comment_id =?", (data['comment'], timeCreated, uid, data['comment_id'])) if cur.rowcount >= 1: executionState = True get_db(DATABASE).commit() except: get_db(DATABASE).rollback( ) #if it fails to execute rollback the database executionState = False finally: if executionState: return jsonify(message="Passed"), 201 else: return jsonify(message="Fail"), 409
def root(article_id): cur = get_db(DATABASE).cursor() cur.execute("SELECT comment from comments WHERE article_id=" + article_id) row = cur.fetchall() return jsonify(row)