Accessing a MySQL connection pool from Python multiprocessing -

Accessing a MySQL connection pool from Python multiprocessing -

i'm trying set mysql connection pool , have worker processes access established pool instead of setting new connection each time.

i'm confused if should pass database cursor each process, or if there's other way this? shouldn't mysql.connector pooling automatically? when check log files, many, many connections opened , closed ... 1 each process.

my code looks this:

path = "/tmp" class db(object): def __init__(self): connected = false while not connected: try: cnxpool = mysql.connector.pooling.mysqlconnectionpool(pool_name = "pool1", **config.dbconfig) self.__cnx = cnxpool.get_connection() except mysql.connector.errors.poolerror: print("sleeping.. (pool error)") sleep(5) except mysql.connector.errors.databaseerror: print("sleeping.. (database error)") sleep(5) self.__cur = self.__cnx.cursor(cursor_class=mysqlcursordict) def execute(self, query): homecoming self.__cur.execute(query) def isvalidfile(self, name): homecoming true def readfile(self, fname): d = db() d.execute("""insert users (first_name) values ('michael')""") def main(): queue = multiprocessing.queue() pool = multiprocessing.pool(none, init, [queue]) dirpath, dirnames, filenames in os.walk(path): full_path_fnames = map(lambda fn: os.path.join(dirpath, fn), filenames) full_path_fnames = filter(is_valid_file, full_path_fnames) pool.map(readfile, full_path_fnames) if __name__ == '__main__': sys.exit(main())

first, you're creating different connection pool each instance of db class. pools having same name doesn't create them same pool

from documentation:

it not error multiple pools have same name. application must distinguish pools pool_name property should create each pool distinct name.

besides that, sharing database connection (or connection pool) between different processes bad thought (and highly uncertainty work correctly), each process using it's own connections should aim for.

you initialize pool in init initializer global variable , utilize instead. simple example:

from multiprocessing import pool mysql.connector.pooling import mysqlconnectionpool mysql.connector import connect import os pool = none def init(): global pool print("pid %d: initializing pool..." % os.getpid()) pool = mysqlconnectionpool(...) def do_work(q): con = pool.get_connection() print("pid %d: using connection %s" % (os.getpid(), con)) c = con.cursor() c.execute(q) res = c.fetchall() con.close() homecoming res def main(): p = pool(initializer=init) res in p.map(do_work, ['select * test']*8): print(res) p.close() p.join() if __name__ == '__main__': main()

or utilize simple connection instead of connection pool, 1 connection active in each process @ time anyway. number of concurrently used connections implicitly limited size of multiprocessing.pool.

python connection-pooling mysql-python