Python globals not generating into code in read_sql execution

65 Views Asked by At

I have to import data into a query where I want to fetch global variables that I declare. It works when I pass through one variable but not when I assign the multiple variables into the query. It is running but the result is not giving me back any data. Any thoughts on why my query isn't resulting in any data?

#Declare variables for queries
globals()['old_store1'] = 313
globals()['new_store1'] = 3157
globals()['old_store2'] = 126
globals()['new_store2'] = 3196
globals()['datefrom'] = '2/15/2019'
globals()['dateto'] = '3/22/2019'
globals()['yearadd'] = '+2'
globals()['dayadd'] = '+4'

#Assign ITC Query
ITCQuery = """SELECT 
    CAST( Store as VARCHAR)  + 'þ' as Store,
    CONVERT( VARCHAR, Tran_Dt2, 101 ) + 'þ' as Tran_Dt,
    CONVERT(char(5), Start_Time, 108) + 'þ' as Start_Time, 
    [Count]  
 FROM
 (
  SELECT 
    CASE 
    WHEN [Store] = {old_store1} THEN {new_store1}
    WHEN [Store] = {old_store2} THEN {new_store2}
    END AS Store
      , DATEADD (YEAR, {yearadd}, DATEADD(DAY, {dayadd}, Tran_Dt2)) as Tran_Dt2
      ,[Start_Time]
      ,[Count]
      ,Store as Sister_Store
 FROM 
(
SELECT 
Store,
CONVERT(datetime, Tran_Dt) as Tran_Dt2,
Start_Time,
Count 
FROM [VolumeDrivers].[dbo].[SALES_DRIVERS_ITC_Signup_65wks]
WHERE CONVERT(datetime, Tran_Dt)  between CONVERT(datetime,{datefrom}) and CONVERT(datetime,{dateto})
AND 
Store IN ({old_store1}, {old_store2}) 
--Single Store: Store = Store #
) AS A
) AS B
ORDER BY Tran_Dt2, Store
            """

#print(ITCQuery)

#execute query
Sales_Drivers_ITCSignup = pd.read_sql(ITCQuery.format(**globals()), con = conn)
Sales_Drivers_ITCSignup.head()
0

There are 0 best solutions below