topic Re: Use a SQL expression inside a Python or Arcade field calculation? in Python Questions

Use a SQL expression inside a Python or Arcade field calculation?

Bud — Wed, 19 Apr 2023 13:37:23 GMT

ArcGIS Pro 3.1.1

Mobile geodatabase,
File geodatabase,
Or enterprise geodatabase that is registered as versioned; option to move edits to base (SQL field calculations not supported on versioned data):

For cases where we would rather use SQL than Python or Arcade, is there a way to generate a value using a SQL expression -- that would be returned back to the Field Calculator geoprocessing tool?

For example, if I already have a complex SQL CASE expression written from a different use case, I don't want to re-write it in Python or Arcade. I'd rather just use the SQL as-is instead, to save time and avoid mistakes.

I'm aware that it's possible to create a SQL database view, join it to the FC attribute table, and do a field calculation to bring in the joined field value. But that's pretty cumbersome for a one-off calculation. So I'm wondering if there's a way to use the SQL expression directly in a Python or Arcade field calculation instead.

Related ideas:

Re: Use a SQL expression inside a Python or Arcade field calculation?

DannyMcVey — Wed, 19 Apr 2023 13:33:39 GMT

Interestingly, SQL is an option when using the field calculator on a hosted feature service.

For file geodatabase feature classes, I'd recommend taking a look at the "where_clause" and "sql_clause" parameters on the UpdateCursor. This might enable your desired workflow.

Re: Use a SQL expression inside a Python or Arcade field calculation?

BlakeTerhune — Wed, 19 Apr 2023 17:55:32 GMT

You could use ArcSDESQLExecute to query your data, format into a dictionary, then use an update cursor or calculate field to update the values by looking them up in your dictionary.