基于Python2.7+Django1.67+SqlAlchemy实现分组列表转JSON及性能优化

Hey there! Let's tackle this problem step by step—since you're dealing with only ~600 records, we can optimize this by pulling all data at once and doing the grouping locally, which is way more efficient than hitting the database multiple times. Here's a solid solution tailored to your tech stack (Python 2.7 + Django 1.6.7 + SQLAlchemy):

First off, we'll fetch all required fields from the database in a single query. This cuts down on database round-trips and overhead—600 rows are trivial to handle in memory.

Assuming your SQLAlchemy model looks something like this (adjust to match your actual schema):

# Example model (replace with your actual model)
class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    country = Column(String)
    city = Column(String)
    street = Column(String)

Then fetch the data efficiently:

from sqlalchemy.orm import sessionmaker
from your_django_app.models import Address  # Update to your actual import path
from your_django_app.db import engine  # Your SQLAlchemy engine setup

# Create a session and fetch only the fields we need
Session = sessionmaker(bind=engine)
session = Session()

# Avoid loading full model instances—only select country, city, street
all_records = session.query(Address.country, Address.city, Address.street).all()
session.close()

We'll use nested dictionaries to build the hierarchy—dictionary lookups are O(1), so this grouping runs in linear time O(n), which is as efficient as it gets.

# Initialize root hierarchy dictionary
hierarchy = {}

for country, city, street in all_records:
    # Ensure the country level exists in the hierarchy
    if country not in hierarchy:
        hierarchy[country] = {}
    # Ensure the city level exists under its country
    if city not in hierarchy[country]:
        hierarchy[country][city] = []
    # Add the street (optional: skip duplicates if needed)
    if street not in hierarchy[country][city]:
        hierarchy[country][city].append(street)

Next, convert this dictionary structure into the nested list format you specified (translated to valid JSON, since -> isn't a valid JSON syntax):

def dict_to_hierarchical_list(data):
    """Recursively convert nested dict to the required list structure"""
    result = []
    for key, value in data.items():
        if isinstance(value, dict):
            # Recurse to handle city-level groups
            result.append({key: dict_to_hierarchical_list(value)})
        else:
            # Street lists are our leaf nodes
            result.append({key: value})
    return result

# Convert to the target nested list structure
final_hierarchy = dict_to_hierarchical_list(hierarchy)

Note: Django 1.6.7 doesn't include the JsonResponse class (that was added in Django 1.7), so we'll construct the response manually to ensure compatibility:

import json
from django.http import HttpResponse

# Generate JSON string, preserving non-ASCII characters (like Cyrillic)
json_str = json.dumps(final_hierarchy, ensure_ascii=False, indent=2)

# Create a proper HTTP response with JSON content type
response = HttpResponse(json_str, content_type='application/json; charset=utf-8')

• Single Database Query: Avoids repeated filter requests—this is the biggest efficiency win, as database calls are far more expensive than in-memory operations.
• Dictionary-Based Grouping: O(1) lookups mean grouping 600 records is almost instantaneous.
• Minimal Data Fetch: We only select the fields we need instead of loading full model instances, reducing memory usage and transfer time.
• Optional Deduplication: The check for existing streets prevents duplicate entries in the final list—remove this line if duplicates are acceptable for your use case.

• Python 2.7: Ensure all strings are handled as Unicode to avoid encoding issues with Cyrillic characters. The ensure_ascii=False flag in json.dumps ensures these characters are preserved in the JSON output.
• Django 1.6.7: The manual HttpResponse setup ensures compatibility since JsonResponse isn't available in this version.

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