只将唯一记录导入PostgreSQL

Question

我正在尝试使用Python将CSV文件导入PostgreSQL。我想告诉Python只导入CSV文件中的记录/行数据，这些记录/行不包含重复项(只有第一个惟一的构建器记录)。我已经附加了用于查找CSV文件中的副本的代码。我刚开始编程，所以请原谅我缺乏经验。

import csv
import psycopg2

database = psycopg2.connect (database = "***", user="***")

cursor = database.cursor()
delete = """Drop table if exists "Real".Noduplicates"""
print (delete)

mydata = cursor.execute(delete)

cursor.execute("""Create Table "Real".Noduplicates
            (UserName varchar(55),
            LastUpdate timestamp,
            Week date,
            Builder varchar(55),
            Traffic integer
            );""")

print "Table created successfully"

csv_data = csv.reader(file('SampleData2.csv'))

already_seen = set()

next(csv_data)

for row in csv_data:
    builder = row[3]
    if builder in already_seen:
        print('{} is a duplicate builder'.format(builder))
    else:
        print('{} is a new builder'.format(builder))
        already_seen.add(builder)

for row in csv_data:

    cursor.execute("""INSERT INTO "Real".Noduplicates (UserName, LastUpdate, Week, Builder, Traffic)"""\
                    """VALUES (%s,%s,%s,%s,%s)""",
           row)

cursor.close()
database.commit()
database.close()

print "CSV Imported"

Answer 1

相反，使用(快得多)将所有行导入临时表，然后使用SELECT DISTINCT ...或SELECT DISTINCT ON (builder)或使用聚合函数将一个不同的集合导入目标表。查找dupes是任何RDBMS的强项。

相关代码示例：

• How to update selected rows with values from a CSV file in Postgres?
• Check if records exists in a Postgres table

或，在逐个插入行时，只需在Postgres 9.5中使用新的并在所有5列上添加UNIQUE索引：

• INSERT into a table from SELECT only if value doesn't exist

但是请注意NULL值的特殊角色(在SQL中从未被认为是相等的)：

• Allow null in unique column

如果您只需要使用列builder来识别dupes，只需定义该列UNIQUE即可。

...
builder varchar(55) UNIQUE NOT NULL,
...

并使用简单的UPSERT：

INSERT INTO "Real".noduplicates (userName, lastUpdate, week, builder, Traffic)
VALUES (%s,%s,%s,%s,%s)
ON CONFLICT ON (builder) DO NOTHING;

最好不要在Postgres中使用CaMelCase名称。

• Are PostgreSQL column names case-sensitive?