how to get multiple dictionaries from sqlite query with python

Question

I am working on a questions & answers project, much more like stackoverflow, i am using python, sqlite3 with flask framework. Here are my tables:

drop table if exists users;
create table users (
    id integer primary key autoincrement,
    email varchar(120) unique not null,
    password varchar(60) not null,
    username varchar(60) unique not null,
    first_name varchar(60) not null,
    last_name varchar(60) not null,
    created timestamp
);

drop table if exists profiles;
create table profiles (
    id integer primary key autoincrement,
    user_id integer unique not null,
    bio varchar(140) not null,
    image varchar(250),
    facebook varchar(250),
    twitter varchar(250),
    googleplus varchar(250),
    youtube varchar(250),
    linkedin varchar(250)
);

drop table if exists questions;
create table questions (
    id integer primary key autoincrement,
    title varchar(225) not null,
    slug varchar(225) not null,
    content text not null,
    user_id integer not null,
    created timestamp
);

drop table if exists answers;
create table answers (
    id integer primary key autoincrement,
    content content not null,
    user_id integer not null,
    question_id integer not null,
    created timestamp
);

drop table if exists tags;
create table tags (
    id integer primary key autoincrement,
    title varchar(60) unique not null,
    slug varchar(60) unique not null,
    description varchar(225)
);

drop table if exists question_tag;
create table question_tag (
    id integer primary key autoincrement,
    question_id integer,
    tag_id integer
);

and a seed:

INSERT INTO users(email, password, username, first_name, last_name) 
VALUES('eslammostafa@icloud.com', '1234', 'eslam', 'Eslam', 'Mostafa');
INSERT INTO profiles(user_id, bio) VALUES(1, 'Developer');
INSERT INTO tags(title, slug) VALUES('Python', 'python');
INSERT INTO tags(title, slug) VALUES('Linux', 'linux');
INSERT INTO tags(title, slug) VALUES('Ubuntu', 'ubuntu');
INSERT INTO tags(title, slug) VALUES('Apple', 'apple');
INSERT INTO tags(title, slug) VALUES('Open Source', 'open_source');
INSERT INTO questions(title, slug, content, user_id) VALUES('Python is the language i love', 'python_is_the_language_i_love', 'do you love python?', 1);
INSERT INTO question_tag(question_id, tag_id) VALUES(1, 1);
INSERT INTO question_tag(question_id, tag_id) VALUES(1, 2);

i am trying to get a dictionary that contains question info, user info, tags and answers but what i get is duplicated rows:

def get_question(self, question_id):
    cur = self.query("""SELECT
    questions.id AS id,
    questions.title AS title,
    questions.content AS content,
    questions.user_id AS user_id,
    questions.created AS created,
    tags.id AS tag_id,
    tags.title AS tag_title,
    users.username AS user_username,
    users.first_name AS user_first_name,
    users.last_name AS user_last_name,
    answers.id AS answer_id,
    answers.content AS answer_content,
    answers.created AS answer_created,
    question_tag.question_id, question_tag.tag_id
    FROM questions
    JOIN users ON questions.user_id = users.id
    JOIN profiles ON questions.user_id = profiles.user_id
    LEFT JOIN answers ON questions.id = answers.question_id
    JOIN question_tag ON questions.id = question_tag.question_id
    JOIN tags ON question_tag.tag_id = tags.id
    WHERE questions.id=%d
    GROUP BY question_tag.question_id, question_tag.tag_id""" % (question_id,))
    rows = cur.fetchall()
    return self.list_from_rows(rows)

and this is the desired output:

{'question' : {}, 'user': {}, 'tags': [{},{},{}], 'answers': [{},{},{}]}

Answer 1

The problem I see is that you are running row.fetchone(), witch will get only one record of the query. The query will return two rows with the same data, EXCEPT that each line will have a different tag info:

1|Python is the language i love|do you love python?|1||1|Python|eslam|Eslam|Mostafa||||1|1
1|Python is the language i love|do you love python?|1||2|Linux|eslam|Eslam|Mostafa||||1|2

You will need to parse them manually or use two queries, one to get all tags of the question and the other to get the info.