Python Flask API：SQLAlchemy联查指定列返回及三表联查结果打印

Got it, let's work through this with a practical example. Since you only shared the Users model, I'll add two logical related tables (Orders and OrderItems) to demonstrate a real-world 3-table join scenario. We'll cover both the query setup and how to return exactly the columns you need for your API.

Step 1: Define Your Related Models #

First, let's flesh out the models with proper SQLAlchemy relationships to link all three tables:

from datetime import datetime, timedelta
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class Users(db.Model):
    id = db.Column(db.Integer, db.Sequence('users_id_seq'), primary_key=True)
    email = db.Column(db.String(64), unique=True, nullable=False)
    password = db.Column(db.String(128), nullable=False)
    created = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
    expires = db.Column(db.DateTime, nullable=False, default=lambda: datetime.utcnow() + timedelta(days=30))
    
    # Relationship: One user has many orders
    orders = db.relationship('Orders', backref='user', lazy='dynamic')

class Orders(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
    order_date = db.Column(db.DateTime, default=datetime.utcnow)
    
    # Relationship: One order has many order items
    order_items = db.relationship('OrderItems', backref='order', lazy='dynamic')

class OrderItems(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, db.ForeignKey('orders.id'), nullable=False)
    product_name = db.Column(db.String(128), nullable=False)
    quantity = db.Column(db.Integer, nullable=False)
    price = db.Column(db.Float, nullable=False)

Step 2: Write the 3-Table Join Query (Return Specific Columns) #

To join all three tables and fetch only the columns you care about, use query.with_entities() to explicitly list the fields you want. This is efficient because it only pulls the necessary data from the database, avoiding full model instance loads.

Here's an example that retrieves user email, order ID, product name, and quantity for all orders:

from flask_restplus import Namespace, Resource

api = Namespace('orders', description='Order related operations')

@api.route('/user-order-details')
class UserOrderDetails(Resource):
    def get(self):
        # Join Users -> Orders -> OrderItems, select specific columns
        query_results = db.session.query(
            Users.email,
            Orders.id.label('order_id'),  # Use label() to rename columns for cleaner response keys
            OrderItems.product_name,
            OrderItems.quantity
        ).join(Orders, Users.id == Orders.user_id)\
         .join(OrderItems, Orders.id == OrderItems.order_id)\
         .all()
        
        # Convert SQLAlchemy named tuples to dictionaries for JSON response
        formatted_results = [
            {
                'user_email': res.email,
                'order_id': res.order_id,
                'product': res.product_name,
                'quantity': res.quantity
            } for res in query_results
        ]
        
        return formatted_results, 200

Alternative: Use Flask-RESTPlus marshal_with (For Structured Responses) #

If you want to enforce a consistent API response schema (highly recommended for production), use marshal_with to define exactly what fields to return:

from flask_restplus import fields

# Define your response schema
order_detail_schema = api.model('OrderDetail', {
    'user_email': fields.String(required=True, description='User email'),
    'order_id': fields.Integer(required=True, description='Unique order ID'),
    'product': fields.String(required=True, description='Product name'),
    'quantity': fields.Integer(required=True, description='Quantity ordered')
})

@api.route('/structured-order-details')
class StructuredOrderDetails(Resource):
    @api.marshal_list_with(order_detail_schema)
    def get(self):
        # Query with column labels matching the schema
        results = db.session.query(
            Users.email.label('user_email'),
            Orders.id.label('order_id'),
            OrderItems.product_name.label('product'),
            OrderItems.quantity
        ).join(Orders)\
         .join(OrderItems)\
         .all()
        
        # Return directly—marshal_with handles converting to the schema
        return results, 200

Key Tips for Returning Specific Columns #

• with_entities(): The most direct way to select only the columns you need. It's more performant than loading full model objects when you don't require all fields.
• label(): Use this to rename columns so your response keys are intuitive (e.g., order_id instead of generic id from the Orders table).
• Tuple to Dictionary: SQLAlchemy returns query results as named tuples; converting them to dictionaries ensures your JSON response is readable and structured.
• marshal_with: Ideal for maintaining API consistency—it validates and formats your response to match a predefined schema, even if your underlying query changes.

内容的提问来源于stack exchange，提问作者Laurent D.