避免重复数据PostgreSQL

Question

如果does not存在于数据库中，如何将这些artist插入数据库。我写了以下代码。但这是错误的。你能帮帮我吗？谢谢

艺术家列表

Lata Mangeshkar  
Lata Mangeshkar
Lata Mangeshkar
Asha Bhosle
Lata Mangeshkar
Mahendra Kapoor  
Lata Mangeshkar
Kishore Kumar
Lata Mangeshkar

我的代码

^{pr2}$

输出：

INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Asha Bhosle,asha-bhosle) RETURNING i
d;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;

输出应为：

INSERT INTO song_artist (name,slug) VALUES (Asha Bhosle,asha-bhosle) RETURNING i
d;
INSERT INTO song_artist (name,slug) VALUES (Lata Mangeshkar,lata-mangeshkar) RET
URNING id;
INSERT INTO song_artist (name,slug) VALUES (Mahendra Kapoor,mahendra-kapoor) RET
URNING id;

更新：

我已经在列名上添加了约束，以强制列是唯一的。在

Answer 1

你可以按照Szymon Guz的建议添加唯一的约束

ALTER TABLE song_artist ADD UNIQUE (name);

然后使用try/except来处理重复错误：

^{pr2}$

Answer 2

1. 在数据库端强制执行。在列名上添加约束以强制列是唯一的。这就足够了：

   更改表宋琰艺术家添加唯一（姓名）

   这样您就可以确保无论您做什么，都不会有任何重复的值。

2. 可以使用以下查询插入数据：

   插入song_artist（姓名，slug）
   选择“Mahendra Kapoor”、“Mahendra-Kapoor”
   不存在的地方
   （
   选择42
   宋琰艺术家
   其中name='Mahendra Kapoor'
   )；

Answer 3

你必须使用if not artist_rows:仅此而已

    artist_slug = self.makeSlug(l)

cursor.execute("SELECT count(id) FROM song_artist WHERE name = %s and slug = %s ;" ,(l,artist_slug) ) # Id exist?
artist_rows = cursor.fetchone()
#artist_rows = artist_rows[0]
db.commit()
if not artist_rows:
cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(l,artist_slug))
#k=( 'INSERT INTO song_artist (name,slug) SELECT %s,%s WHERE NOT EXISTS ( SELECT 42 FROM song_artist WHERE name = %s) RETURNING id;'%(l,artist_slug,l))
artist_id = cursor.fetchone()[0] # get mysql_insert_id using RETURNING
db.commit()