Flask+SQLite露营日志查询仅返回单条结果，如何返回全部匹配项？

我是Flask和Python新手，正在开发基于SQLite的露营日志系统，已实现将包含start_date、end_date、location、camping_site、weather、notes、pictures字段的露营记录存入数据库。系统支持按月份、地点或组合查询，但目前仅能显示符合条件的最后一条记录，需要修改为显示所有匹配记录。

Flask后端代码 #

@app.route('/past-trips', methods=['GET'])
def get_past_trips(): 
    # Fetch past trip information from the database
    db = get_db()
    cursor = db.cursor()
    cursor.execute("SELECT * FROM past_trips")
    rows = cursor.fetchall()

    # Convert the rows into a list of dictionaries
    past_trips = []
    for row in rows:
        trip = {
            'start_date': row['start_date'],
            'end_date': row['end_date'],
            'location': row['location'],
            'camping_site': row['camping_site'],
            'weather': row['weather'],
            'notes': row['notes'],
            'pictures': row['pictures']
        }
        past_trips.append(trip)

    # Retrieve query parameters for filtering
    location = request.args.get('location')
    month = request.args.get('month')
    
    # Filter past trips based on query parameters
    filtered_trips = past_trips  # Initialize the filtered trips list with all past trips

    if location:
        # Perform case-insensitive comparison
        location = location.lower()
        filtered_trips = [trip for trip in filtered_trips if trip['location'].lower() == location]

    if month:
        # Convert month name to YYYY-MM format
        month_names = {
            'january': '01',
            'february': '02',
            'march': '03',
            'april': '04',
            'may': '05',
            'june': '06',
            'july': '07',
            'august': '08',
            'september': '09',
            'october': '10',
            'november': '11',
            'december': '12'
        }

        if month in month_names:
            month = month_names[month]
            filtered_trips = [trip for trip in filtered_trips if
                              datetime.datetime.strptime(trip['start_date'], '%Y-%m-%d').strftime('%m') == month]

    print(filtered_trips)  # Print the filtered past trips

    # Return the filtered past trip information as a JSON response
    return jsonify(filtered_trips)

if __name__ == '__main__':
    app.teardown_appcontext(close_db)
    app.run()

前端camping_ui.html相关代码 #

<div class="my-5">
      <h2 class="text-center mb-3">Most Recent Trip by Month or Location</h2>
      <div class="row mb-3">
        <label for="month" class="col-sm-2 col-form-label">Month</label>
        <div class="col-sm-10">
          <select class="form-select" id="month">
            <option value=""></option>
            <option value="january">January</option>
            <option value="february">February</option>
            <option value="march">March</option>
            <option value="april">April</option>
            <option value="may">May</option>
            <option value="june">June</option>
            <option value="july">July</option>
            <option value="august">August</option>
            <option value="september">September</option>
            <option value="october">October</option>
            <option value="november">November</option>
            <option value="december">December</option>
          </select>
        </div>
      </div>
      <div class="row mb-3">
        <label for="past-location" class="col-sm-2 col-form-label">Location</label>
        <div class="col-sm-10">
          <input type="text" class="form-control" id="past-location">
        </div>
      </div>
      <div class="text-center">
        <button type="button" class="btn btn-primary" onclick="searchPastTrips()">Search</button>
      </div>
      <div class="my-5">
        <h3 class="text-center">Results</h3>
        <table class="table table-striped">
          <thead>
            <tr>
              <th scope="col">Start Date</th>
              <th scope="col">End Date</th>
              <th scope="col">Location</th>
              <th scope="col">Camping Site ID</th>
              <th scope="col">Weather</th>
              <th scope="col">Notes</th>
              <th scope="col">Pictures</th>
            </tr>
          </thead>
          <tbody id="past-trips">
            <!-- past trips will be populated here -->
          </tbody>
        </table>
      </div>
    </div>
  </div>
  <script src="https://cdn.jsdelivr.net/npm/axios/dist/axios.min.js"></script>
  <script>
  // Function to fetch and display past trips based on month and location
  function searchPastTrips() {
    const monthSelect = document.getElementById('month');
    const locationInput = document.getElementById('past-location');

    const selectedMonth = monthSelect.value.toLowerCase();
    const selectedLocation = locationInput.value.toLowerCase();

    const url = `/past-trips?month=${selectedMonth}&location=${selectedLocation}`;

    fetch(url)
      .then(response => response.json())
      .then(data => {
        const pastTripsContainer = document.getElementById('past-trips');
        pastTripsContainer.innerHTML = '';

        data.forEach(trip => {
          const row = document.createElement('tr');

          const pastTripsContainer = document.getElementById('past-trips');
          pastTripsContainer.innerHTML = '';

          const startDateCell = document.createElement('td');
          startDateCell.textContent = trip.start_date;
          row.appendChild(startDateCell);

          const endDateCell = document.createElement('td');
          endDateCell.textContent = trip.end_date;
          row.appendChild(endDateCell);

          const locationCell = document.createElement('td');
          locationCell.textContent = trip.location;
          row.appendChild(locationCell);

          const campingSiteCell = document.createElement('td');
          campingSiteCell.textContent = trip.camping_site;
          row.appendChild(campingSiteCell);

          const weatherCell = document.createElement('td');
          weatherCell.textContent = trip.weather;
          row.appendChild(weatherCell);

          const notesCell = document.createElement('td');
          notesCell.textContent = trip.notes;
          row.appendChild(notesCell);

          const picturesCell = document.createElement('td');
          const pictureImage = document.createElement('img');
          // pictureImage.src = trip.pictures; // Assuming the picture field contains the image URL
          picturesCell.appendChild(pictureImage);
          row.appendChild(picturesCell);

          pastTripsContainer.appendChild(row);
        });
      })
      .catch(error => console.error(error));
  }

  // Call the function to fetch and display past trips when the page loads
  document.addEventListener('DOMContentLoaded', searchPastTrips);
