Append Query Results to Different Sheets in Excel in python

Question

I'm using the code below to run 3 different queries in 3 different DB's And trying to export them to a single Excel file each result on a different sheet (9 total sheets).

But when running the code below I'm getting a file with only 3 sheets (each contain only the first query result on each DB) I'm using pyodbc for the DB connection and pandas in order to handle the Excel writing.

databases = {DB1, DB2, DB3}
queries = {query1, query2, query3}

writer = pd.ExcelWriter('C:\Temp\Output.xlsx')

for database in databases:
   cnxn = pyodbc.connect(driver='{SQL Server}', 
                         host=database.server, 
                         database=database.db_name,
                         trusted_connection=database.trusted_connection, 
                         user=database.user_name, 
                         password=database.password)
   cursor = cnxn.cursor()

   for q in queries:
      cursor.execute(q)
      rows = cursor.fetchall()
      df = pd.read_sql_query(q, cnxn)
      df.to_excel(writer, 
                  sheet_name=str(q.index(q))+"-"+database.name)
writer.save()

Where does XML come into this? Anyway - instead of writing after executing each query, why not build up a single dataframe of all queries, then write? — Jon Clements
– Jon Clements, Commented Apr 1, 2018 at 15:26
Also - if you're using cursor.execute and then rows = cursor.fetchall() - why do you then use pd.read_sql_query (executing the query again) instead of feeding the previous stuff straight into a DataFrame? — Jon Clements
– Jon Clements, Commented Apr 1, 2018 at 15:29
str(q.index(q)) this always give 0 , so are u getting sheets 0_DBNAME — Rehan Azher
– Rehan Azher, Commented Apr 1, 2018 at 15:33
@JonClements I Tried commenting the 2 in a hope that pd.read_sql_query (q, cnxn) would give what i need , but it kept getting empty Output until returning them — Yonnatan Bar
– Yonnatan Bar, Commented Apr 1, 2018 at 22:28

Parfait · Accepted Answer · 2018-04-02 14:18:18Z

2

Consider enumerate in inner loop to get the iterator number with variable for sheet naming.

...
for i, q in enumerate(queries):
  df = pd.read_sql_query(q, cnxn)
  df.to_excel(writer, 
              sheet_name=str(i+1)+"-"+database.name)
...

Alternatively, if queries is a list, you could use the index approach:

queries = [query1, query2, query3]

...
for q in queries:
  df = pd.read_sql_query(q, cnxn)
  df.to_excel(writer, 
              sheet_name=str(queries.index(q)+1)+"-"+database.name)
...

edited Apr 2, 2018 at 14:18

answered Apr 1, 2018 at 23:46

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yonnatan Bar Over a year ago

It worked , But i`m still not sure why index(q) does not provide me with the same thing when iterating through the queries .

Parfait Over a year ago

I believe you intended to index a list but used the value of a set. Had queries been a list, you could use queries.index(q).

Collectives™ on Stack Overflow

Append Query Results to Different Sheets in Excel in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related