How to pass updates into pd.read_sql? Update

Question

I have this query that I use on a weekly basis to create a report using pd.read_sql. I want to be able to update the case statement of store, update the Date Add, and store IN at the end of the statement without having to manually change the store numbers and the dates. Is there any way that I can edit the query to make the updates?

This is the query

dataframe = pd.read_sql("""
            SELECT Top(10)
    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] = 313 THEN 3174
    WHEN [Store] = 126 THEN 3191
    END AS Store
      , DATEADD (YEAR, +2, DATEADD(DAY, +4, 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,'2/8/2019') and CONVERT(datetime,'3/15/2019')
AND 
Store IN (313, 126) 
--Single Store: Store = Store #
) AS A
) AS B
ORDER BY Tran_Dt2, Store
            """, con = conn)

I would want to be able to do something like declare a variable and have it populate in the code such as something like:

oldstore1 = 313
newstore1 = 3174
oldstore2 = 126
newstore2 = 3191

daframe = pd.ready_sql("""...  
...
   SELECT 
    CASE 
    WHEN [Store] = oldstore1 THEN newstore1
    WHEN [Store] = oldstore2 THEN newstore2
...

UPDATE----

I am currently at this point and had the query working until my kernel restarted and I lost my code. Any tips on why it isn't working anymore?

#Declare variables for queries
old_store1 = 313
new_store1 = 3157
old_store2 = 126
new_store2 = 3196
datefrom = '2/8/2019'
dateto = '3/15/2019'
yearadd = '+2'
dayadd = '+4'

ITC = pd.read_sql("""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
""", con = conn)

You'll want to utilize the `params=` argument in the `read_sql()` or `read_sql_query()` method. See https://stackoverflow.com/q/57825950/9987623, https://stackoverflow.com/q/24408557/9987623 — AlexK, Mar 29 '21 at 04:35

score 0 · Answer 1 · answered Apr 05 '21 at 18:55

Was able to figure out why it wasn't working. I guess python 3 and beyond has a built in function that allows you to place "f" in front of the query and will let you pass the variables you created. I know this isn't the most secure way of executing the script but I'll look into creating a for loop in the future that will allow it to be more secure. Thanks for all the help!

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

ITC = pd.read_sql(f"""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}
    .....
#run code and verify it works
Sales_Drivers_ITCSignup = pd.read_sql(ITCQuery, con = conn, index_col='Store')
Sales_Drivers_ITCSignup.head()

How to pass updates into pd.read_sql? Update

1 Answers1