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).

In [1]:
import sqlalchemy
from multiprocessing import Process
import os
import sys
sys.path.append('..')
import doctable
In [9]:
class SimpleTable(doctable.DocTable):
    __schema__ = (
        ('idcol', 'id'),
        ('string', 'process'),
        ('integer', 'number')
    )
db = SimpleTable(target='exdb/tmp_concurrent.db', new_db=True, timeout=60)
In [18]:
def thread(process_name, db):
    db.reopen_engine() # call to create new connections
    for i in range(100):
        db.insert({'process': process_name, 'number': i})
        
db.delete()
p1 = Process(target=thread, args=('p1', db))
p2 = Process(target=thread, args=('p2', db))

p1.start(), p2.start() # start the processes
p1.join(), p2.join() # wait for processes to finish
db.head(10)
Out[18]:
id process number
0 1 p1 0
1 2 p1 1
2 3 p1 2
3 4 p1 3
4 5 p2 0
5 6 p2 1
6 7 p1 4
7 8 p2 2
8 9 p1 5
9 10 p2 3