使用SQLite3筛选获女演员类提名但从未获奖的人员名单

Got it, let's break down how to solve this problem using Python's sqlite3 and pandas. Here's a straightforward approach to get the DataFrame you need:

Step 1: Define the Core Logic #

We need to isolate people who:

• Have received nominations for awards where the name includes 'actress'
• Never won any of those actress-specific awards

The key is to first identify names that meet the "no wins" condition for actress awards, then pull all their relevant nomination records.

Step 2: Write the Target SQL Query #

Assuming your table is named awards (swap this with your actual table name), here's the query to use:

SELECT *
FROM awards
WHERE Name IN (
    -- Subquery to find names with zero wins in actress awards
    SELECT Name
    FROM awards
    WHERE Award LIKE '%actress%'
    GROUP BY Name
    HAVING MAX(Winner) = 0
)
AND Award LIKE '%actress%';

• The subquery groups entries by Name (filtered to actress awards) and checks if the maximum Winner value is 0—this guarantees the person never won any of those awards.
• The outer query fetches all records for those names that are tied to actress awards, giving you the full context of their nominations.

Step 3: Python Implementation #

Here's how to execute this query and load the results into a pandas DataFrame:

import sqlite3
import pandas as pd

# Connect to your SQLite database
conn = sqlite3.connect('your_database_file.db')  # Replace with your DB path

# Define the query (adjust table name if needed)
query = """
SELECT *
FROM awards
WHERE Name IN (
    SELECT Name
    FROM awards
    WHERE Award LIKE '%actress%'
    GROUP BY Name
    HAVING MAX(Winner) = 0
)
AND Award LIKE '%actress%';
"""

# Run query and load into DataFrame
result_df = pd.read_sql_query(query, conn)

# Clean up the connection
conn.close()

# Verify the output (optional)
print(result_df.head())

Quick Adjustments to Match Your Columns #

If you want to explicitly select the columns you mentioned (instead of using *), modify the query to list them directly:

SELECT "Unnamed: 0", CeremonyNumber, CeremonyYear, CeremonyMonth, CeremonyDay, FilmYear, Award, Winner, Name
FROM awards
WHERE Name IN (
    SELECT Name
    FROM awards
    WHERE Award LIKE '%actress%'
    GROUP BY Name
    HAVING MAX(Winner) = 0
)
AND Award LIKE '%actress%';

内容的提问来源于stack exchange，提问作者T.v.L.