Can i specify db in MySQL query? [duplicate]

Question

I have a tableA with the same structure in 4 different databases under the same host. Is there a better way, how to make a vertical join of these 4 tables than this in Python?

import pandas as pd

import MySQLdb


dbs = ["db1", "db2", "db3", "db4"]

config = ConfigParser()
config.read("config.ini")

for db in dbs:
    connection = MySQLdb.connect(
             user=config["db"]["user"],
             passwd=config["db"]["password"],
             host=config["db"]["host"],
             db=db,
             )

    cursor = connection.cursor()

    query = """ 
                SELECT *
                FROM tableA            
            """

    cursor.execute(query)
    df = pd.DataFrame(cursor.fetchall())
    # todo and now join all df into one

in other words, can I specify db1, db2, db3, db4 in the query?

Answer 1

If they are in 4 different databases I think that what you do here is good enough as long as the 4 tables are not so huge that it will make the process slow.

I would therefore recommend creating an empty dataframe before your for loop and at the end of the loop merge this dataframe with the new one you create. For this, a good way that would keep the information from which database the data comes from can be found here,