Access in-memory SQLite3 DB asychronously, using concurrent.futures.thread.ThreadPoolExecutor

sync_async_db_access.py

#! /usr/bin/python3
# Access in-memory SQLite3 DB asychronously,
# using concurrent.futures.thread.ThreadPoolExecutor
import datetime
import sqlite3
import concurrent.futures

RECORD_COUNT = 50000

e = concurrent.futures.thread.ThreadPoolExecutor(max_workers=2)

# # if by using process
# import multiprocessing
# cpu_count = multiprocessing.cpu_count()
# e = concurrent.futures.process.ProcessPoolExecutor(max_workers=cpu_count)

def prepare_table():
    db = sqlite3.connect(":memory:")
    db.execute("create table DATA ( txt text );")
    db.execute("create table LOG ( txt text );")
    return db

def insert_data(db, cnt):
    db.execute("insert into DATA values ('%s');" % cnt)

def insert_log(db, cnt):
    db.execute("insert into LOG values ('%s');" % cnt)

def test_blocking():
    now = datetime.datetime.now()
    db = prepare_table()
    for i in range(RECORD_COUNT):
        insert_data(db, i)
        insert_log(db, i)
    print("test_blocking:", datetime.datetime.now() - now)

def test_partial_blocking():
    now = datetime.datetime.now()
    db = prepare_table()
    f = None
    for i in range(RECORD_COUNT):
        insert_data(db, i)
    print("test_partial_blocking (blocking) insert_data:", datetime.datetime.now() - now)
    for i in range(RECORD_COUNT):
        f = e.submit(insert_log, db, i)
    concurrent.futures.wait([f])
    print("test_partial_blocking (nonblocking) done:", datetime.datetime.now() - now)

def test_non_blocking():
    now = datetime.datetime.now()
    db = prepare_table()
    f = None
    for i in range(RECORD_COUNT):
        f = e.submit(insert_data, db, i)
        f = e.submit(insert_log, db, i)
    concurrent.futures.wait([f])
    print("test_non_blocking done:", datetime.datetime.now() - now)

test_blocking()
test_partial_blocking()
test_non_blocking()

# result:
# In [767]: test_blocking()
# test_blocking: 0:00:00.935371
# In [768]: test_partial_blocking()
# test_partial_blocking (blocking) insert_data: 0:00:00.464673
# test_partial_blocking (nonblocking) done: 0:00:02.191768
# In [769]: test_non_blocking()
# test_non_blocking done: 0:00:03.471059