CODEWITHSUNDEEP
pythonpostgresql
user514310
user514310

Reputation:

Avoid duplicate data PostgreSQL

How do i insert these artist into database only if does not exist in database. I have written following code. But it is wrong. Will you please help me ? thanks

Artist List

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

My code

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

Output :

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;

OUTPUT SHOULD BE:

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;

UPDATE:

I have already Added constraint on the column name to enforce the column is unique.

Upvotes: 0

Views: 1618

Answers (3)

no_freedom
no_freedom

Reputation: 1961

You have to use if not artist_rows: That's all

    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()

Upvotes: 0

vasek1
vasek1

Reputation: 14071

You can add the unique constraint as Szymon Guz suggested,

ALTER TABLE song_artist ADD UNIQUE (name);

Then use a try / except to handle the duplicate error:

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'

Upvotes: 0

Szymon Lipiński
Szymon Lipiński

Reputation: 28594

  1. Enforce that on the database side. Add constraint on the column name to enforce the column is unique. This should be enough:

    ALTER TABLE song_artist ADD UNIQUE (name);

    This way you can be sure that whatever you do, you won't have any duplicated values.

  2. You could insert the data using queries like this:

    INSERT INTO song_artist (name, slug)
    SELECT 'Mahendra Kapoor','mahendra-kapoor'
    WHERE NOT EXISTS
    (
    SELECT 42
    FROM song_artist
    WHERE name = 'Mahendra Kapoor'
    );

Upvotes: 2

Related Questions