Python Forum

the MSSQL Query works just fine by its self in SQL Studio and if I remove
this line

and  ompRequestedShipDate >=getdate() and ompRequestedShipDate <=getdate()+7

it will work in python. But with this line it gets the column names and nothing else. no error when I remove the line I get data in python.

import datetime
import pyodbc
import xlsxwriter
import pandas as pd
from os import environ, lseek
from datetime import timedelta
#from plyer import notification 
#from datetime import datetime
begin = pd.to_datetime("today")


CONNECTION_STRING="""Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1;
                     Server=server;
                     Database=M1_KF;
                     UID=who;
                     PWD=password;"""
connection = pyodbc.connect(CONNECTION_STRING)
with pd.ExcelWriter("Output.xlsx", engine="xlsxwriter", options = {'strings_to_numbers': False, 'strings_to_formulas': False}) as writer:
      
        try:
            df = pd.read_sql_query("""select top 10000 omlPartID as Part 
,CONVERT(varchar(7),ompRequestedShipDate,100) as day 
,sum(omlOrderQuantity) as ordered  
,CASE WHEN imbWarehouseID='' THEN 'WH1' 
	  WHEN imbWarehouseID='WH2' THEN 'WH2' else 'None' end as Warehouse 
,cast(imbQuantityOnHand as INT) as onhand 
from M1_KF.dbo.SalesOrders 
left outer join M1_KF.dbo.SalesOrderLines on omlSalesOrderID=ompSalesOrderID 
left outer join M1_KF.dbo.Parts on impPartID=omlPartID 
left outer join M1_KF.dbo.PartRevisions on imrPartID=omlPartID 
left outer join M1_KF.dbo.PartBins on imbPartID=omlPartID 
where ompClosed!=-1 and imbWarehouseID!='vw'  and UOMPTRUCKNUMBER!='' 
and  ompRequestedShipDate >=getdate() and ompRequestedShipDate <=getdate()+7
group by omlPartID,ompRequestedShipDate,imbWarehouseID 
,imbQuantityOnHand""",connection)
            df.to_excel(writer,sheet_name="Sheet1", header=True , index =False)
            
            connection.close()
            print("File saved successfully")

        except:
            print("There is an error")