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

2499
3
10-30-2017 04:16 AM
Lakshmi_KanthanMuralikrishnan
New Contributor III

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. 

The CSV file and the hosted feature layer

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

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:

  1. I've tried the query with simple numeric queries like (where='dead>=100', ...) and it works!
  2. When there was an error in the where keyword-argument value, suitable error was raised. So, I think this could be ruled out. 
  3. 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!

0 Kudos
3 Replies
JohnYaist1
Esri Contributor

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?

Lakshmi_KanthanMuralikrishnan
New Contributor III

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.

0 Kudos
JohnYaist1
Esri Contributor

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.

0 Kudos