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__)
