Creating pandas list of data frames from MySQL tables in python

Question

I have MySQL sever that we can call MySever there are a hundred databases on that server, we can call the databases: database1, database2, database3… database100. Each database has a table called tablepull. What I am trying to do is get each table within each database called ‘tablepull’ and store the table into a python list, the table “tablepull” has the exact same headers for each database and can save as a dataframe in pandas.

Here are the variables being defined:

from sqlalchemy import create_engine
import pandas as pd

UserName = MyUserName
Password = MyPassword
Server = MyServer
Databases = [‘database1’, ‘database2’, ‘database3’,… ‘database100’]
Table = tablepull

I can easily pull one database table in using this syntax:

engine = create_engine(mssql:// UserName: Password@Server/Table?driver=SQL Server Native Client 11.0')
connection = engine.connect()
cnxn = connection.connection
#Read in data into from sql table#
raw_data = pd.read_sql(sql = """SELECT * FROM Table""", con = cnxn)

The goal is to be able to iterate through all of the databases and grab the desired table (tablepull) for each database and save into a list. Any help or guidance would be greatly appreciated.

Thank You

Answer 1

You can connect to multiple databases, provided they are on same server by not specifying database name in connect command.

Now, you can have a list with all database names:

db_list = ['db1', 'db2', ... 'db100']
df_dict = {}

for c, db in enumerate(db_list):
    sql = """SELECT * FROM {}.Table"""
    df_dict[c] = pd.read_sql_query(sql.format(db), engine)        

Finally you will get a dictionary with all dataframes.

This should help.