[Python] Is the Python ThreadPoolExecutor best used for synchronous database calls?

I need to connect to several different databases and then perform a number of queries on each one. I first tried to use asyncio until I realize that JDBC doesn't have async calls, and wrapping the database call in an async function doesn't help. Though I did try a solution that involved asyncio loops and tasks but that was becoming convoluted. It seems best to avoid asyncio unless it is truly an async call, not something hacked together.

My research led me to concurrent.futures ThreadPoolExecutor. It seemed like the best solution because

• a) I don't have to manage the threads myself and
• b) Threads are good for IO operations, such as waiting for a database response (so I've read).

I will use the executor to connect to 100ish databases. Putting them all in the thread pool is significantly faster than waiting for each connection to finish before starting the next.

And within each database connection, I use another executor to perform 5 queries. Each query can take up to 5 minutes depending on dB size. Again, putting them all in a pool is faster. And once I'm connected to a database, I can immediately start querying it while the other databases are still connecting.

The code below gave me the output I expected, multiple connectDb() and performQuery() executing at the same time, instead of sequentially. There are numerous solutions on stackoverflow and reddit about how to do this, so I'm unsure if using ThreadPoolExecutor is ideal. If there is an even more efficient way to do this, please share.

import time
import concurrent.futures

def performQuery(x, i):
print(f'starting query {i} in db {x}')
time.sleep(1)
print(f'completed query {i} in db {x}')

def connectDb(x):
print('connecting to', x)
time.sleep(2)
print('connected to', x)
with concurrent.futures.ThreadPoolExecutor() as pool:
for i in range(4):
performQuery(x, i)

def main():
with concurrent.futures.ThreadPoolExecutor() as pool:
dbs = ['A','B','C','D','E','F','G']
for db in dbs:
pool.submit(connectDb, db)

main()

Continue reading...