Flask 民意调查应用程序的 sqlite3 架构

发布于 2024-10-30 02:01:07 字数 488 浏览 0 评论 0原文

我是 Flask 初学者，我想使用 Flask 和 sqlite3 作为数据库引擎构建一个民意调查应用程序。

我的问题是如何创建两个表，“问题”和“选择”，以便每个问题都有一些选择（可能不是固定的数字。

我原来的方法相当幼稚：

drop table if exists entries;
create table question (
    ques_id integer primary key autoincrement,
    ques string not null,
    choice1 string not null,
    choice2 string not null,
    choice3 string not null,
    choice4 string not null,
    pub_date integer
);

原文

I'm a Flask beginner and I want to build a poll app using flask and sqlite3 as the database engine.

My question is how can I create two tables, 'questions' and 'choices' so that each question has some choices(may not be a fixed number.

My orginal approach was rather naive:

drop table if exists entries;
create table question (
    ques_id integer primary key autoincrement,
    ques string not null,
    choice1 string not null,
    choice2 string not null,
    choice3 string not null,
    choice4 string not null,
    pub_date integer
);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

江南烟雨〆相思醉 2024-11-06 02:01:07

以下是一种更加规范化的方法。这对于存储所有问题共有的一组单独的选择很有用。

CREATE TABLE choices (
    choice_id integer primary key autoincrement,
    choice string not null
);

CREATE TABLE questions (
    ques_id integer primary key autoincrement,
    ques string not null,
    choice_id integer,
    FOREIGN KEY(choice_id) REFERENCES choice(choice_id)
);

解释器会话示例：

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute("""CREATE TABLE choices (
...     choice_id integer primary key autoincrement,
...     choice string not null
... );""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""CREATE TABLE questions (
...     ques_id integer primary key autoincrement,
...     ques string not null,
...     choice_id integer,
...     pub_date integer,
...     FOREIGN KEY(choice_id) REFERENCES choice(choice_id)
... );""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("INSERT INTO choices (choice) VALUES ('yes')")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""INSERT INTO questions (ques,choice_id) 
                 VALUES ('do you like sqlite?',1)""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""SELECT ques, choice 
                   FROM questions q 
                        JOIN choices c ON c.choice_id = q.choice_id;""")
>>> c.fetchall()
[(u'do you like sqlite?', u'yes')]

The following is a more normalized approach. This is good for storing a separate set of choices common to all questions.

CREATE TABLE choices (
    choice_id integer primary key autoincrement,
    choice string not null
);

CREATE TABLE questions (
    ques_id integer primary key autoincrement,
    ques string not null,
    choice_id integer,
    FOREIGN KEY(choice_id) REFERENCES choice(choice_id)
);

Example interpreter session:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> c.execute("""CREATE TABLE choices (
...     choice_id integer primary key autoincrement,
...     choice string not null
... );""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""CREATE TABLE questions (
...     ques_id integer primary key autoincrement,
...     ques string not null,
...     choice_id integer,
...     pub_date integer,
...     FOREIGN KEY(choice_id) REFERENCES choice(choice_id)
... );""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("INSERT INTO choices (choice) VALUES ('yes')")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""INSERT INTO questions (ques,choice_id) 
                 VALUES ('do you like sqlite?',1)""")
<sqlite3.Cursor object at 0x7f29f60b8ce8>
>>> c.execute("""SELECT ques, choice 
                   FROM questions q 
                        JOIN choices c ON c.choice_id = q.choice_id;""")
>>> c.fetchall()
[(u'do you like sqlite?', u'yes')]

回复收藏 0 原文

~没有更多了~