How to develop a simple online complaint suggestion system using MySQL and Python

How to use MySQL and Python to develop a simple online complaint suggestion system

Introduction:
With the development of the Internet, more and more people are beginning to choose Submitting complaints and suggestions online provides enterprises and institutions with the opportunity to better understand user needs and improve services. This article will introduce how to use MySQL and Python to develop a simple online complaint suggestion system and provide corresponding code examples.

1. System requirements analysis
Before starting development, we need to clarify the system requirements. A simple online complaint and suggestion system should have the following functions:

• User registration and login
• Submission and management of complaints and suggestions
• Query and reply to complaints and suggestions
• System management function

2. Database design
Create a database named "complaint_system" in MySQL and create the following table:

• Users table: used to store user information, including user ID, user name, password and other fields.

  CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL
  );

  Copy after login

• complaints table: used to store complaint suggestion information, including complaint ID, complaint content, submission time, processing status and other fields.

  CREATE TABLE complaints (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    content TEXT NOT NULL,
    submit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50)
  );

  Copy after login

• replies table: used to store reply information, including reply ID, reply content, reply time and other fields.

  CREATE TABLE replies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    complaint_id INT,
    content TEXT NOT NULL,
    reply_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

  Copy after login

3. Back-end development
We use Python’s Flask framework to develop the back-end interface. First, install Flask and MySQL connection libraries:

pip install flask
pip install flask-mysql

Then, write a Python file named "app.py" as the entry file for the backend. Introduce the corresponding library into the file and configure the database connection:

from flask import Flask, request, jsonify
from flask_mysqldb import MySQL

app = Flask(__name__)

app.config['MYSQL_HOST'] = 'localhost'
app.config['MYSQL_USER'] = 'root'
app.config['MYSQL_PASSWORD'] = 'password'
app.config['MYSQL_DB'] = 'complaint_system'

mysql = MySQL(app)

Next, define routing and corresponding processing functions to implement various functions of the system:

• User registration and login:

  @app.route('/register', methods=['POST'])
  def register():
    username = request.form['username']
    password = request.form['password']
  
    cur = mysql.connection.cursor()
    cur.execute("INSERT INTO users (username, password) VALUES (%s, %s)", (username, password))
    mysql.connection.commit()
    cur.close()
  
    return jsonify({'message': 'Registration success'})
  
  @app.route('/login', methods=['POST'])
  def login():
    username = request.form['username']
    password = request.form['password']
  
    cur = mysql.connection.cursor()
    cur.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
    user = cur.fetchone()
    cur.close()
  
    if user:
        return jsonify({'message': 'Login success'})
    else:
        return jsonify({'message': 'Invalid username or password'})

  Copy after login

• Submission and management of complaints and suggestions:

  @app.route('/complaints', methods=['POST'])
  def submit_complaint():
    user_id = request.form['user_id']
    content = request.form['content']
  
    cur = mysql.connection.cursor()
    cur.execute("INSERT INTO complaints (user_id, content, status) VALUES (%s, %s, %s)", (user_id, content, 'Pending'))
    mysql.connection.commit()
    cur.close()
  
    return jsonify({'message': 'Complaint submitted'})
  
  @app.route('/complaints/<complaint_id>', methods=['PUT'])
  def update_complaint(complaint_id):
    status = request.form['status']
  
    cur = mysql.connection.cursor()
    cur.execute("UPDATE complaints SET status = %s WHERE id = %s", (status, complaint_id))
    mysql.connection.commit()
    cur.close()
  
    return jsonify({'message': 'Complaint updated'})

  Copy after login

• Inquiries and responses to complaints and suggestions:

  @app.route('/complaints', methods=['GET'])
  def get_complaints():
    cur = mysql.connection.cursor()
    cur.execute("SELECT * FROM complaints")
    complaints = cur.fetchall()
    cur.close()
  
    return jsonify({'complaints': complaints})
  
  @app.route('/complaints/<complaint_id>/reply', methods=['POST'])
  def reply_complaint(complaint_id):
    content = request.form['content']
  
    cur = mysql.connection.cursor()
    cur.execute("INSERT INTO replies (complaint_id, content) VALUES (%s, %s)", (complaint_id, content))
    mysql.connection.commit()
    cur.close()
  
    return jsonify({'message': 'Reply submitted'})

  Copy after login
• System management functions:
  (omitted, develop corresponding routing and processing functions according to actual needs)

Finally, run the Flask application:

if __name__ == '__main__':
    app.run()

4. Front-end development
The development of front-end interface can be carried out using front-end technologies such as HTML, CSS and JavaScript. For the sake of simplicity here, we use Bootstrap as the front-end framework and jQuery for AJAX requests and dynamic display. Here is a simple front-end example:

<!DOCTYPE html>
<html>
<head>
    <title>Complaint System</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container">
        <h1>Complaint System</h1>
        <form id="register_form">
            <h2>Register</h2>
            <div class="form-group">
                <input type="text" class="form-control" name="username" placeholder="Username">
            </div>
            <div class="form-group">
                <input type="password" class="form-control" name="password" placeholder="Password">
            </div>
            <button type="submit" class="btn btn-primary">Register</button>
        </form>

        <form id="login_form">
            <h2>Login</h2>
            <div class="form-group">
                <input type="text" class="form-control" name="username" placeholder="Username">
            </div>
            <div class="form-group">
                <input type="password" class="form-control" name="password" placeholder="Password">
            </div>
            <button type="submit" class="btn btn-primary">Login</button>
        </form>

        <!-- 其他功能界面 -->

    </div>

    <script>
        // 注册事件
        $("#register_form").submit(function(event) {
            event.preventDefault();

            var url = "/register";
            var data = $(this).serialize();

            $.post(url, data, function(response) {
                alert(response.message);
            });
        });

        // 登录事件
        $("#login_form").submit(function(event) {
            event.preventDefault();

            var url = "/login";
            var data = $(this).serialize();

            $.post(url, data, function(response) {
                alert(response.message);
            });
        });

        // 其他事件....

    </script>
</body>
</html>

Now you can open the HTML file in your browser to test the functionality of the system.

Conclusion:
This article introduces how to use MySQL and Python to develop a simple online complaint suggestion system. Through the design of the database and the development of the back-end interface, functions such as user registration and login, submission and management of complaints and suggestions, query and reply to complaints and suggestions, and system management are realized. The front-end uses Bootstrap and jQuery for interface development and event processing. Through this system, enterprises and institutions can better collect user feedback and improve service quality.

The above is the detailed content of How to develop a simple online complaint suggestion system using MySQL and Python. For more information, please follow other related articles on the PHP Chinese website!