How to develop a simple Q&A website using MySQL and Python

How to use MySQL and Python to develop a simple Q&A website

Introduction:
The Q&A website is one of the most popular online social platforms on the Internet. It provides a platform where users can ask questions and get answers from other users. This article will detail how to develop a simple Q&A website using the MySQL database and the Python programming language, and provide specific code examples.

1. Environment setup
Before you start, you need to make sure that the MySQL database and Python programming environment have been installed. You can learn how to install and configure the relevant environment through the following link:

• MySQL database: https://dev.mysql.com/downloads/installer/
• Python programming environment: https: //www.python.org/downloads/

2. Create a database
Create a database in MySQL to store the data required for the Q&A website. You can use MySQL's graphical tools (such as phpMyAdmin) or the command line to create a database.

Sample code:

CREATE DATABASE qanda;

3. Create data tables
In order to store information such as users, questions, and answers, corresponding data tables need to be created in the database. Create three data tables in the qanda database: users, questions, and answers.

1. The users data table stores user information, such as user name, password, etc.

Sample code:

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

2. The questions data table stores question information, such as the question and the questioner.

Sample code:

CREATE TABLE questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  user_id INT,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

3. answers data table stores answer information, such as answer content and respondent.

Sample code:

CREATE TABLE answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  content TEXT NOT NULL,
  question_id INT,
  user_id INT,
  FOREIGN KEY (question_id) REFERENCES questions(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

4. Write Python code
Use the Python programming language to connect to the MySQL database, and write code to process the logic of the Q&A website.

1. Connect to database:

Sample code:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="qanda"
)

2. Registered user:

Sample code:

def register_user(username, password):
    cursor = db.cursor()
    sql = "INSERT INTO users (username, password) VALUES (%s, %s)"
    val = (username, password)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

3. Ask a question:

Sample code:

def ask_question(title, content, user_id):
    cursor = db.cursor()
    sql = "INSERT INTO questions (title, content, user_id) VALUES (%s, %s, %s)"
    val = (title, content, user_id)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

4. Answer a question:

Sample code:

def answer_question(content, question_id, user_id):
    cursor = db.cursor()
    sql = "INSERT INTO answers (content, question_id, user_id) VALUES (%s, %s, %s)"
    val = (content, question_id, user_id)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

5. Get the list of questions:

Sample code:

def get_question_list():
    cursor = db.cursor()
    sql = "SELECT * FROM questions"
    cursor.execute(sql)
    return cursor.fetchall()

6. Get the list of questions and answers:

Example Code:

def get_answer_list(question_id):
    cursor = db.cursor()
    sql = "SELECT * FROM answers WHERE question_id = %s"
    val = (question_id,)
    cursor.execute(sql, val)
    return cursor.fetchall()

7. Complete sample code:

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="qanda"
)

def register_user(username, password):
    cursor = db.cursor()
    sql = "INSERT INTO users (username, password) VALUES (%s, %s)"
    val = (username, password)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

def ask_question(title, content, user_id):
    cursor = db.cursor()
    sql = "INSERT INTO questions (title, content, user_id) VALUES (%s, %s, %s)"
    val = (title, content, user_id)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

def answer_question(content, question_id, user_id):
    cursor = db.cursor()
    sql = "INSERT INTO answers (content, question_id, user_id) VALUES (%s, %s, %s)"
    val = (content, question_id, user_id)
    cursor.execute(sql, val)
    db.commit()
    return cursor.lastrowid

def get_question_list():
    cursor = db.cursor()
    sql = "SELECT * FROM questions"
    cursor.execute(sql)
    return cursor.fetchall()

def get_answer_list(question_id):
    cursor = db.cursor()
    sql = "SELECT * FROM answers WHERE question_id = %s"
    val = (question_id,)
    cursor.execute(sql, val)
    return cursor.fetchall()

5. Run the website program
Use a Web framework such as Flask to write a simple website program and start the Web server. Make the Q&A site accessible in the browser.

Sample code (using Flask):

from flask import Flask, request, render_template

app = Flask(__name__)

# 注册用户
@app.route('/register', methods=['POST'])
def handle_register():
    username = request.form.get('username')
    password = request.form.get('password')
    user_id = register_user(username, password)
    return f"User registered with ID: {user_id}"

# 提问问题
@app.route('/ask', methods=['POST'])
def handle_ask():
    title = request.form.get('title')
    content = request.form.get('content')
    user_id = int(request.form.get('user_id'))
    question_id = ask_question(title, content, user_id)
    return f"Question asked with ID: {question_id}"

# 回答问题
@app.route('/answer', methods=['POST'])
def handle_answer():
    content = request.form.get('content')
    question_id = int(request.form.get('question_id'))
    user_id = int(request.form.get('user_id'))
    answer_id = answer_question(content, question_id, user_id)
    return f"Answered with ID: {answer_id}"

# 获取问题列表
@app.route('/questions')
def handle_questions():
    questions = get_question_list()
    return render_template('questions.html', questions=questions)

# 获取问题回答列表
@app.route('/answers/<question_id>')
def handle_answers(question_id):
    answers = get_answer_list(int(question_id))
    return render_template('answers.html', answers=answers)

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

6. Summary
At this point, the development of a simple question and answer website is completed. In this article, we introduce how to develop a Q&A website using MySQL and Python, and provide specific code examples. I hope readers can learn some knowledge about MySQL and Python development through this article, and can use it as a basis for more complex application development. I wish you all good luck with your development!

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