Concurrent Database Connections

DocTable makes it easy to establish concurrent database connections from different processes. DocTable objects can be copied as-is from one process to another, except that you must call .reopen_engine() to initialize in process thread. This removes now stale database connections (which are not meant to traverse processes) from the engine connection pool.

You may also want to use a large timeout using the timeout argument of the doctable constructor (provided in seconds).

import sqlalchemy
from multiprocessing import Process
import os
import random
import string
import dataclasses
import time
import sys
sys.path.append('..')
import doctable

import datetime
@doctable.schema
class SimpleRow:
    __slots__ = []
    id: int = doctable.IDCol()
    updated: datetime.datetime = doctable.AddedCol()
    process: str = doctable.Col()
    number: int = doctable.Col()

tmp = doctable.TempFolder('exdb')
db = doctable.DocTable(schema=SimpleRow, target=tmp.joinpath('tmp_concurrent.db'), new_db=True, connect_args={'timeout': 15})

def thread_func(numbers, db):
    process_id = ''.join(random.choices(string.ascii_uppercase, k=2))
    print(f'starting process {process_id}')
    db.reopen_engine() # create all new connections
    for num in numbers:
        db.insert({'process': process_id, 'number': num})
        time.sleep(0.01)

numbers = list(range(100)) # these numbers are to be inserted into the database
        
db.delete()
with doctable.Distribute(5) as d:
    d.map_chunk(thread_func, numbers, db)
db.head(10)

starting process DZ
starting process JL
starting process VM
starting process KT
starting process CW

	id	updated	process	number
0	1	2022-07-26 19:56:26.192070	DZ	0
1	2	2022-07-26 19:56:26.196852	JL	20
2	3	2022-07-26 19:56:26.202347	VM	40
3	4	2022-07-26 19:56:26.205927	DZ	1
4	5	2022-07-26 19:56:26.208501	CW	80
5	6	2022-07-26 19:56:26.210643	JL	21
6	7	2022-07-26 19:56:26.205855	KT	60
7	8	2022-07-26 19:56:26.216189	VM	41
8	9	2022-07-26 19:56:26.221924	CW	81
9	10	2022-07-26 19:56:26.224920	JL	22