I'm having trouble passing my where clause for arcpy.SearchCursor as a variable.

The following code successfully generates a query for me:

PolyList = "C:\\Users\\korora912\\Desktop\\polygons.shp"
cursor = arcpy.SearchCursor(PolyList,"""date > date '2016-01-01 00:00:00' AND date < date '2016-01-04 00:00:00'""")

However when I try to pass the dates as variables, like so:

StartDate = "2016/01/01"
date_1 = datetime.datetime.strptime(StartDate, "%Y/%m/%d")
end_date = date_1 + datetime.timedelta(days=3)
where_clause = "\"\"\"date > date '%s' AND date < date '%s'\"\"\"" %(date_1,end_date)
cursor = arcpy.SearchCursor(PolyList,where_clause)

I get: "RuntimeError: ERROR 999999: Error executing function. An invalid SQL statement was used."

This, despite the fact that

print where_clause

returns the exact string that succeeds above:

"""date > date '2016-01-01 00:00:00' AND date < date '2016-01-04 00:00:00'"""

I know I must be doing something wrong, but with my limited Python and SQL experience, I'm at a loss. Can any of you with a more robust background help?


You don't need the triple quotes at the start and the end. Try:

where_clause = "date > date '%s' AND date < date '%s'" %(date_1,end_date)
  • Not that it matters now, but is there a reason that the query succeeded with the triple-quotes when typed out explicitly, but not when generated using a variable?
    – korora912
    Apr 1 '16 at 19:41
  • @korora912 because "\"\"\"date > date '%s' AND date < date '%s'\"\"\"" %(date_1,end_date) is passing the quotes as part of the string, not as the string quotes themselves: """date > date '%s' AND date < date '%s'""" %(date_1,end_date)
    – Midavalo
    Sep 2 '16 at 0:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.