Error when querying REST API

[MollyMoore]

I am pretty new to working with the REST API and I'm trying to query a feature service on our organization's server using arcpy. Here is my code:

fs_url = r"https://maps.waterlandlife.org/arcgis/rest/services/PNHP/NHAEdit/FeatureServer/1/query?where=STATUS='C'&outFields=*&returnGeometry=true&f=JSON" 
fs = arcpy.FeatureSet()
fs.load(fs_url)

I have tried to change the options for nearly all of the parameters here and tried including other parameters or excluding some of these. I keep getting the following error:

RuntimeError: RecordSetObject: Cannot open table for Load

I'm able to load the feature set fine without the query and have looked at a ton of resources online and can't find any solutions that have worked. Can anyone help me out with what I'm doing wrong? Thanks!

[DavidPike]

have you supplied a token with the url? &token=

[MollyMoore]

I have not. I figured since it was letting me use the feature service layer without the query that this wasn't the issue. But, I'll try this now.

[JoshuaBixby]

Are you using Pro or ArcGIS Desktop/ArcMap?  If you are using ArcMap, what you are trying won't work.  If you are using Pro, you need to break your query out separate from the URL to the hosted feature layer.

[MollyMoore]

Yes, this is being used in a toolbox that I'm running through Pro. Do you mean that I need to bring in the data and then query it in another step? Or is there a way to separate the query from the URL in the feature service request? If the latter, is there an example of this somewhere? I couldn't find anything like this.

[JoshuaBixby · Accepted solution]

In this case, the FeatureSet - ArcGIS Pro | Documentation gives an example, FeatureSet example 2.  The query needs to be passed as a separate argument and not part of the URL.

[MollyMoore]

Thank you! This works for the given example. However, I'm unable to pass a list query. I can pass the list query without error, but it ignores it and loads all features into the feature set. This is what I'm passing:

nha_expression = "NHA_JOIN_ID IN ({0})".format(','.join(nha_ids))
nha_core_fs = arcpy.FeatureSet()
nha_core_fs.load(nha_core_url,nha_expression)

The nha_expression looks like this:

'NHA_JOIN_ID IN (xxx42966,xxx42967,xxx43009)'

I am able to drop the necessary records after I load in the features with an update cursor, but I figured it would be a lot more elegant and faster to just not load them in the first place through the where clause, so if anyone has ideas about why it's not honoring the query, I'd appreciate it!

[Anonymous User · Accepted solution]

I think the list items need to be in quotes.

nha_expression = "NHA_JOIN_ID IN ('{}')".format("','".join(f for f in nha_ids))

to make it

"NHA_JOIN_ID IN ('xxx42966','xxx42967','xxx43009')"

[JoshuaBixby · Accepted solution]

Swap the double and single quotes:   SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

Strings must always be enclosed in single quotation marks in queries, for example: STATE_NAME = 'California'

[MollyMoore]

This did the trick (with the single quotes). Thank you all!