def mypost_list(user_idx): sql = '''select posts.post_idx, location, sell_yn from posts inner join post_file on posts.post_idx = post_file.post_idx where file_idx in (select min(file_idx) from post_file group by post_idx) and posts.user_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['post_idx'] = row[0] temp_dict['location'] = row[1] temp_dict['sell_yn'] = row[2] data_list.append(temp_dict) print(data_list) return data_list
def search_post(text): sql = '''select posts.post_idx, posts.user_idx, post_like, location from posts inner join post_file on posts.post_idx = post_file.post_idx where file_idx in (select min(file_idx) from post_file group by post_idx) and description like ''' + "'%%#" + text + "%%'" try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['post_idx'] = row[0] temp_dict['user_idx'] = row[1] temp_dict['post_like'] = row[2] temp_dict['img_url'] = row[3] data_list.append(temp_dict) print(data_list) return data_list
def post_list(order_type): sql = '''select posts.post_idx, posts.user_idx, post_like, location from posts inner join post_file on posts.post_idx=post_file.post_idx where file_idx in (select min(file_idx) from post_file group by post_idx)''' try: conn = get_connection() cursor = conn.cursor() if order_type == 0: sql += ' order by posts.post_idx desc' cursor.execute(sql) else: sql += ' order by post_like desc' cursor.execute(sql) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['post_idx'] = row[0] temp_dict['user_idx'] = row[1] temp_dict['post_like'] = row[2] temp_dict['img_url'] = row[3] data_list.append(temp_dict) print(data_list) return data_list
def pay_list(user_idx): sql = '''select posts.post_idx, user.user_idx, email, title, price, location from cart inner join posts on posts.post_idx = cart.post_idx inner join post_file on posts.post_idx = post_file.post_idx inner join user on posts.user_idx = user.user_idx where file_idx in (select min(file_idx) from post_file group by post_idx) and cart.user_idx = %s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['post_idx'] = row[0] temp_dict['uesr_idx'] = row[1] temp_dict['user_email'] = row[2] temp_dict['title'] = row[3] temp_dict['price'] = row[4] temp_dict['location'] = row[5] data_list.append(temp_dict) print(data_list) return data_list
def post_comment(post_idx): sql = '''select comment_idx, post_idx, user_idx, text from comment where post_idx=%s order by comment_idx''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, post_idx) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['comment_idx'] = row[0] temp_dict['post_idx'] = row[1] temp_dict['user_idx'] = row[2] temp_dict['text'] = row[3] data_list.append(temp_dict) print(data_list) return data_list
def get_wishlist(user_idx): sql = '''select posts.post_idx, posts.user_idx, name, email, user_img, title, description, tags, post_like, location from posts inner join post_file on posts.post_idx=post_file.post_idx inner join user on user.user_idx=posts.user_idx where posts.post_idx in (select post_idx from wishlist where user_idx=%s) and file_idx in (select min(file_idx) from post_file group by post_idx) order by posts.post_idx desc''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False data_list = [] for row in result: temp_dict = {} temp_dict['post_idx'] = row[0] temp_dict['user_idx'] = row[1] temp_dict['user_name'] = row[2] temp_dict['user_email'] = row[3] temp_dict['user_img'] = row[4] temp_dict['title'] = row[5] temp_dict['description'] = row[6] temp_dict['tags'] = row[7] temp_dict['post_like'] = row[8] temp_dict['img_url'] = row[9] data_list.append(temp_dict) print(data_list) return data_list
def delete_wish(user_idx, post_idx): sql = '''delete from wishlist where user_idx=%s and post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, post_idx)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def delete_sub(following, followed): sql = '''delete from user_follow where following=%s and followed=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (following, followed)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def delete_comment(comment_idx): sql = '''delete from comment where comment_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, comment_idx) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def update_comment_count(post_idx, comment_count): sql = '''update posts set comment_count=%s where post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (comment_count, post_idx)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def add_comment(post_idx, user_idx, text): sql = '''insert into comment (post_idx, user_idx, text, to_comment) values (%s, %s, %s, 0)''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (post_idx, user_idx, text)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def add_wish(user_idx, post_idx): sql = '''insert into wishlist (user_idx, post_idx) values (%s, %s)''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, post_idx)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def add_sub(following, followed): sql = '''insert into user_follow (following, followed) values (%s, %s)''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (following, followed)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def add_post_file(post_idx, file_type, location): sql = '''insert into post_file(post_idx, file_type, location) values (%s, %s, %s)''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (post_idx, file_type, location)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def add_like(user_idx, post_idx): sql = '''insert into like_posts (user_idx, post_idx, datetime) values (%s, %s, NOW())''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, post_idx)) conn.commit() finally: if conn is not None: conn.close() return 'OK'
def check_user(user_id): sql = '''select * from user where id=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_id) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False return True
def count_like(post_idx): sql = '''select post_like from posts where post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, post_idx) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return 0 return result[0]
def cart_list(user_idx): sql = '''select post_idx from cart where user_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return 1 return result[0]
def check_user_like(user_idx, post_idx): sql = '''select * from like_posts where user_idx=%s and post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, post_idx)) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False return True
def user_login(id): sql = '''select user_idx from user where id=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, id) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False return result[0]
def check_wish(user_idx, post_idx): sql = '''select wish_idx from wishlist where user_idx=%s and post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_idx, post_idx)) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False return result[0]
def check_sub(following, followed): sql = '''select follow_idx from user_follow where following=%s and followed=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (following, followed)) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False return result[0]
def add_user(user_id, user_name, user_email, user_img): sql = '''insert into user (id, name, email, user_img, sign_date) values (%s, %s, %s, %s, NOW())''' sql1 = '''select last_insert_id()''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, (user_id, user_name, user_email, user_img)) conn.commit() cursor.execute(sql1) result = cursor.fetchone() finally: if conn is not None: conn.close() return result[0]
def add_post(user_idx, title, description, tags, price, category, size, brand, gender, certificate, receipt, image_count): sql = '''insert into posts(user_idx, title, written_time, description, tags, price, category, size, brand, gender, certificate, receipt, image_count, post_like, sell_yn, comment_count) values(%s, %s, now(), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 0, 0, 0)''' sql1 = '''select last_insert_id()''' try: conn = get_connection() cursor = conn.cursor() cursor.execute( sql, (user_idx, title, description, tags, price, category, size, brand, gender, certificate, receipt, image_count)) conn.commit() cursor.execute(sql1) result = cursor.fetchone() finally: if conn is not None: conn.close() return result[0]
def post_file_list(post_idx): sql = '''select location from post_file where post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, post_idx) result = cursor.fetchall() finally: if conn is not None: conn.close() if not result: return False file_list = [] for row in result: file_list.append(row[0]) print(file_list) return file_list
def user_info(user_idx): sql = '''select id, name, email, user_img from user where user_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, user_idx) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False data = {} data['id'] = result[0] data['name'] = result[1] data['email'] = result[2] data['img'] = result[3] return data
def post_detail(post_idx): sql = '''select post_idx, posts.user_idx, email, user_img, title, written_time, description, tags, price, category, size, brand, gender, certificate, receipt, post_like, sell_yn, comment_count from posts inner join user on user.user_idx = posts.user_idx where post_idx=%s''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, post_idx) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False data = {} data['post_idx'] = result[0] data['user_idx'] = result[1] data['user_email'] = result[2] data['user_img'] = result[3] data['title'] = result[4] data['time'] = result[5] data['text'] = result[6] data['tags'] = result[7] data['price'] = result[8] data['category'] = result[9] data['size'] = result[10] data['brand'] = result[11] data['gender'] = result[12] data['certificate'] = result[13] data['receipt'] = result[14] data['post_like'] = result[15] data['sell_yn'] = result[16] data['comment_count'] = result[17] return data
def check_post(post_idx): sql = '''select posts.post_idx, title, price, location from posts inner join post_file on posts.post_idx=post_file.post_idx where posts.post_idx=%s and file_idx in (select min(file_idx) from post_file group by post_idx)''' try: conn = get_connection() cursor = conn.cursor() cursor.execute(sql, post_idx) result = cursor.fetchone() finally: if conn is not None: conn.close() if not result: return False data = {} data['post_idx'] = result[0] data['title'] = result[1] data['price'] = result[2] data['location'] = result[3] return data