arcgis.features.FeatureLayer.query() method returns Database error (500)

Lakshmi_KanthanMuralikrishnan — Sun, 12 Dec 2021 09:33:04 GMT

Hi!

I've been exploring ArcGIS API for Python. Currently, I am working on querying a feature layer using the same.

Some Background: (on the feature layer queried)
- The data was added in the Portal as an 'Item' using the arcgis.gis.GIS.content.add() method from a CSV file.
- Then, the item was published using the arcgis.gis.GIS.Item.publish() method.

Feature Layer Info: (from the hosted service)

Layer: miss_mig_date_clean (ID: 0)

Fields:

objectid ( type: esriFieldTypeOID , alias: objectid , nullable: false , editable: false )

id ( type: esriFieldTypeInteger , alias: id , editable: true , nullable: true )

cause_of_death ( type: esriFieldTypeString , alias: cause_of_death , editable: true , nullable: true , length: 8000 )

region_origin ( type: esriFieldTypeString , alias: region_origin , editable: true , nullable: true , length: 8000 )

affected_nationality ( type: esriFieldTypeString , alias: affected_nationality , editable: true , nullable: true , length: 8000 )

missing ( type: esriFieldTypeInteger , alias: missing , editable: true , nullable: true )

dead ( type: esriFieldTypeInteger , alias: dead , editable: true , nullable: true )

incident_region ( type: esriFieldTypeString , alias: incident_region , editable: true , nullable: true , length: 8000 )

date_ ( type: esriFieldTypeDate , alias: date , editable: true , nullable: true , length: 29 )

source ( type: esriFieldTypeString , alias: source , editable: true , nullable: true , length: 8000 )

reliability ( type: esriFieldTypeString , alias: reliability , editable: true , nullable: true , length: 8000 )

lat ( type: esriFieldTypeDouble , alias: lat , editable: true , nullable: true )

lon ( type: esriFieldTypeDouble , alias: lon , editable: true , nullable: true )

A snippet of the feature layer table from Portal for ArcGIS

I am trying to execute the following query against this feature layer:

#mmig_fl is a arcgis.features.layer.FeatureLayer object

mmig_fl.query(where='cause_of_death LIKE Mixed',
return_count_only=True)

The query returns a Database Error as:

---------------------------------------------------------------------------RuntimeError                              Traceback (most recent call last)<ipython-input-104-5e79550b0106> in <module>()      2       3 mmig_qr_syria = mmig_fl.query(where='cause_of_death LIKE Mixed', ----> 4                               return_count_only=True)      5 mmig_qr_syria D:\Anaconda3\envs\AGISAPIPy\lib\site-packages\arcgis\features\layer.py in query(self, where, out_fields, time_filter, geometry_filter, return_geometry, return_count_only, return_ids_only, return_distinct_values, return_extent_only, group_by_fields_for_statistics, statistic_filter, result_offset, result_record_count, object_ids, distance, units, max_allowable_offset, out_sr, geometry_precision, gdb_version, order_by_fields, out_statistics, return_z, return_m, multipatch_option, quanitization_parameters, return_centroid, return_all_records, **kwargs)    388     389         if not return_all_records:--> 390             return self._query(url, params)    391     392         params['returnCountOnly'] = TrueD:\Anaconda3\envs\AGISAPIPy\lib\site-packages\arcgis\features\layer.py in _query(self, url, params)    818         """ returns results of query """    819         result = self._con.post(path=url,--> 820                                 postdata=params, token=self._token)    821         if 'error' in result:    822             raise ValueError(result)D:\Anaconda3\envs\AGISAPIPy\lib\site-packages\arcgis\_impl\connection.py in post(self, path, postdata, files, ssl, compress, is_retry, use_ordered_dict, add_token, verify_cert, token, try_json, out_folder, file_name, force_bytes, add_headers)   1127                 elif errorcode == 498:   1128                     raise RuntimeError('Invalid token')-> 1129                 self._handle_json_error(resp_json['error'], errorcode)   1130                 return None   1131         except AttributeError:D:\Anaconda3\envs\AGISAPIPy\lib\site-packages\arcgis\_impl\connection.py in _handle_json_error(self, error, errorcode)   1147    1148         errormessage = errormessage + "\n(Error Code: " + str(errorcode) +")"-> 1149         raise RuntimeError(errormessage)   1150    1151 class _StrictURLopener(request.FancyURLopener):RuntimeError: Database error has occurred. (Error Code: 500)

---------------------------------------------------------------------------

I've also tried the query quoting the string literal used in comparison - mmig_fl.query(where='cause_of_death LIKE "Mixed"', ...), but end up with the same.

I'd like to know what could possibly cause this error. Please let me know if more details are required from my end!

FYI:

I've tried the query with simple numeric queries like (where='dead>=100', ...) and it works!
When there was an error in the where keyword-argument value, suitable error was raised. So, I think this could be ruled out.
The Feature Layer was read with necessary authentication (using gis=<current_gis_instance_which_has_access_to_data> argument in the arcgis.features.FeatureLayer() method).

Thanks for your time and attention!

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)

JohnYaist1 — Wed, 08 Nov 2017 19:24:56 GMT

This query would work to return a count of features with the cause_of_death attribute value of Mixed:

mmig_fl.query(where="cause_of_death LIKE 'Mixed'", return_count_only=True)‍‍

The 500 HTTP error code means the server couldn't process the request for some reason. Do your Server logs have any more detailed information from when the error occurred?

How many features are returned when you run that where clause in the query dialog on the rest endpoint of the hosted feature layer?

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)

Lakshmi_KanthanMuralikrishnan — Mon, 13 Nov 2017 15:19:25 GMT

Hi John,

Thank you for your reply!

These are the Server logs for the error generated:

SEVERE

Nov 13, 2017, 8:26:37 PM

Database error has occurred.

Rest

SEVERE

Nov 13, 2017, 8:26:37 PM

0 : ERROR: column "Mixed" does not exist Position: 132

Rest

It seems like the query is treating "Mixed" as a column (though I do not know why it would)?

So, I changed the query as:

mmig_fl.query(where='"Mixed" LIKE "cause_of_death"',
return_count_only=True)

Still the error persists (you can see the change in the position from 132 to 112):

SEVERE

Nov 13, 2017, 8:27:18 PM

Database error has occurred.

Rest

SEVERE

Nov 13, 2017, 8:27:18 PM

0 : ERROR: column "Mixed" does not exist Position: 112

Rest

What could cause this error? I do not think I am messing with the SQL Query. But, somehow, it is misinterpreted.

(P.S. - I assume the SQL Query syntax in ArcGIS API for Python is same as that of ArcGIS Desktop. If not, is there a reference you can point me to?)

Regards,

Lakshmi Kanthan M.

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)

JohnYaist1 — Thu, 04 Jan 2018 00:38:33 GMT

Hi Lakshmi - Ok. So Let's try a query from the API just to make sure that we're getting results. Try this:

mmig_fl.query(where="1=1", return_count_only=True)‍

Does that return the expected number of features? Can you also try to run the query from the where clause on the rest endpoint of the layer? What results do you get from that?

Let me know what you find out.

topic Re: arcgis.features.FeatureLayer.query() method returns Database error (500) in ArcGIS API for Python Questions

arcgis.features.FeatureLayer.query() method returns Database error (500)

Layer: miss_mig_date_clean (ID: 0)

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)

Re: arcgis.features.FeatureLayer.query() method returns Database error (500)