How do I avoid inserting duplicate data in PostgreSQL?

Question

How to avoid inserting duplicate data? I only want to insert data that does not already exist. I have written following queries but its not working properly. I'm using PostgreSQL.

title_exits = cursor.execute ("SELECT title,pageid FROM movie_movie WHERE title = %s AND pageid = %s;",(title,pageid))
    if title_exits == 0:
        cursor.execute("INSERT INTO movie_movie (title,pageid,slug,language) values (%s,%s,%s,%s);",(title,pageid,slug,id))
    db.commit()

Update: I tried result = cursor.fetchone ("SELECT count(*) FROM movie_movie WHERE title = %s AND pageid = %s;",(title,pageid)). But I'm getting error message. TypeError: fetchone() takes not arugments (2 given).

Have you looked at this question and decision? [link](http://stackoverflow.com/questions/2511679/python-number-of-rows-affected-by-cursor-executeselect) — tony, Nov 11 '11 at 08:15

score 0 · Answer 1 · answered Nov 11 '11 at 08:09

0

Try to define title field as unique(must define as varchar(constant_length)). Then try insert title into database if title exists, db return error else will insert

answered Nov 11 '11 at 08:09

Yusuf K.

4,195
1
33
69

its already unique. But I don't want show error on terminal. If `found then skip`. – no_freedom Nov 11 '11 at 08:12
you can handle an exception in case you have duplicate record on table, and just pass. – tony Nov 11 '11 at 08:21
Maybe you could check what returns in title_exists var from execute function. Then try to take count from your SQL. – Yusuf K. Nov 11 '11 at 08:21
And I noticed you did not check only title you also check pageid. So not only given title checked also pageid checked in your SQL(title-pageid must same as DB according to your SQL) and maybe title exists but pageid different from exist pageid. May it be cause of not working properly? – Yusuf K. Nov 11 '11 at 08:24

score 0 · Answer 2 · answered Nov 11 '11 at 08:18

0

As I suspected (and @tony points out) cursor.execute does not return the number of rows. It always return None.

answered Nov 11 '11 at 08:18

madth3

7,275
12
50
74

tony · Accepted Answer · 2011-11-11T10:18:12.800

0

Answer related to your update:
You should use "%" symbol instead comma:

result = cursor.fetchone ("SELECT count(*) FROM movie_movie WHERE title = %s AND pageid = %s;" % (title,pageid))

update

as @no_freedom said in comments, think better approach would be

result = cursor.fetchone ("SELECT count(*) FROM movie_movie WHERE title = :1 AND pageid = :2", [title,pageid])

But i'm not sure, just try it.

edited Nov 11 '11 at 10:18

answered Nov 11 '11 at 09:50

tony

1,506
3
21
28

@no_freedom i think you're right, i'm going to update my answer. – tony Nov 11 '11 at 10:11
I tried. But getting same error message. `TypeError: fetchone() takes not arugments (2 given)` – no_freedom Nov 11 '11 at 10:30
but you've used wrong brackets, you've used these "()". did you try with such ones "[]"? – tony Nov 11 '11 at 10:39

How do I avoid inserting duplicate data in PostgreSQL?

3 Answers3