insert or replace statement not working with sqlite3 and python

Question

Everytime I run this through the python interpreter it writes new values. for example:

name = ben
age = 10
phone = 42045042

If I run it 10 times. I get 10 duplicates in my database. I know it has to be an easy fix, but I've been working on this for hours and can't figure it out.

conn = sqlite3.connect('addressbook.db')
cur=conn.cursor()
conn.execute('''
    CREATE TABLE IF NOT EXISTS people(name TEXT,
                       age TEXT, phone TEXT, fblink TEXT)''')
conn.execute("INSERT OR REPLACE INTO people values (?, ?, ?, ?)", ben.displayPerson())
cursor = conn.execute("SELECT name, age, phone, fblink from people")
for row in cursor:
   print "NAME = ", row[0]
   print "AGE = ", row[1]
   print "PHONE = ", row[2]
   print "FACEBOOK LINK = ", row[3], "\n"
cur.close()
conn.commit()
conn.close()

Why are you using INSERT OR REPLACE INTO ...? Shouldn't it be just INSERT INTO? — Sazid
– Sazid, Commented Oct 26, 2013 at 2:27
insert into gives me the same problem, that's why i tried insert or replace. — stephan
– stephan, Commented Oct 26, 2013 at 2:42

falsetru · Accepted Answer · 2013-10-26 03:27:46Z

2

There's no primary key field.

Make a primary key field.

For example:

conn.execute('''
    CREATE TABLE IF NOT EXISTS people(name TEXT primary key,
                       age TEXT, phone TEXT, fblink TEXT)''')

REPLACE is executed when UNIQUE constraint violation occurs. Without primary key (or unique ..), it does not happen.

edited Oct 26, 2013 at 3:27

answered Oct 26, 2013 at 3:04

falsetru

371k69 gold badges769 silver badges659 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

stephan Over a year ago

it has the conn.commit

falsetru Over a year ago

@user2905382, I misunderstood your question. I updated the answer.

stephan Over a year ago

i have no idea what a primary key does, but i will try this. also i reworded my question

stephan Over a year ago

I don't know why or how, but that fixed it. You are a genius!

falsetru Over a year ago

@user2905382, Primary key is like the identifier of the row.

|

Ignacio Vazquez-Abrams · Accepted Answer · 2013-10-26 03:16:55Z

1

Your table has no primary key, and hence SQLite doesn't know what it should "OR REPLACE" since it has nothing to base replacing on. Add a primary key.

answered Oct 26, 2013 at 3:16

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

1 Comment

CL. Over a year ago

A primary key is just one of the methods to get a UNIQUE constraint.

Collectives™ on Stack Overflow

insert or replace statement not working with sqlite3 and python

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related