python之如何使用python从sqlite查询中获取多个字典

我正在做一个问答项目，更像是 stackoverflow，我正在使用 python、sqlite3 和 flask 框架。这是我的表格:

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

和一颗种子:

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

我正在尝试获取包含问题信息、用户信息、标签和答案的字典 但我得到的是重复的行:

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) 

这是期望的输出:

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

请您参考如下方法:

我看到的问题是您正在运行 row.fetchone()，女巫将只获得一条查询记录。查询将返回具有相同数据的两行，除了每行将具有不同的标记信息:

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 

您将需要手动解析它们或使用两个查询，一个获取问题的所有标签，另一个获取信息。