CODEWITHSUNDEEP
pythonloops
rain123
rain123

Reputation: 273

Using python looping to call multiple SQL databases

I want to call multiple databases in Microsoft SQL:

driver = 'SQL Server'
server = '123'
tcon = 'yes'
uname = 'name'
pword = 'password'
query = "query1"

I make my databases into list as below:

db = ['DBA','DBB','DBC']

Then execute looping to call all databases in the list above as below:

for i in db:
  sql_conn = pyodbc.connect(driver='{SQL Server}', host=server, database= f'{i}',
                  trusted_connection=tcon, user=uname, password=pword)

  df = pd.read_sql(query, sql_conn)

  df['DB_NAME'] = f'{i}' #to add name column in the dataframe

However, i only get data from 'DBC'. I want three separate dataframes such as df_DBA, df_DBB, df_DBC from the looping above.

Upvotes: 0

Views: 787

Answers (1)

Gary Goh
Gary Goh

Reputation: 244

In each loop, df is overwriten with a new dataframe. So it will hold data from the last iteration of the loop, which is from DBC since it is the last in the list.

If you want separate variables for data from each database, try this:

def read_sql(query, db_name):
    conn = pyodbc.connect(driver='{SQL Server}', host=server, database=db_name,
                          trusted_connection=tcon, user=uname, password=pword)
    df = pd.read_sql(query, conn)
    df['DB_NAME'] = db_name
    return df


db_DBA = read_sql(query, 'DBA')
db_DBB = read_sql(query, 'DBB')
db_DBC = read_sql(query, 'DBC')

Upvotes: 1

Related Questions