topic Re: Making more Complex SQL Queries with Arcpy SearchCursor in Python Questions

Making more Complex SQL Queries with Arcpy SearchCursor

LaurenWallace — Sat, 21 Mar 2020 23:03:34 GMT

Hello everyone,

I am writing a python script and want to perform the following query using arcpy's SearchCursor:

select unique name, corridor, pnt, zone where not (name is null or corridor is null or pnt is null or zone is null)

I did try making the SearchCursor's where_clause = (name is null or corridor is null or pnt is null or zone is null), but the script could not run like that and gave an error. I am using the following code to query the arcgis data successfully but not getting the data the way I need it:

with arcpy.da.SearchCursor(gdbConnectTable, fieldsList) as readCursor:
for record in readCursor:

I have not been able to find any documentation on getting SearchCursor to yield unique results.

Is it possible to have the arcpy SearchCursor return what I need?

Re: Making more Complex SQL Queries with Arcpy SearchCursor

DanPatterson_Retired — Sat, 21 Mar 2020 23:55:01 GMT

text case is important

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation

Re: Making more Complex SQL Queries with Arcpy SearchCursor

DavidPike — Sun, 22 Mar 2020 14:17:42 GMT

A useful workflow for me is to write the expression first in the 'Select by attribute' window and see if it returns the expected results, then enclose the expression in quotations.

for example a simple selection

"Id" = 0

becomes

'"id" = 0'

Re: Making more Complex SQL Queries with Arcpy SearchCursor

JoshuaBixby — Sun, 22 Mar 2020 17:22:03 GMT

In addition to the SQL reference that Dan links to, make sure to read SearchCursor—Data Access module | Documentation.

In order to use SQL SELECT criteria, like DISTINCT, with ArcPy DA cursors, you will need to use a sql_clause argument to define what Esri calls a "SQL prefix." In general, and particularly with file geodatabases, SQL SELECT criteria do not work with spatial data types. For example you cannot write something like, SELECT DISTINCT shape. If you want to compare spatial data, you will need to create another column(s) and put a spatial property in it, e.g., create a WKT field.

Assuming your four fields are text (pnt being some text-based property or representation of your point data), your where clause would be:

where_clause = "NOT(name IS NULL OR corridor IS NULL OR pnt IS NULL OR zone IS NULL)"‍

and your SQL clause would be:

sql_clause = ("DISTINCT", None)‍

and your cursor definition would look like:

arcpy.da.SearchCursor(gdbConnectTable, ["name", "corridor", "pnt", "zone"], where_clause=where_clause, sql_clause=sql_clause)‍