def get_postTag_by_post_id(postId): with sqlite3.connect("./rare.db") as conn: conn.row_factory = sqlite3.Row db_cursor = conn.cursor() db_cursor.execute( """ SELECT pt.id, pt.post_id, pt.tag_id, t.name FROM post_tag pt JOIN tag t on t.id = pt.tag_id WHERE pt.post_id = ? """, (postId, )) postTags = [] dataset = db_cursor.fetchall() for row in dataset: postTag = PostTag(row['id'], row['post_id'], row['tag_id']) postTags.append(postTag.__dict__) tag = Tag(row['tag_id'], row['name']) postTag.tag = tag.__dict__ return json.dumps(postTags)
def get_single_post(id): with sqlite3.connect("./rare.db") as conn: conn.row_factory = sqlite3.Row db_cursor = conn.cursor() # Use a ? parameter to inject a variable's value # into the SQL statement. db_cursor.execute( """ SELECT p.id, p.user_id, p.category_id, p.title, p.publication_date, p.image_url, p.content, p.approved FROM Posts p WHERE p.id = ? """, (id, )) # Load the single result into memory data = db_cursor.fetchone() # Create an animal instance from the current row post = Post(data['id'], data['user_id'], data['category_id'], data['title'], data['publication_date'], data['image_url'], data['content'], data['approved']) db_cursor.execute( """ SELECT pt.id, pt.tag_id, pt.post_id, t.id tagId, t.label FROM PostTags pt JOIN Tags t ON t.id = pt.tag_id WHERE pt.post_id = ? """, (id, )) post_tags = db_cursor.fetchall() postTags = [] for row in post_tags: post_tag = PostTag(row['id'], row['tag_id'], row['post_id']) tag = Tag(row['tag_id'], row['label']) post_tag.tag = tag.__dict__ postTags.append(post_tag.__dict__) post.postTags = postTags return json.dumps(post.__dict__)