How can I write every loop result from SQL in python?-CodePudding

Here is my code. In the excel file, it should have 4 tables, including date = 1/3,1/7,1/14,1/21. I have run the query, it showed 4 results. However, when I wrote to the excel, the file only has one table, which was date = 1/3. I'm wondering how can I correct this, thanks!

    import pyodbc 
    import pandas as pd
    from datetime import datetime,timedelta
    
    cnxn = pyodbc.connect('DRIVER=xx; SERVER=xx; DATABASE=xx; UID=xx; PWD=xx')
    
    cursor = cnxn.cursor()

    query="""
    declare @START_ORDATE  DATETIME
    declare @END_ORDATE  DATETIME
    set @START_ORDATE  ='2022-01-03 00:00:00:000'
    set @END_ORDATE  ='2022-01-24 00:00:00:000'
    
    WHILE @START_ORDATE<=@END_ORDATE
    BEGIN
    select xx,xx,xx...
    
    set @START_ORDATE  = @START_ORDATE   7
    
    END
    """
    
    df = pd.read_sql_query(query, cnxn)
    
    writer = pd.ExcelWriter('test1.xlsx')
    df.to_excel(writer, sheet_name='test000')
    writer.save()

CodePudding user response：

I solved it! Thanks for any help：）

import pyodbc 
import pandas as pd
from datetime import datetime,timedelta

cnxn = pyodbc.connect('DRIVER=xx; SERVER=xx; DATABASE=xx; UID=xx; PWD=xx;MARS_Connection=yes')

cursor = cnxn.cursor()
start = datetime.strptime('2022-01-03','%Y-%m-%d')
end = datetime.strptime('2022-10-24','%Y-%m-%d')

query="""

...

"""

while start<=end:
   cursor.execute(query,(start,start,start timedelta(days=6)))

   df = pd.DataFrame.from_records(cursor.fetchall(),columns=[desc[0] for desc in cursor.description])
   start = start   timedelta(days=7)

CodePudding user response：

You made need to use a for loop on the actual object to loop through what was returned. Let me know if this helps.

writer = pd.ExcelWriter('test1.xlsx')
for x in df:
    df.to_excel(writer, sheet_name='test000')
writer.save()