MySQL select request in parallel (python)

Question

I saw a "similar" post Executing MySQL SELECT * query in parallel, buy my question is different, and this has not been answered either, so i guess its not a duplicate.

I am trying to do a MySQL select request in parallel. The reason is because i need the response fast. I managed to create the request when i paralleled the connection as well, but as the connection takes more time then the actual select it would be faster to connect one time, and do the select in parallel.

My approach:

import mysql.connector
import multiprocessing

def func(id):
    return GetFasta(id, cnx).sequence()

cnx = mysql.connector.connect(user='U_NAME', host='IP_ADDR', database='DB_NAME')
p = multiprocessing.Pool()

for x in p.imap_unordered(func, ["Q32P44", "ASD"]):
    print x

p.close()
p.join()
cnx.close()

The GetFasta is a Class and its working as intended, requires a connector, and an ID and does a simple SELECT with those.

If i execute this script the interpreter freezes, and i have to kill it manually. I guess it is either not this trivial, or maybe impossible to select in parallel with one connector.

Is it?

Any workaround?

If you are using `multiprocessing`, I'd suggest not using it in the interpreter. It could work when you are running as a script, however. It also has a chance of working if you define `func` in another file that you will then import. Alternately, try using `multiprocess` instead of `multiprocessing`, as the `multiprocess` fork provides better serialization, which is the usual culprit. — Mike McKerns, Jan 27 '16 at 19:51

score -1 · Accepted Answer · edited Jan 30 '20 at 23:46

I found the "solution" after a lot of research. It is actually not possible. MySQL does not support parallel queries from the same ~~user~~ connection. It is absolutely impossible, and there are no plans to add this.

The best you can get is the way i implemented it, so parallel the connection as well.

In some other types of databases it is possible, but for now i ll stick with MySQL and use the parallel connection method

MySQL select request in parallel (python)

1 Answers1

Linked