避免PostgreSQL中的重复数据

Question

我想把这些artist插入到数据库里，但前提是数据库里还没有这些记录。我写了以下代码，但好像不对。能帮我一下吗？谢谢！

艺术家列表

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

我的代码

for l in artist:
        l = l.strip()
        cursor.execute("SELECT id FROM song_artist WHERE name = %s ;" ,(l,) ) # Id exist?
        artist_rows = cursor.fetchone()
        if not artist_rows:
            artist_slug = self.makeSlug(artist[0])
            cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(artist[0],artist_slug))
            print k

输出：

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来处理重复的错误：

try:
  cursor.execute( 'INSERT INTO song_artist (name,slug) VALUES (%s,%s) RETURNING id;',(artist[0],artist_slug))

except psycopg2.IntegrityError:
  debug.print 'Duplicate values found.  Insert was not successful'

Answer 2

1. 在数据库那边设置规则。给名字这一列添加一个约束，确保这个列里的值是唯一的。这样做就可以了：

   ALTER TABLE song_artist ADD UNIQUE (name);

   这样一来，不管你做什么，都能确保不会有重复的值。

2. 你可以用这样的查询来插入数据：

   INSERT INTO song_artist (name, slug)
   SELECT 'Mahendra Kapoor','mahendra-kapoor'
   WHERE NOT EXISTS
   (
   SELECT 42
   FROM song_artist
   WHERE 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()