Search code examples
pythonsqlpandasfor-looppymssql

Iterate loop in Python through multiple databases in SQL

Currently have a remote SQL server without multiple database structures on it. Connecting through Python code using PyMSSQL plugin and extracting data into pandas before applying some analysis. Is there a way to iterate such that with each loop, the database number changes, allowing a new database's data to be analysed?

E.g. *connect to server

cursor.execute("SELECT TOP 100 *variable name* FROM *database_1*")

*analyse

*disconnect server

Ideally would have a loop allowing me to automatically read data from say database_1 through to database_10

Solution

  • IIUC you can easily do this using read_sql() method:

    engine = create_engine('mssql+pymssql://USER:PWD@hostname/db_name')

for i in range(1,10):
    qry = 'SELECT TOP 100 variable name FROM database_{}'.format(i)
    df = pd.read_sql(qry, engine)
    # analyse ...