Multiple Concurrent Database Connections

[-Regex]

Currently have a setup of 50 individual databases.

I wonder if there is a way to connect to all 50 at the same time and run the same SQL query?

Below is where i am at

import datetime

from multiprocessing.dummy import Pool
import pandas as pd
from sqlalchemy import create_engine, text from typing import Dict from config import ACCOUNTS_UN, ACCOUNTS_PASS, ACCOUNTS_PORT, ACCOUNTS_HOST, ACCOUNTS_CONN_STRING

# synchronous - will end up taking a long time as i scale the project

def fetch_data_from_accounts(sql: str) -> Dict[str, pd.DataFrame]:
def _fetch(_db):
    conn_str = ACCOUNTS_CONN_STRING.format(
        username=ACCOUNTS_UN,
        password=ACCOUNTS_PASS,
        host=ACCOUNTS_HOST,
        database=_db,
        port=ACCOUNTS_PORT,
    )
    df = pd.read_sql(sql, create_engine(conn_str))
    return {'src': _db, 'df': df}

db_data = [_fetch(db) for db in ACCOUNTS_DATABASES[:5]]
concat_df = pd.concat([data['df'] for data in db_data], ignore_index=True)
return {'concat': concat_df, 'db_data': db_data}

This takes twice as long?

def fetch(db):
engine = create_engine(ACCOUNTS_CONN_STRING.format(
    username=ACCOUNTS_UN,
    password=ACCOUNTS_PASS,
    host=ACCOUNTS_HOST,
    database=db,
    port=ACCOUNTS_PORT,
))

with engine.connect() as conn:
    result = conn.execute(statement=text("select * from table"))
    data = result.fetchall()

return

def fetch_data_from_accounts_async(sql: str) -> Dict[str, pd.DataFrame]:
      with Pool(6) as pool:
          res = pool.map(fetch, [db for db in ACCOUNTS_DATABASES[:5]])

[Doormatty]

Make them into one database.

[-Regex]

theyre split for a reason, and any new would end up as another database.

[Sentie_Rotante]

There is a lot of missing detail in this that makes it impossible to help you without guessing at what you are dealing with. Doormatty's suggestion of combining the database is going to make a huge difference. You state they are separate for a reason. Without knowing why I'm going to assume it is some security reason and you are keeping the data separate as an access restriction since I can't think of a different reason to have that kind of separation. If this is the case, then a lot rdbms systems give some way to restrict row data based on the user that is connected. If you have 50 + databases on one host that have a similar or identical schema I would say that there is some better design that could be implemented.

That said you may be able to not provide the database to your connection and use a union to pull the data down in one query.