</script>
</body>
</html>

问题原因 #

前端代码的forEach循环内重复执行了pastTripsContainer.innerHTML = ''，导致每处理一条记录就清空一次结果容器，最终只保留最后一条添加的记录。

修复方案 #

移除循环内的容器清空代码，仅在循环开始前执行一次清空操作即可。修复后的前端JavaScript代码如下：

// Function to fetch and display past trips based on month and location
function searchPastTrips() {
  const monthSelect = document.getElementById('month');
  const locationInput = document.getElementById('past-location');

  const selectedMonth = monthSelect.value.toLowerCase();
  const selectedLocation = locationInput.value.toLowerCase();

  const url = `/past-trips?month=${selectedMonth}&location=${selectedLocation}`;

  fetch(url)
    .then(response => response.json())
    .then(data => {
      const pastTripsContainer = document.getElementById('past-trips');
      pastTripsContainer.innerHTML = ''; // 仅在循环前清空一次容器

      data.forEach(trip => {
        const row = document.createElement('tr');

        const startDateCell = document.createElement('td');
        startDateCell.textContent = trip.start_date;
        row.appendChild(startDateCell);

        const endDateCell = document.createElement('td');
        endDateCell.textContent = trip.end_date;
        row.appendChild(endDateCell);

        const locationCell = document.createElement('td');
        locationCell.textContent = trip.location;
        row.appendChild(locationCell);

        const campingSiteCell = document.createElement('td');
        campingSiteCell.textContent = trip.camping_site;
        row.appendChild(campingSiteCell);

        const weatherCell = document.createElement('td');
        weatherCell.textContent = trip.weather;
        row.appendChild(weatherCell);

        const notesCell = document.createElement('td');
        notesCell.textContent = trip.notes;
        row.appendChild(notesCell);

        const picturesCell = document.createElement('td');
        const pictureImage = document.createElement('img');
        // pictureImage.src = trip.pictures; // Assuming the picture field contains the image URL
        picturesCell.appendChild(pictureImage);
        row.appendChild(picturesCell);

        pastTripsContainer.appendChild(row);
      });
    })
    .catch(error => console.error(error));
}

// Call the function to fetch and display past trips when the page loads
document.addEventListener('DOMContentLoaded', searchPastTrips);

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