如何通过Flask.jsonify查询MySQL，返回按类型频道分组的嵌套JSON

Hey there! I'll walk you through exactly how to get that nested JSON structure grouped by genre using Flask and MySQL. Here's a step-by-step breakdown with code examples:

Step 1: Install Required Packages #

First, make sure you have the necessary libraries installed. You'll need Flask for the API and a MySQL connector to interact with your database. Run these commands in your terminal:

pip install flask mysql-connector-python

Step 2: Full Flask App Example #

Here's a complete working example that connects to your MySQL database, fetches the movie data, groups it by genre, and returns the nested JSON via an API endpoint:

from flask import Flask, jsonify
import mysql.connector

app = Flask(__name__)

# Configure your MySQL connection details - replace these with your own!
DB_CONFIG = {
    'host': 'localhost',
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database_name'
}

@app.route('/movies/grouped', methods=['GET'])
def get_grouped_movies():
    try:
        # Connect to MySQL
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)  # Return rows as dictionaries for easier handling

        # Fetch all movie records
        cursor.execute("SELECT id, movie_name, genre, time, channel FROM Movie")
        movies = cursor.fetchall()

        # Initialize an empty dictionary to hold our grouped data
        grouped_data = {}

        # Iterate through each movie and group by genre
        for movie in movies:
            genre = movie['genre']
            # Create a new entry for the genre if it doesn't exist
            if genre not in grouped_data:
                grouped_data[genre] = []
            # Add the movie details (excluding genre since it's the key) to the genre's list
            movie_details = {
                'id': movie['id'],
                'movie_name': movie['movie_name'],
                'time': movie['time'],
                'channel': movie['channel']
            }
            grouped_data[genre].append(movie_details)

        # Close database connections
        cursor.close()
        conn.close()

        # Return the grouped data as JSON using jsonify
        return jsonify(grouped_data)

    except mysql.connector.Error as err:
        # Handle database errors
        return jsonify({"error": f"Database error: {str(err)}"}), 500
    except Exception as e:
        # Handle other unexpected errors
        return jsonify({"error": f"Unexpected error: {str(e)}"}), 500

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

Key Explanations: #

• Database Connection: We use mysql.connector to connect to MySQL, and set dictionary=True so rows are returned as Python dictionaries (super helpful for accessing fields by name).
• Data Grouping: We loop through each movie record, use the genre as the key in our grouped_data dictionary. For each genre, we maintain a list of movie objects containing only the fields you need (id, movie_name, time, channel).
• jsonify: Flask's jsonify function takes our Python dictionary and converts it directly into a properly formatted JSON response with the correct Content-Type header.

Testing the Endpoint #

Run the app, then visit http://localhost:5000/movies/grouped in your browser or use a tool like Postman. You'll get exactly the nested JSON structure you requested, grouped by each movie genre.

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