PostgreSQL从CSV查询匹配用户的最简方法及脚本示例

Hey there! Unfortunately, you can't directly run SELECT * FROM users WHERE email IN (emails.csv) like that—databases don't recognize CSV files directly inside the IN clause. But there are a few straightforward ways to get this done, depending on your database and preferences. Let's break down the most common approaches:

This is usually the best approach because it leverages your database's optimized tools for handling bulk data.

For MySQL/MariaDB #

1. First, create a temporary table to hold the CSV emails:

CREATE TEMPORARY TABLE temp_emails (email VARCHAR(255) NOT NULL);

2. Import your CSV into the temporary table (adjust the path and options to match your CSV format):

LOAD DATA INFILE '/full/path/to/your/emails.csv'
INTO TABLE temp_emails
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' -- Remove this line if your emails aren't wrapped in quotes
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- Add this only if your CSV has a header row (like "email")

3. Now join this temp table with your users table to get the matching records:

SELECT u.*
FROM users u
INNER JOIN temp_emails te ON u.email = te.email;

For PostgreSQL #

1. Create a temporary table:

CREATE TEMPORARY TABLE temp_emails (email TEXT NOT NULL);

2. Import the CSV (use \copy instead if you're running this from psql and don't have superuser access):

COPY temp_emails FROM '/full/path/to/your/emails.csv' WITH (FORMAT csv, HEADER); -- Include HEADER if your CSV has a header row

3. Run the join query:

SELECT u.*
FROM users u
INNER JOIN temp_emails te ON u.email = te.email;

If you prefer not to create temporary tables, you can write a quick script to read the CSV and generate a valid SQL IN clause. This works well for 1000 emails (most databases handle this size of IN list without issues).

import csv

# Read emails from the CSV file
emails = []
with open('emails.csv', 'r', encoding='utf-8') as csv_file:
    csv_reader = csv.reader(csv_file)
    # Skip the header row if your CSV has one (remove this line if there's no header)
    next(csv_reader)
    for row in csv_reader:
        # Make sure we don't add empty strings
        if row and row[0].strip():
            emails.append(row[0].strip())

# Escape single quotes to avoid SQL syntax errors and injection risks
escaped_emails = [f"'{email.replace("'", "\\'")}'" for email in emails]

# Build the final SQL query
sql_query = f"SELECT * FROM users WHERE email IN ({', '.join(escaped_emails)})"

# Print the query to run manually, or uncomment the code below to execute it directly
print(sql_query)

# Example: Execute the query with PostgreSQL (install psycopg2 first: pip install psycopg2-binary)
# import psycopg2
# conn = psycopg2.connect(
#     host="your_host",
#     database="your_db",
#     user="your_user",
#     password="your_password"
# )
# cur = conn.cursor()
# cur.execute(sql_query)
# results = cur.fetchall()
# for row in results:
#     print(row)
# cur.close()
# conn.close()

Quick Notes: #

• Watch for whitespace: The script uses strip() to remove extra spaces around emails—this avoids mismatches like "user@example.com " vs "user@example.com".
• SQL Injection Risk: Even though this is your own CSV, escaping single quotes is critical to prevent syntax errors (e.g., if an email is o'neill@example.com) and bad practices.
• Large Datasets: If you ever have more than ~10,000 emails, go back to the temporary table method—IN clauses can get slow with huge lists.

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