SQLite concurrency: the 2nd process doesn't get DB updates

Question

In order to see if SQLite can be used by 2 processes at the same time, I tried this:

script1.py (updating the database every 1 second)

import sqlite3, time
conn = sqlite3.connect('test.db')
conn.execute("CREATE TABLE IF NOT EXISTS kv (key text, value text)")

for i in range(1000):
    conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (1, i))
    conn.commit()
    print i
    time.sleep(1)

script2.py (querying the database every 1 second)

import sqlite3, time
conn = sqlite3.connect('test.db')
c = conn.cursor()

while True:
    c.execute('SELECT value FROM kv WHERE key = ?', (1,))
    item = c.fetchone()
    print item
    time.sleep(1)

I started script1.py and then script2.py, and let them running at the same time. I hoped that script2.py would know (I don't know how though!) that the DB has been updated, and that it has to reload a part of it. But sadly I get this in script2.py:

(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)
(u'0',)

i.e. it doesn't get script1.py's updates.

Is there a simple way to make this work with SQLite?

Answer 1

REPLACE needs a UNIQUE or PRIMARY KEY constraint to be able to detect duplicates. (SELECT MAX(value)... would work.)

Answer 2

This works fine with sqlite3: Moved from the answer to this question

script1.py

import sqlite3, time
conn = sqlite3.connect('test.db')
conn.execute("CREATE TABLE IF NOT EXISTS kv (key text unique, value text)")

for i in range(1000):
    conn.execute('REPLACE INTO kv (key, value) VALUES (?,?)', (1, i))
    conn.commit()
    print i
    time.sleep(1)

script2.py

import sqlite3, time
conn = sqlite3.connect('test.db')
c = conn.cursor()

while True:
    c.execute('SELECT value FROM kv WHERE key = ?', (1,))
    item = c.fetchone()
    print item
    time.sleep(1)

output

python script2.py 
(u'3',)
(u'4',)
(u'5',)
(u'6',)
(u'7',)

The problem is that you originally haven't made your key unique

When a UNIQUE or PRIMARY KEY constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally.

Without the key being unique here's what happening:

sqlite3 test.db 
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
1|5
sqlite> select * from kv;
1|0
1|1
1|2
1|3
1|4
1|5
1|6
1|7
sqlite> 

And yes, sqlite3 supports transactions, with a few caveats though. So if you also need to support multiple writers - multiple readers scenario everything may become a bit tricky because of locks contention

Here's a related discussion on the multiple writers case if you need it