使用Falcon与SQLite3构建RESTful API：插入学生数据失败求助

Hey there! Let's get that student insertion working properly. The main issues you're likely facing are either incorrect JSON request parsing or unsafe string-based SQL queries (which cause syntax errors or injection risks). Here's a step-by-step solution:

1. Key Mistakes to Avoid #

First, let's call out the common pitfalls that might be breaking your code:

• Never directly concatenate user input into SQL strings (e.g., f"INSERT INTO students VALUES ('{name}', {age})"). This breaks if names have special characters like O'Neil and opens you up to SQL injection attacks.
• Don't skip validating the incoming JSON data—missing fields or wrong data types will cause database errors.
• Make sure you're using the right Falcon method to parse the request body (it changed between v2 and v3+ versions).

2. Working Implementation Code #

Here's a complete, safe example that handles JSON requests, validates input, and uses parameterized SQL queries:

import falcon
import sqlite3
from sqlite3 import Error

class StudentResource:
    def on_post(self, req, resp):
        # Parse JSON request body (Falcon 3+ uses req.media)
        try:
            student_payload = req.media
            student_name = student_payload.get("name")
            student_age = student_payload.get("age")

            # Validate required fields and data types
            if not student_name:
                resp.status = falcon.HTTP_400
                resp.media = {"error": "Name is a required field"}
                return
            if not isinstance(student_age, int) or student_age < 0:
                resp.status = falcon.HTTP_400
                resp.media = {"error": "Age must be a non-negative integer"}
                return
        except Exception:
            resp.status = falcon.HTTP_400
            resp.media = {"error": "Invalid JSON format in request body"}
            return

        # Connect to SQLite and insert data safely
        db_conn = None
        try:
            # Replace with your actual database file path
            db_conn = sqlite3.connect("school.db")
            cursor = db_conn.cursor()

            # Use parameterized query with ? placeholders (SAFE!)
            insert_sql = "INSERT INTO students (name, age) VALUES (?, ?)"
            cursor.execute(insert_sql, (student_name, student_age))
            db_conn.commit()

            # Return success response with the new student's ID
            resp.status = falcon.HTTP_201  # Created status code
            resp.media = {
                "message": "Student added successfully",
                "student_id": cursor.lastrowid
            }
        except Error as db_err:
            resp.status = falcon.HTTP_500
            resp.media = {"error": f"Database error: {str(db_err)}"}
        finally:
            # Always close the database connection
            if db_conn:
                db_conn.close()

# Set up the Falcon app and route
app = falcon.App()
app.add_route("/students", StudentResource())

For Falcon 2.x Users #

If you're on an older Falcon version, replace the JSON parsing part with this (since req.media wasn't available yet):

import json

# Inside on_post method
raw_body = req.stream.read()
try:
    student_payload = json.loads(raw_body.decode("utf-8"))
except json.JSONDecodeError:
    resp.status = falcon.HTTP_400
    resp.media = {"error": "Invalid JSON format"}
    return

3. Testing with Insomnia #

To test this correctly in Insomnia:

• Set the request method to POST
• Use the URL http://localhost:8000/students (adjust port if your server uses a different one)
• Add a Content-Type header with value application/json
• In the Body tab, select JSON and input your student data:

  {
      "name": "Bob Smith",
      "age": 21
  }
  

4. Troubleshooting Tips #

• Double-check your SQLite database path: Ensure the file exists and your app has write permissions.
• Verify the students table schema: Make sure it has name (TEXT type) and age (INTEGER type) columns.
• Check server logs: If you get 500 errors, the database error message in the response will tell you exactly what's wrong (e.g., missing columns, permission issues).

内容的提问来源于stack exchange，提问作者Kutas