1

I am wanting to run a Presto SQL query in a for loop so that the query will pull hourly data based on my date variables.

Example query is along the lines of:

x = datetime.strptime('12-10-22', '%d-%m-%y').date()
y = datetime.strptime('13-10-22', '%d-%m-%y').date()

for dt in rrule.rrule(rrule.HOURLY, dtstart=nextProcStart, until=nextProcEnd):
    sql_query = "SELECT SUM(sales) FROM a WHERE date between x and y"

I will note I'm using the syntax of writing the SQL query as a variable so along the lines of: sql_query = """ SELECT... FROM..."""

I have tried just adding the variables into the query but no luck. Unsure what steps will work.

I've also tried using .format(x,y) at the end of my SQL query but keep getting an error saying KeyError: 'x'

Galo do Leste
  • 703
  • 5
  • 13
Garrett Kochner
  • 11
  • 3

1 Answers1

0

Remember that your SQL statement is no more than a string, so you just need to know how to incorporate a variable into a string. Try:

sql_query = "SELECT SUM(sales) FROM a WHERE date between {} and {}".format(x, y)

Read How do I put a variable’s value inside a string (interpolate it into the string)? for more info or alternative methods.

Hopefully this answers your immediate question above on how to incorporate variable into string and get your code, as is, to work. However, as @nbk, mentions in comment below, this method is NOT recommended as it is insecure.

Using concatenations in SQL statements like this does open the code up to injection attacks. Even if your database does not contain sensitive information, it is bad practice.

Prepared statements have many advantages, not least of all that they are more secure and more efficient. I would certainly invest some time in researching and understanding SQL prepared statements.

Galo do Leste
  • 703
  • 5
  • 13
  • prepared statements would be better if supported – nbk Feb 06 '23 at 20:48
  • That is definitely true @nbk but I suspect the OP may be at early stage of understanding the coding. Prepared statements would necessitate re-writing the code into something they may struggle to decipher. I figured small steps are preferable – Galo do Leste Feb 06 '23 at 20:53
  • as this is still insecure, you may get downvotes for it, as answers should be as secure as possible, so waiting till the user has responded to my comment would be better – nbk Feb 06 '23 at 21:02
  • @nbk, point taken. – Galo do Leste Feb 06 '23 at 21:08