'Best practices for using SQLAlchemy with multiprocessing
I have a class which creates a SQLAlchemy engine object. (I've left out some of the config args and replaced with <> tags).
class DBClient:
def __init__(self, isolation_level='AUTOCOMMIT'):
logging.info('Creating DB connection')
engine_string = sqlalchemy.engine.url.URL(
"postgresql+psycopg2",
username= <username>
password= <password>
database= <database>
port= <port>
host= <host>
)
self._engine = sqlalchemy.create_engine(
engine_string,
pool_size=1,
isolation_level=isolation_level,
pool_pre_ping=True,
connect_args={
"keepalives": 1,
"keepalives_idle": 30,
"keepalives_interval": 10,
"keepalives_count": 5,
}
)
I have multiple processes which read from a database using this client class. Would it be preferable for me to instantiate a new instance of this class inside each process or to find a way to pass it into the multiprocess pool map? I'm currently seeing the following error when trying to pass an instance of this class to a pool map, but am not sure if I should look for a way around it or continue with an engine per process.
return self._map_async(func, iterable, mapstar, chunksize).get()
File "/usr/lib/python3.6/multiprocessing/pool.py", line 644, in get
raise self._value
File "/usr/lib/python3.6/multiprocessing/pool.py", line 424, in _handle_tasks
put(task)
File "/usr/lib/python3.6/multiprocessing/connection.py", line 206, in send
self._send_bytes(_ForkingPickler.dumps(obj))
File "/usr/lib/python3.6/multiprocessing/reduction.py", line 51, in dumps
cls(buf, protocol).dump(obj)
TypeError: can't pickle _thread.RLock objects
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
