Python API query string

921
6
Jump to solution
11-11-2019 05:44 AM
GrantHaynes
Occasional Contributor

I'm trying to use the Python API in a regularly scheduled script to query data from a table, I'd like to query the weeks data for processing. I can query any non date field just fine, but whenever I try to query a date field I get either a 'where' parameter invalid or "Unable to perform query. Please check your parameters." Below is the code, I have also tried numerous different date formats, but to no avail.

timeDifference = datetime.datetime.now() - datetime.timedelta(days = 7)
queryString1 = 'Survey_Start>' + str(timeDifference)
query1Result = table.query(where=queryString1, out_fields='Project')
Tags (1)
0 Kudos
1 Solution

Accepted Solutions
GrantHaynes
Occasional Contributor

I figured it out, the query string needs the dates in a very specific format, thanks for helping out.

# time difference for querying
timeDifference = datetime.datetime.now() - datetime.timedelta(days = 7)

# create the query string for the first query
queryString1 = 'Survey_Start > date \'{}\''.format(timeDifference.strftime('%Y/%m/%d %H:%M:%S'))

View solution in original post

6 Replies
JohannesBierer
Regular Contributor

And if you use .format() ?

queryString1 = 'Survey_Start>{}'.format(str(timeDifference))
DanPatterson_Retired
MVP Esteemed Contributor

You don't have to str objects prior to using python 'format' or f strings

"{} - {} = {} ".format(2, 1, 2-1)

'2 - 1 = 1 '
0 Kudos
GrantHaynes
Occasional Contributor

It still fails

timeDifference = datetime.datetime.now() - datetime.timedelta(days = 7)
queryString1 = 'Survey_Start>{}'.format(str(timeDifference))
query1Result = table.query(where=queryString1, out_fields='Project')

message

'where' parameter is invalid

0 Kudos
JohannesBierer
Regular Contributor

What happens if you print out queryString1, maybe it doesn't fit to Survey_Start field?

Is this helpful?

4 Examples to convert Python datetime to string 

0 Kudos
GrantHaynes
Occasional Contributor

I figured it out, the query string needs the dates in a very specific format, thanks for helping out.

# time difference for querying
timeDifference = datetime.datetime.now() - datetime.timedelta(days = 7)

# create the query string for the first query
queryString1 = 'Survey_Start > date \'{}\''.format(timeDifference.strftime('%Y/%m/%d %H:%M:%S'))
J_R_Matchett
New Contributor III

Here's a useful post on the various date/time query formats for feature services: Querying Feature Services: Date-Time Queries 

0 Kudos