PYTHON SQLITE3 INSERTION: if exist than make addition

Question

Prelumination:

I have the Table "baseline" in the Sqlite3-DB. Table "Baseline" has following columns:

word
counter

Question: Any ideas how it is possible to let sqlite3 engine (if it is more efficient) do for me following operation:

if table "baseline" already has an element with primary key "word", than while insertion don't replace this row but do addition between old counter and new counter.

Example

After following Insertions Commands:

"INSERT INTO  baseline(word, counter) VALUES 'bla', 1; "
"INSERT INTO  baseline(word, counter) VALUES 'bla', 1; "

I want to get the following data from the DB:

>>> "SELECT * FROM baseline;" 
'("bla", 2)'

Thx in advance=)

have you tried SELECT word, sum(counter) FROM baseline GROUP BY word? — Ma0
– Ma0, Commented Aug 27, 2018 at 16:20
thx for the fast responce=) i was thinking about it, but 'word' column has uniq constrain and I need it for space efficiency. — Egor Savin
– Egor Savin, Commented Aug 27, 2018 at 16:22
@EgorSavin Your silent on the case when the word doesn't exist. Should we assume nothing happens? or do you need to insert a new word with counter=1? — gregory
– gregory, Commented Aug 27, 2018 at 16:52
You can also use UPSERT to do this very cleanly, with the current sqlite version. — Shawn
– Shawn, Commented Aug 27, 2018 at 18:58

Hai Vu · Accepted Answer · 2018-08-28 11:33:59Z

3

If word is a primary key, i.e.:

CREATE TABLE baseline (word TEXT PRIMARY KEY, counter INTEGER);

Then the following would work:

INSERT OR REPLACE INTO baseline VALUES (
    'foo',
    COALESCE((SELECT counter FROM baseline WHERE word='foo'), 0) + 1
);

The COALESCE() function returns either the count if word exists in the table, or 0, if not.

edited Aug 28, 2018 at 11:33

answered Aug 27, 2018 at 16:45

Hai Vu

41.5k16 gold badges75 silver badges106 bronze badges

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

Comments

mypetlion · Accepted Answer · 2018-08-27 16:29:15Z

3

If you have a unique constraint on the word column then you can do this quite easily with two statements.

INSERT OR IGNORE INTO baseline
(word, counter)
VALUES
('bla', 0);

Here we either insert a new word with a counter of 0, or if it already exists, then nothing happens.

UPDATE baseline
SET counter = counter+1
WHERE word = 'bla';

Run this update statement regardless of whether the word key already existed. That's why we set counter to 0 instead of 1. This way we don't need any external logic in the code.

answered Aug 27, 2018 at 16:29

mypetlion

2,5445 gold badges21 silver badges22 bronze badges

3 Comments

gregory Over a year ago

for something more explicit: update a set counter=(counter+1) where exists (select 1 from a where word = 'foo' limit 1) ;

mypetlion Over a year ago

@gregory That won't insert in cases where the foo record doesn't exist though. Also, it will increment all counter records when foo does exist.

gregory Over a year ago

@mypetition, OP doesn't want to insert in cases where foo doesn't exist. But, yes, a where predicate needs to be added to the update clause- -thanks.

Collectives™ on Stack Overflow

PYTHON SQLITE3 INSERTION: if exist than make addition

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related