0

I've got a simple SQL query that works fine from a mysql command line:

select dateandtime from times where their_id=0053 and dateandtime>= 2022-08-24 and dateandtime<=DATE_ADD("2022-08-24", INTERVAL 7 DAY);

If I try to do this programatically by doing either:

params = (their_id,date,date," INTERVAL 7 DAY")
query = "select dateandtime from times where their_id=%s and dateandtime>= %s and dateandtime<=DATE_ADD(%s,%s) ";
cursor.execute(query,(params))

or

params = (their_id,date,"\""+date+"\""," INTERVAL 7 DAY")
query = "select dateandtime from times where their_id=%s and dateandtime>= %s and dateandtime<=DATE_ADD(%s,%s) ";
cursor.execute(query,(params))

I get: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' INTERVAL 7 DAY')' at line 1

The obvious question is, is there any way to parameterise this query? Secondly, though, to help with similar problems in future, is there any way with a parameterised query to see the query as interpreted by MYSQL once the paramaters have been parsed?

Any help would be very gratefully received!

iwmike
  • 21
  • 2
  • 1
    Parameterized queries don't use string interpolation. They pass the parameter values directly to the compiled execution plan generated from a query. `INTERVAL 7 DAY` isn't a value, it's an expression that's part of the `DATE_ADD` call. Why add days in SQL anyway? You can calculate the start and end dates in python and use `datefield BETWEEN %s and %s` – Panagiotis Kanavos Aug 24 '22 at 15:34
  • @PanagiotisKanavos - Thank you so much! I've sketched out an answer, but I'm not sure how the economics of stackoverflow works - I guess you get credit for an answer? Please let me know if you want to provide an answer that I can vote for, in which case I'll delete mine. – iwmike Aug 24 '22 at 15:55
  • Your second question, how to get the query after parameters have been combined, is only possible in the query log. See my answer here: https://stackoverflow.com/a/210693/20860 That question is about PHP, but the same answer applies regardless of language. – Bill Karwin Aug 24 '22 at 16:58

1 Answers1

0

As per Panagiotis Kanavos's answer above, using a query of the form:

select dateandtime from times where dateandtime BETWEEN %s and %s

... and supplying the dateandtime parameters with python datetimes, rather than strings, fixed the problem.

T.S.
  • 18,195
  • 11
  • 58
  • 78
iwmike
  • 21
  • 2