0
conn=pyodbc.connect('Driver={SQL Server};'
'Server=test123;'
'Trusted_Connection=yes;')

data = []
date_x = ["202101","202010","202007"]
business_y = ["sales","marketing","tech"]
      
for i in date_x, business_y:
    sql_query = """ 
     Select name, period, business, purchase_date
     From test_table 
     Where period = ? #(date_x)
     AND business = ? #(business_y)
     """
     conn.execute(sql_query)
     data.append(pd.read_sql(sql_query, conn))

print(data)

I am trying to run this SQL statement through a loop, and each iteration I want to populate the placeholder "?" with the correct variables as shown in my example above and execute the queries, writing results to the variable data

Cheech
  • 21
  • 6

1 Answers1

0

This should work, assuming the length of date_x and business_y are always the same

data = pd.DataFrame()
date_x = ["202101","202010","202007"]
business_y = ["sales","marketing","tech"]
counter = 0
      
for i in date_x:
    sql_query = """ 
     Select name, period, business, purchase_date
     From test_table 
     Where period = {}
     AND business = {}
     """.format(date_x[counter], business_y[counter])
    counter +=1
    #print(sql_query)
    
    conn.execute(sql_query)
    data = data.append(pd.read_sql(sql_query, conn))

Results:

  Select name, period, business, purchase_date
 From test_table 
 Where period = 202101
 AND business = sales
 

 Select name, period, business, purchase_date
 From test_table 
 Where period = 202010
 AND business = marketing
 

 Select name, period, business, purchase_date
 From test_table 
 Where period = 202007
 AND business = tech

Updated to show how to execute and store results to DF

JD2775
  • 3,658
  • 7
  • 30
  • 52
  • Thanks! This is almost what I want. How can I execute these queries and write the results to a datadrame? Sorry I dont think I was clear in my post actuially – Cheech Mar 10 '21 at 19:37
  • I updated the code. You were close, but you were defining data as a list []. You need to define it as a dataframe, and when you append to it you need to do data = data.append, rather than data.append. I cant test this obviously but it should be close to what you need – JD2775 Mar 10 '21 at 19:45
  • This code is vulnerable to [sql-injection](https://realpython.com/prevent-python-sql-injection/). Follow the link for the right way to do it. Get in the habit of doing it right even in cases where you are sure you know you have good inputs. Code often gets modified for future needs. – Steven Rumbalski Mar 10 '21 at 19:50
  • @StevenRumbalski is probably correct. Our projects are all internal, nothing public facing so we can get away with it but yea it may not be best practice as Steven said – JD2775 Mar 10 '21 at 19:59
  • Yup this is an internal use also so I should be good, but definitely good practice. I am running the code now and will report back @JD2775 – Cheech Mar 10 '21 at 19:59
  • Looks like it worked!! Question, what is the point of the counter? Never saw that before @JD2775 – Cheech Mar 10 '21 at 20:48
  • The counter is just an incrementer. It could be named anything. It starts at 0 and each loop it increments by 1, thereby incrementing in the lists by 1 (moving from left to right by index) and storing that value in the sql placeholders {}. You can accept the answer as solved if you want, but Id also encourage you to investigate the other method that @StevenRumbalski came up with as a better practice for you in the long run – JD2775 Mar 10 '21 at 20:56
  • So the counter is required to do the loop? I marked as solved and reviewing the vulnerabilities – Cheech Mar 10 '21 at 21:07
  • It needs some way to iterate through the lists and populate the placeholders, so yes, it is required in that case if you want it to dynamically update the SQL statement with the appropriate values. If you just wanted to loop through the lists and print out the values you wouldnt need it, but thats not the use case here – JD2775 Mar 10 '21 at 21:10
  • @JD2775 Hey I have another question. I noticed in your answer you said "assuming length is always the same", what did you mean by this? If I add more to my date and business variable (If say instead of 3 things in each variable, I had 6 things each or something), will the loop work still? or does the counter need to be changed? When I run the code, I noticed it it only took 2 items inside the variables and not all 3 items. – Cheech Mar 10 '21 at 22:35
  • What I meant is they need to be the same length as each other. They can each be 500 items long that doesnt matter as long as they match each other. BTW I just noticed an error in the code, you are calling conn twice. Get rid of the line: conn.execute(sql_query) – JD2775 Mar 10 '21 at 23:10
  • @JD2775 ah ok that makes sense, I follow ya. Does the "counter += 1" need to be adjusted if I add more items into the variables? or does it always stay +=1 ? Thanks for all the help, you really cleared a lot up for me here. – Cheech Mar 10 '21 at 23:17
  • No prob...and that stays the same....no matter the length of the list(s). it will increment to the end of the lists, by 1, whether its 3 items long or 300. Count +=1 is the same as writing counter = counter + 1. So its basically updating the counter value with each loop. 0, 1, 2......300 – JD2775 Mar 10 '21 at 23:20
  • Any ideas why I am only getting results back for the first 2 items in my variables and not the third? Not sure if it is something in the code or maybe is on my side. – Cheech Mar 11 '21 at 18:34
  • Make sure you are doing for i in date_x and not for i in date_x, business_y – JD2775 Mar 11 '21 at 19:23
  • Yup that was my issue, all good now!! Thank you. – Cheech Mar 13 '21 at 20:21