使用Python和Redis构建在线问卷调查：如何实现数据收集与分析-猿码集

介绍

随着互联网的普及，问卷调查成为了了解用户需求、了解市场趋势不可或缺的一种方式。传统的问卷调查需要发放纸质问卷或者进行电话调查，花费时间和人力成本较高。而通过在线问卷调查，不仅可以方便地收集用户数据，还能够通过数据分析得出更精准的结论。

本文将介绍如何使用Python和Redis构建一个在线问卷调查系统，实现数据收集与分析。

前置知识

Redis

Redis是一个开源、内存型的高效数据存储系统，可以用作数据库、缓存、消息中间件等多种用途。

import redis
r = redis.Redis(host='localhost', port=6379, db=0)
r.set('name', 'Tom')
name = r.get('name')
print(name)

Python Flask

Flask是一个基于Python的轻量级web框架，可以用于创建网站、API等。

from flask import Flask
app = Flask(__name__)
@app.route('/')
def hello_world():
    return 'Hello, World!'
if __name__ == '__main__':
    app.run()

构建在线问卷调查系统

1. 数据库设计

问卷表

CREATE TABLE `survey` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

问题表

CREATE TABLE `question` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `type` tinyint(1) NOT NULL,
  `options` text,
  PRIMARY KEY (`id`),
  KEY `survey_id` (`survey_id`),
  CONSTRAINT `question_ibfk_1` FOREIGN KEY (`survey_id`) REFERENCES `survey` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

回答表

