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 |