Just hoping someone can give me a hand, I am trying filter out rows from a MariaDB database using a start and end date as conditions. Currently, I am getting a SQL Error from the current script and I am unsure why, anyone able to offer advice? (NOTE: for this demo code I am using the alarm_events table but it is identical in my other code for a different database.)
startDate = system.date.addDays(system.date.now(), -1)
endDate = system.date.now()
query="SELECT * FROM alarm_events WHERE eventtime >= {startDate} AND eventtime <= {endDate} ORDER BY eventtime DESC".format(startDate=startDate, endDate=endDate)
result = system.db.runQuery(query)
for row in result:
rowData = [item for item in row]
print(rowData)
Always use system.db.runPrepQuery over runQuery if you need any arguments. Seriously, always.
Right now you're relying on whatever string formatting for a date happens to get inserted into your SQL query. This is bad; it's locale and scripting environment dependent, it's a performance cliff, it's a practice that can lead to SQL injection vulnerabilities - it's just a bad time all around.
Using system.db.runPrepQuery, you can substitute arbitrary Python and Java objects into your query, and they will be sent "as-is" (subject to what the JDBC driver involved can deal with) to your DB - with no lossy formatting to string.
Just changing the query and result lines should do it:
query = """
SELECT *
FROM alarm_events
WHERE eventtime >= ?
AND eventtime <= ?
ORDER BY eventtime DESC
"""
result = system.db.runPrepQuery(query, [startDate, endDate])