Result from SQLite not reliable.. [Solved]

[SpongeB0B]

Hi everyone,

I'm testing in memory SQLite DB to use with Flask

here my simple test

Conn = sqlite3.connect(':memory:')
ConnCursor = Conn.cursor()
ConnCursor.execute('PRAGMA foreign_keys = ON;')
ConnCursor.execute("\
CREATE TABLE IF NOT EXISTS Test (\
id INTEGER NOT NULL UNIQUE PRIMARY KEY,\
data TEXT NOT NULL)")

def f_insert2mem(inject):
	ConnCursor.execute("\
		INSERT INTO Test\
		(data) VALUES\
		(?)",[inject])
	Conn.commit


@app.route('/testmem')
def road_testmem():
	f_insert2mem(str(arrow.now()))

	ConnCursor.execute('SELECT * from Test')
	records = ConnCursor.fetchall()
	return str(records)

So when you visit the endpoint localhost/testmem
The f_insert2mem insert in the DB the current time.

then a SQL query fetch all the record in the same table
and flask should simply display the full list of items.

What's happening
When I visit the first time the page it work as expected I got in the browser

[(1, '2023-01-18T05:35:48.497469+00:00')]

I refresh the page I got

[(1, '2023-01-18T05:35:48.497469+00:00'), (2, '2023-01-18T05:35:49.442227+00:00')]

and the third time I refresh I got

[(1, '2023-01-18T05:35:48.497469+00:00')]

and when I continue to refresh I got randomly the records with only the first 1 or 2, 3 items. and sometimes all of them !?

Any ideas ?

Thanks

[SpongeB0B]

OK I've found where was the problem..

I use Gunicorn, that allow the be multithreaded !

therefore when I was refreshing sometime I got the DB mem of one worker, and on refresh the DB mem of another worker :) "simple" as that.

Cheers

[Gribouillis]

What is your solution to the multithreading problem?

[SpongeB0B]

What is your solution to the multithreading problem?

If you want to use the server (Gunicorn) multithreading you can't use the SQLite :memory:
instead you can place the DB is a faster location :)