使用Python对比CSV条目与PostgreSQL 10表数据的技术需求

Based on your table structure, here's a practical step-by-step approach to import your CSV data, compare it with existing records, and insert/update the necessary entries in domains and ranks:

1. Define Your CSV Structure (Assumption) #

First, let’s assume your CSV follows this format (adjust if your actual file differs):

list_name,domain_name,rank
TopSites,example.com,1
TopSites,google.com,2
NewsSites,cnn.com,1

2. Create a Temporary Staging Table #

Staging CSV data in a temp table makes validation and transformation far easier before merging into your main tables:

CREATE TEMP TABLE csv_staging (
    list_name text,
    domain_name text,
    rank integer
);

3. Import CSV into the Staging Table #

Use either \copy (for local files in psql) or server-side COPY (if the file lives on your database server):

Option A: Local File (psql Client) #

Run this directly in your psql terminal:

\copy csv_staging(list_name, domain_name, rank) FROM '/path/to/your/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

• HEADER true skips the first row if your CSV includes column headers.
• Adjust DELIMITER if your file uses tabs or another separator.

Option B: Server-Side File #

If the CSV is on the PostgreSQL server (and you have superuser privileges):

COPY csv_staging(list_name, domain_name, rank) FROM '/path/to/server/file.csv' WITH (FORMAT csv, HEADER true, DELIMITER ',');

4. Insert New Domains into domains Table #

Since domain_name is marked as unique, we’ll only insert domains that don’t already exist:

INSERT INTO domains (domain_name)
SELECT DISTINCT domain_name FROM csv_staging
ON CONFLICT (domain_name) DO NOTHING;

The DISTINCT ensures we don’t waste time trying to insert duplicate domains from the CSV.

5. Insert/Update Ranks in ranks Table #

Now we’ll link the CSV data to your lists and domains tables to populate ranks. Choose the approach that fits your needs:

Insert Only New Ranks (Ignore Existing Pairs) #

If you don’t want to overwrite existing ranks for list-domain pairs:

INSERT INTO ranks (list_id, domain_id, rank)
SELECT 
    l.list_id,
    d.domain_id,
    cs.rank
FROM csv_staging cs
JOIN lists l ON cs.list_name = l.list_name
JOIN domains d ON cs.domain_name = d.domain_name
ON CONFLICT (list_id, domain_id) DO NOTHING;

Update Ranks for Existing Pairs #

If you want to refresh the rank for existing list-domain pairs:
First, add a unique constraint to ranks (required for the conflict update):

ALTER TABLE ranks ADD CONSTRAINT unique_list_domain UNIQUE (list_id, domain_id);

Then run the insert/update:

INSERT INTO ranks (list_id, domain_id, rank)
SELECT 
    l.list_id,
    d.domain_id,
    cs.rank
FROM csv_staging cs
JOIN lists l ON cs.list_name = l.list_name
JOIN domains d ON cs.domain_name = d.domain_name
ON CONFLICT (list_id, domain_id) DO UPDATE 
SET rank = EXCLUDED.rank;

6. Clean Up (Optional) #

Drop the temporary staging table once you’ve verified the data is correctly imported:

DROP TABLE csv_staging;

Key Notes: #

• Ensure your lists table already contains all list_name values present in the CSV (otherwise those rows will be excluded from the final insert). If not, add a step to insert missing list names first.
• Test with a small sample CSV first to confirm the logic works as expected.
• For large CSVs, consider batch processing or adding QUOTE/ESCAPE options to COPY if your data contains special characters.

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