如何批量执行DataJoint事务？Python航空预订项目需求

Hey there! Since you're new to DataJoint and need to scale up to 30k+ reservation records while hitting that 75% flight occupancy requirement, let's walk through this step by step. First, we'll fix a small bug in your current reservation logic, then build efficient batch tools to meet your project goals.

1. Fix the Core Reservation Logic (Critical Bug!) #

Looking at your existing reserve function, there's a key issue: your available seats query doesn't actually filter by flight route or departure time. Right now it's pulling every unreserved seat across all flights, which isn't what you want. Let's fix that first with a corrected single-reservation function:

def reserve_single(passenger_id, origin_code, dest_code, departure_start, departure_end):
    with dj.conn().transaction:
        # First, target flights matching origin, destination, and departure window
        target_flights = Flight & {
            'origin_code': origin_code,
            'dest_code': dest_code,
            'departure': ['between', departure_start, departure_end]
        }
        
        # Get only seats on these flights that haven't been reserved yet
        available_seats = (Seat * target_flights - Reservation).fetch(as_dict=True)
        
        if not available_seats:
            raise IndexError(
                f"No seats available for flights from {origin_code} to {dest_code} between {departure_start} and {departure_end}"
            )
        
        # Pick a random available seat/flight combo
        selected = random.choice(available_seats)
        # Fetch passenger name for confirmation
        name = (Passenger & {'passenger_id': passenger_id}).fetch1('full_name')
        
        print(
            f"Success: Reserved seat {selected['aircraft_seat']} on flight {selected['flight_no']} for {name} (price: ${selected['economy_price']})"
        )
        
        # Insert the reservation (cleaner explicit fields)
        Reservation.insert1({
            'flight_no': selected['flight_no'],
            'aircraft_seat': selected['aircraft_seat'],
            'passenger_id': passenger_id
        }, ignore_extra_fields=True)

2. Batch Reservation Function for Random Bookings #

If you want to generate random bulk reservations (e.g., 50 at a time), here's a function that handles this efficiently. It uses a single transaction for the batch to reduce database overhead, and skips failed attempts (no seats available) instead of rolling back the entire batch:

import random
from faker import Faker

fake = Faker()

def batch_reserve(num_bookings, origin_range=(1,7), dest_range=(1,7)):
    # Set departure window to the current month (adjust if needed)
    departure_start = fake.date_time_this_month(before_now=False).replace(day=1, hour=0, minute=0)
    departure_end = departure_start.replace(day=28, hour=23, minute=59)
    
    # Get all existing passenger IDs to sample from
    all_passengers = Passenger.fetch('passenger_id')
    if len(all_passengers) < num_bookings:
        raise ValueError("Not enough passengers in the Passenger table for this batch size")
    
    successful = 0
    failed = 0
    
    with dj.conn().transaction:
        for _ in range(num_bookings):
            try:
                # Pick random passenger, origin, and destination (ensure origin != dest)
                p_id = random.choice(all_passengers)
                origin = random.randint(*origin_range)
                dest = random.randint(*dest_range)
                while origin == dest:
                    dest = random.randint(*dest_range)
                
                # Use our corrected single reservation function
                reserve_single(p_id, origin, dest, departure_start, departure_end)
                successful += 1
            except IndexError:
                failed += 1
                continue
    
    print(f"Batch complete: {successful} successful bookings, {failed} failed (no seats available)")
    return successful, failed

# Example: Run a batch of 50 reservations
batch_reserve(50)

3. Guaranteed 75% Occupancy per Flight (Best for Project Requirements) #

Since your project requires at least 75% occupancy per flight (108 seats per 144-seat aircraft), the most reliable way is to directly fill each flight to its target capacity. This method is far faster than random batches because it uses bulk inserts instead of individual transactions:

def fill_flight_to_capacity(flight_no, target_occupancy=0.75):
    total_seats = len(Seat.fetch())  # Should equal 144 in your setup
    target_bookings = int(total_seats * target_occupancy)
    
    # Check current reservations for this flight
    current_bookings = len((Reservation & {'flight_no': flight_no}).fetch())
    needed_bookings = target_bookings - current_bookings
    
    if needed_bookings <= 0:
        print(f"Flight {flight_no} already meets occupancy: {current_bookings}/{total_seats}")
        return
    
    # Get all available seats for this flight
    available_seats = (Seat * (Flight & {'flight_no': flight_no}) - Reservation).fetch('aircraft_seat')
    if len(available_seats) < needed_bookings:
        needed_bookings = len(available_seats)
        print(f"Flight {flight_no} has limited seats available - filling all {needed_bookings}")
    
    # Sample random passengers for the needed bookings
    selected_passengers = random.sample(Passenger.fetch('passenger_id'), needed_bookings)
    
    with dj.conn().transaction:
        # Prepare bulk insert data (list of dictionaries)
        bulk_reservations = []
        for seat, p_id in zip(available_seats[:needed_bookings], selected_passengers):
            bulk_reservations.append({
                'flight_no': flight_no,
                'aircraft_seat': seat,
                'passenger_id': p_id
            })
        
        # Bulk insert all reservations at once (way faster than individual inserts)
        Reservation.insert(bulk_reservations, ignore_extra_fields=True)
    
    new_occupancy = current_bookings + needed_bookings
    print(f"Flight {flight_no} updated: {new_occupancy}/{total_seats} ({new_occupancy/total_seats*100:.1f}% occupancy)")

# Run this for all 315 flights to hit your 30k+ reservation goal
for flight_no in range(315):
    fill_flight_to_capacity(flight_no)

4. Quick Performance Tips for DataJoint #

• Bulk Inserts: Always use Reservation.insert() with a list of entries instead of looping insert1 - this cuts down on database round trips drastically.
• Transaction Scope: Grouping multiple inserts into a single transaction (like we did for each flight) ensures data consistency and speeds up operations.
• Filter Early: Narrow down your flight/seat queries before joining tables (e.g., target_flights first) to avoid unnecessary data processing.

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