SQLite concurrency: the second process does not receive database updates

Question

To check if SQLite can be used by two processes at the same time, I tried this:

script1.py (database update 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 (database query 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 work at the same time. I was hoping that I script2.pywould know (although I don’t know how to do it!) That the database was updated and that it needed to restart part of it. But unfortunately, I get this in script2.py:

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

i.e. he does not receive updates script1.py.

Is there an easy way to make this work with SQLite?

Answer 1

sqlite3:

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)

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

,

UNIQUE PRIMARY KEY, REPLACE , .

, , :

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> 

, sqlite3 . , - , -

,

Answer 2

REPLACE UNIQUE PRIMARY KEY, . (SELECT MAX(value)... .)