CREATE TABLE `answer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `survey_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `survey_id` (`survey_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `answer_ibfk_1` FOREIGN KEY (`survey_id`) REFERENCES `survey` (`id`),
  CONSTRAINT `answer_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

回答详情表

CREATE TABLE `answer_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `answer_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `question_type` tinyint(1) NOT NULL,
  `answer` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `answer_id` (`answer_id`),
  KEY `question_id` (`question_id`),
  CONSTRAINT `answer_detail_ibfk_1` FOREIGN KEY (`answer_id`) REFERENCES `answer` (`id`),
  CONSTRAINT `answer_detail_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 编写页面和接口

在Flask项目中，我们可以编写两个页面：问卷列表页面和问卷详情页面。其中，问卷列表页面展示所有的问卷，用户点击某个问卷可以跳转到问卷详情页面。

问卷列表页面

问卷列表页面可以通过查询数据库中的问卷表来生成。具体代码如下：

from flask import Flask, render_template
import pymysql
app = Flask(__name__)
@app.route('/')
def index():
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='survey')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM survey')
    surveys = cursor.fetchall()
    return render_template('index.html', surveys=surveys)
if __name__ == '__main__':
    app.run()

index.html页面如下所示：

<!doctype html>
<html>
  <head>
    <title>问卷调查列表</title>
  </head>
  <body>
    <table border="1">
      <tr>
        <th>问卷ID</th>
        <th>标题</th>
        <th>描述</th>
        <th>开始时间</th>
        <th>结束时间</th>
      </tr>
      {% for survey in surveys %}
      <tr>
        <td>{{ survey[0] }}</td>
        <td>{{ survey[1] }}</td>
        <td>{{ survey[2] }}</td>
        <td>{{ survey[3] }}</td>
        <td>{{ survey[4] }}</td>
      </tr>
      {% endfor %}
    </table>
  </body>
</html>

问卷详情页面

问卷详情页面需要根据问卷ID查询数据库中的问题表，生成前端页面。具体代码如下：

from flask import Flask, render_template, request
import pymysql
app = Flask(__name__)
def get_survey(survey_id):
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='survey')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM survey WHERE id=%s', survey_id)
    survey = cursor.fetchone()
    cursor.execute('SELECT * FROM question WHERE survey_id=%s', survey_id)
    questions = cursor.fetchall()
    return survey, questions
@app.route('/survey//')
def survey_detail(survey_id):
    survey, questions = get_survey(survey_id)
    return render_template('survey_detail.html', survey=survey, questions=questions)
if __name__ == '__main__':
    app.run()

survey_detail.html页面如下所示：

<!doctype html>
<html>
  <head>
    <title>问卷详情：{{ survey[1] }}</title>
  </head>
  <body>
    <h1>{{ survey[1] }}</h1>
    <p>{{ survey[2] }}</p>
    <form action="{{ url_for('answer_save', survey_id=survey[0]) }}" method="post">
      {% for question in questions %}
      <p>{{ question[2] }}（{% if question[3] == 1 %}单选{% else %}多选{% endif %}）</p>
      <ul>
      {% for option in question[4].split(',') %}
      <li><input type="{% if question[3] == 1 %}radio{% else %}checkbox{% endif %}" name="answer_{{ question[0] }}" value="{{ option }}"> {{ option }}</li>
      {% endfor %}
      </ul>
      {% endfor %}
      <input type="submit" value="提交">
    </form>
  </body>
</html>

3. 提交数据

用户提交答案之后，需要将回答保存到数据库中。具体代码如下：

@app.route('/survey//save', methods=['POST'])
def answer_save(survey_id):
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='survey')
    cursor = conn.cursor()
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    user_id = request.remote_addr
    cursor.execute('INSERT INTO answer(survey_id, user_id, created_at) VALUES (%s, %s, %s)', (survey_id, user_id, now))
    answer_id = cursor.lastrowid
    for key, value in request.form.items():
        if key.startswith('answer_'):
            question_id = key.replace('answer_', '')
            question_type = cursor.execute('SELECT type FROM question WHERE id=%s', question_id)
            cursor.execute('INSERT INTO answer_detail(answer_id, question_id, question_type, answer) VALUES (%s, %s, %s, %s)', (answer_id, question_id, question_type, value))
    conn.commit()
    return '提交成功！'

4. 数据展示与分析

用户提交答案之后，我们可以通过Redis进行数据统计和展示。具体代码如下：

def count_survey(survey_id):
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='survey')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM question WHERE survey_id=%s', survey_id)
    questions = cursor.fetchall()
    r = redis.Redis(host='localhost', port=6379, db=0)
    for question in questions:
        key_prefix = f'survey:{survey_id}:question:{question[0]}'
        if question[3] == 1:
            for option in question[4].split(','):
                count = r.scard(f'{key_prefix}:answer:{option}')
                r.set(f'{key_prefix}:count:{option}', count)
        else:
            for option in question[4].split(','):
                count = r.scard(f'{key_prefix}:answer:{option}')
                r.set(f'{key_prefix}:count:{option}', count)
@app.route('/survey//result')
def survey_result(survey_id):
    count_survey(survey_id)
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='survey')
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM question WHERE survey_id=%s', survey_id)
    questions = cursor.fetchall()
    return render_template('survey_result.html', survey_id=survey_id, questions=questions)

survey_result.html页面如下所示：

<!doctype html>
<html>
  <head>
    <title>问卷结果：{{ survey_id }}</title>
  </head>
  <body>
    {% for question in questions %}
    <p>{{ question[2] }}（{% if question[3] == 1 %}单选{% else %}多选{% endif %}）</p>
    <ul>
    {% for option in question[4].split(',') %}
    <li>{{ option }}：{{ redis.get(f'survey:{survey_id}:question:{question[0]}:count:{option}') }}</li>
    {% endfor %}
    </ul>
    {% endfor %}
  </body>
</html>

总结

本文介绍了如何使用Python和Redis构建一个在线问卷调查系统，实现了问卷设计、问卷答题、数据统计和展示等功能。通过对问卷数据的收集和处理，可以更加准确地了解用户的需求和市场趋势，为业务发展提供有力的支持。

使用Python和Redis构建在线问卷调查：如何实现数据收集与分析

介绍