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

Question

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) in () 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 + " (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= argument in the arcgis.features.FeatureLayer() method). Thanks for your time and attention!

JohnYaist1 · Answer

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.

Lakshmi_KanthanMuralikrishnan · Answer

Hi John,

Thank you for your reply!

These are the Server logs for the error generated:

SEVERENov 13, 2017, 8:26:37 PMDatabase error has occurred.Rest

SEVERENov 13, 2017, 8:26:37 PM0 : ERROR: column "Mixed" does not exist Position: 132Rest

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):

SEVERENov 13, 2017, 8:27:18 PMDatabase error has occurred.Rest

SEVERENov 13, 2017, 8:27:18 PM0 : ERROR: column "Mixed" does not exist Position: 112Rest

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.

JohnYaist1 · Answer

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?