def posts_parent_tree_sort_since(thread, params): connect = connectDB() cursor = connect.cursor() try: command = '''SELECT posts.post_id, posts.user_id, posts.thread_id, posts.forum_id, posts.created, posts.isedited, posts.message, posts.parent_id, posts.path FROM posts WHERE path[1] IN (SELECT posts.post_id FROM posts WHERE thread_id = %s AND parent_id = 0 ORDER BY post_id %s) ORDER BY path %s;''' % (thread.id, params['order'], params['order']) cursor.execute(command) posts = cursor.fetchall() if posts is None: raise Exception("post is not exist") posts = posts_since_limit_parent(posts, params['since'], params['limit']) post_arr = [] for post in posts: post_arr.append(post_model.from_tuple(post)) return post_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def update_thread(thread, new_thread): connect = connectDB() cursor = connect.cursor() message = new_thread.message if message is None: message = thread.message title = new_thread.title if title is None: title = thread.title try: command = '''UPDATE threads SET message = '%s', title = '%s' WHERE thread_id = %s RETURNING *;''' % (str(message), str(title), thread.id) cursor.execute(command) thread = cursor.fetchone() return thread_model.from_tuple(thread) except psycopg2.IntegrityError as e: print("This user is already exist") raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def delete_users(): connect = connectDB() cursor = connect.cursor() try: cursor.execute(DELETE_USERS_TABLE) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def count_users(): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_COUNT_USERS) count_users = cursor.fetchone()[0] return count_users except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def count_votes_by_thread_id(thread_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(COUNT_VOTES_BY_THREAD_ID, [thread_id, ]) count_votes = cursor.fetchone()[0] return count_votes except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def count_threads_by_forum_id(forum): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_COUNT_THREADS_BY_FORUM_ID, [forum.id, ]) count_threads = cursor.fetchone()[0] return count_threads except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def create_forum(forum, user): connect = connectDB() cursor = connect.cursor() try: cursor.execute(INSERT_FORUM, [user.id, forum.slug, forum.title, ]) returning_forum = cursor.fetchone() return forum_model.from_tuple(returning_forum) except psycopg2.IntegrityError as e: print("This user is already exist" + e.diag.message_primary) raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def create_thread(thread, forum, user): connect = connectDB() cursor = connect.cursor() try: cursor.execute(INSERT_THREAD, [forum.id, user.id, thread.created, thread.message, thread.slug, thread.title, ]) returning_thread = cursor.fetchone() return thread_model.from_tuple(returning_thread) except psycopg2.IntegrityError as e: print("This user is already exist" + e.diag.message_primary) raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def select_post_by_id(post_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_POST_BY_ID, [post_id, ]) post = cursor.fetchone() if post is None: raise Exception("post is not exist") return post_model.from_tuple(post) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def select_forum_by_id(forum_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_FORUM_BY_ID, [forum_id, ]) forum = cursor.fetchone() if forum is None: raise Exception("forum is not exist") return forum_model.from_tuple(forum) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def get_thread_by_id(thread_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_THREAD_BY_ID, [thread_id, ]) thread = cursor.fetchone() if thread is None: raise Exception("threads is not exist") return thread_model.from_tuple(thread) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def count_votes_by_thread_id(thread_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(COUNT_VOTES_BY_THREAD_ID, [ thread_id, ]) count_votes = cursor.fetchone()[0] if count_votes is None: raise Exception("vote is not exist") return count_votes except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError" + +e.diag.message_primary) finally: cursor.close()
def update_post(post, message): connect = connectDB() cursor = connect.cursor() isEdited = True try: command = '''UPDATE posts SET message = '%s', isedited = %s WHERE post_id = %s RETURNING *;''' % (message, isEdited, post.id) cursor.execute(command) post = cursor.fetchone() return post_model.from_tuple(post) except psycopg2.IntegrityError as e: print("This user is already exist") raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def select_vote(thread_id, user_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_VOTE_BY_THREAD_AND_USER_ID, [thread_id, user_id]) vote = cursor.fetchone() if vote is None: raise Exception("vote is not exist") return vote_model.from_tuple(vote) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def select_user_by_user_id(user_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_USER_BY_USER_ID, [ user_id, ]) user = cursor.fetchone() if user is None: raise Exception("user is not exist") return user_model.from_tuple(user) except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def select_user_by_nickname_or_email(user): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_USERS_BY_NICKNAME_OR_EMAIL, [ user.nickname, user.email, ]) param_name_array = ["nickname", "about", "email", "fullname"] users = [] for user in cursor.fetchall(): users.append(dict(zip(param_name_array, user[1:]))) return users except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def create_post(post): connect = connectDB() cursor = connect.cursor() try: cursor.execute(SELECT_NEXT_VAL) post_id = cursor.fetchone()[0] post_path = post.path post_path = post_path.append(post_id) cursor.execute(INSERT_POST, [post_id, post.user_id, post.thread_id, post.forum_id, post.parent_id, post.created, post.message, post.path ]) returning_post = cursor.fetchone() return post_model.from_tuple(returning_post) except psycopg2.IntegrityError as e: print("This user is already exist" + e.diag.message_primary) raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def update_vote(voice, thread_id, user_id): connect = connectDB() cursor = connect.cursor() try: cursor.execute(UPDATE_VOTE, [ voice, user_id, thread_id, ]) returning_vote = cursor.fetchone() return vote_model.from_tuple(returning_vote) except psycopg2.IntegrityError as e: print("This vote is already exist") raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def update_user_by_nickname(user): connect = connectDB() cursor = connect.cursor() try: cursor.execute(UPDATE_USER_BY_NICKNAME, [ user.about, user.email, user.fullname, user.nickname, ]) user = cursor.fetchone() return user_model.from_tuple(user) except psycopg2.IntegrityError as e: print("This user is already exist") raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def insert_user(user): connect = connectDB() cursor = connect.cursor() try: cursor.execute(INSERT_USER, [ user.nickname, user.about, user.email, user.fullname, ]) returning_user = cursor.fetchone() return user_model.from_tuple(returning_user) except psycopg2.IntegrityError as e: print("This user is already exist") raise except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) finally: cursor.close()
def select_threads_by_forum_id(forum, params): connect = connectDB() cursor = connect.cursor() try: command = SELECT_THREADS_BY_FORUM_ID % (forum.id, params['since'], params['order'], params['limit']) cursor.execute(command) threads = cursor.fetchall() if threads is None: raise Exception("threads is not exist") thread_arr = [] for thread in threads: thread_arr.append(thread_model.from_tuple(thread)) return thread_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()
def select_users_arr(forum_id, params): connect = connectDB() cursor = connect.cursor() if params['since'] is not None: if params['desc']: try: command = SELECT_USERS_SINCE_DESC % ( forum_id, forum_id, params['since'], params['limit']) cursor.execute(command) users = cursor.fetchall() if users is None: raise Exception("user is not exist") users_arr = [] for user in users: user = user_model.from_tuple(user) users_arr.append(user) return users_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close() else: try: command = SELECT_USERS_SINCE % ( forum_id, forum_id, params['since'], params['limit']) cursor.execute(command) users = cursor.fetchall() if users is None: raise Exception("user is not exist") users_arr = [] for user in users: user = user_model.from_tuple(user) users_arr.append(user) return users_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close() else: if params['desc']: try: command = SELECT_USERS_DESC % (forum_id, forum_id, params['limit']) cursor.execute(command) users = cursor.fetchall() if users is None: raise Exception("user is not exist") users_arr = [] for user in users: user = user_model.from_tuple(user) users_arr.append(user) return users_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close() else: try: command = SELECT_USERS % (forum_id, forum_id, params['limit']) cursor.execute(command) users = cursor.fetchall() if users is None: raise Exception("user is not exist") users_arr = [] for user in users: user = user_model.from_tuple(user) users_arr.append(user) return users_arr except psycopg2.Error as e: print("PostgreSQL Error: " + e.diag.message_primary) except Exception as e: print("IntegrityError") raise finally: cursor.close()