topic Re: arcpy.da.SearchCursor - sqlprefix in Python Questions

arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Mon, 08 Sep 2014 21:20:44 GMT

Does the sql prefix - DISTINCT work well on SDE tables.

http://resources.arcgis.com/en/help/main/10.1/index.html#//018w00000011000000

Syntax SearchCursor (in_table, field_names, {where_clause}, {spatial_reference}, {explode_to_points}, {sql_clause})

An SQL prefix clause is positioned in the first position and will be inserted between the SELECT keyword and the SELECT COLUMN LIST. The SQL prefix clause is most commonly used for clauses such as DISTINCT or ALL.

My following example is resulting in all rows, not distinct rows. rs = arcpy.da.SearchCursor(sTableName, (sSelectField1, sSelectField2), sSqlExp, None, None, ("DISTINCT", sSqlPostfix))

What elese could I do to get truely distinct records only.
I store the values in a list and are they any function to get unique values in a list. Example [1,2,2,3,3,3,5,6,7] to [1,2,3,5,6,7]. Also set(xyz) won't work because I'm using a list, whereas set(xyz) works on tuples.

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Fri, 10 Dec 2021 20:01:51 GMT

I doubt you can perform GROUP BY clause in any arcpy SearchCursor (I can't remember where I saw this, but I think this is the case). However, you can:

1. Depending upon the Database type, you can issue your SQL in the python code. You will need the appropriate library (cx_Oracle, pyodbc, pysmssql, etc).

2. These typically put results into a "cursor" (not an arcpy cursor). So you will then have to parse this into your own list/array.

3. Take the list and either use it as it or perform an additional conversion to NumPy.

4. Or you can utilize pandas library to convert from your list and perform just about any grouping you'd want.


import cx_Oracle


import numpy


import pandas

con = cx_Oracle.connect('theuser', 'thepass', 'your DB alias on your TNSNAMES.ORA file ')
cur = con.cursor()
if cur.execute('select fld1, fld2 from WHERE fld1 = 'blah' GROUP BY fld1, fld2'):
    print "finally, it works!!!"
else:
    print "facepalm"


 


datArray = []  


cxRows = cursor.fetchall()


for cxRow in cxRows:


   datArray.append(cxRow)


con.close()


 


#convert the array to a pandas DataFrame


dbDF = DataFrame(datArray, columns=['fld1', 'fld2'])


 


#to get unique values from any pandas datframe


uniquevals = numpy.unique(dbDF['fld1'])

Re: arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Tue, 09 Sep 2014 16:16:34 GMT

I get error for these, because I dont have those modules:

import cx_Oracle - Is there an equivalent for SQL Server, our SDE runs of it.
import pandas - Is it an open source freeware? Just to be sure.

Re: arcpy.da.SearchCursor - sqlprefix

XanderBakker — Tue, 09 Sep 2014 17:21:44 GMT

Some small example on creating a unique list of values in a field:

# create a unique list of the values in a field in a table or featureclass
import arcpy
FC_or_TBL = r"D:\Xander\Genesis\Tablas LayerRef\bk_DLLO_931.gdb\AG_LAYERREF"
fld_name1 = "COLUMNA"
unique_list = list(set(r[0] for r in arcpy.da.SearchCursor(FC_or_TBL, (fld_name1))))
unique_list.sort()
print "\n".join(unique_list)
print "\n#####\n"

On line 5 a list is created using list comprehensions, which is converted to a set (unique values) and parsed back to a list

# using a combination of 2 columns
import arcpy
FC_or_TBL = r"D:\Xander\Genesis\Tablas LayerRef\bk_DLLO_931.gdb\AG_LAYERREF"
fld_name1 = "COLUMNA"
fld_name2 = "ID_LAYERREF"
unique_list = list(set("{0},{1}".format(r[0], r[1]) for r in arcpy.da.SearchCursor(FC_or_TBL, (fld_name1, fld_name2))))
print "\n".join(unique_list)
print "\n#####\n"

On line 6 the list is created from the combination of 2 fields

# including a where clause
import arcpy
FC_or_TBL = r"D:\Xander\Genesis\Tablas LayerRef\bk_DLLO_931.gdb\AG_LAYERREF"
fld_name1 = "COLUMNA"
fld_name2 = "ID_LAYERREF"
fld_search = "COLUMNA"
search_value = "OBJECTID"
where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(FC_or_TBL, fld_search), search_value)
unique_list = list(set("{0}_{1}".format(r[0], r[1]) for r in arcpy.da.SearchCursor(FC_or_TBL, (fld_name1, fld_name2, fld_search), where)))
print "\n".join(unique_list)
print "\n#####\n"

In this case an additional where clause is used to limited the results. As James already showed, Pandas is a powerful library. If you have it, use it. If you don't and you don't want to install it, the above might be an alternative.

Kind regards,

Xander

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Tue, 09 Sep 2014 17:22:22 GMT

Looks like pyodbc will connect to SQL Server sql server - MSSQL in python 2.7 - Stack Overflow

Pandas home Python Data Analysis Library — pandas: Python Data Analysis Library

I am just a developer and don't have experience getting these installed, so you will have to figure that out.

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Tue, 09 Sep 2014 17:26:32 GMT

Thanks for the example Xander --- it's good to see straight-forward solutions. While it's frustrating to be unable to issue more complex SQL on arcpy/GIS side of things, I'd even prefer to completely remove the SQL out of the code I posted and move that to StoredProcedures/PL-SQL packages on the database instead.

As as an application and database devloper, it just makes sense to only return the rows needed rather than return everything and filter on the app side.

Re: arcpy.da.SearchCursor - sqlprefix

RobertBurke — Tue, 09 Sep 2014 17:33:51 GMT

Here is a simple sql clause that I use to get an ordered unique list of values from the Speed field in the SpeedLimits table. Distinct only returns unique values, Order by with ASC for ascending, sorts the rows. Note my Speed field is a test type and not numeric.

uniqueList = []

with arcpy.da.SearchCursor("SpeedLimits", ["Speed"], sql_clause=("DISTINCT", 'ORDER BY Speed ASC') ) as rows:

for row in rows:

uniqueList.append(row[0])

stringFilter.list = uniqueList

Re: arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Tue, 09 Sep 2014 17:44:27 GMT

Wowww. Does "DISTINCT" work for you? If so what version of ArcMap are you using?

The whole problem started since use sqlprefix didn't work with arcpy.da.SearchCursor. It won't throw an error but wouldn't give the correct distinct recrods. So what do you mean by "Speed field is a test type and not numeric". Does python have a data type "test". Just to rant, python could use better terminology for naming their data types - arrays(can they hold strings?), list, set, dictionary, tuples...... Ofcourse, I'm not an expert but its totally confusing.

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Tue, 09 Sep 2014 18:45:31 GMT

It works for Robert because he is only returning 1 field. Your OP has multiple fields -- how can you return distinct values for multiple fields without correctly apply a GROUP BY clause?

Re: arcpy.da.SearchCursor - sqlprefix

RobertBurke — Tue, 09 Sep 2014 19:28:04 GMT

Sorry Manny, Test should be Text.

I have a field to store speed limit values, 45, 55, 65 etc. I was just trying to point out, that my field called 'Speed' is defined as the Text data type, and not as a Numeric data type as some might expect.

And I also only have the one field. I haven't tried Two.

Thanks,

Rob

Re: arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Tue, 09 Sep 2014 19:45:56 GMT

@Xander Bakker

What does this do: set("{0}_{1}".format(r[0], r[1]) . Specifically, the curly brackets {} and the need for format(). Is format() mandatory?

Is it possible to breakdown the following into more "bite sized", multi-line code:

unique_list = list(set("{0}_{1}".format(r[0], r[1]) for r in arcpy.da.SearchCursor(FC_or_TBL, (fld_name1, fld_name2, fld_search), where)))

Also, what is the difference between set() and list() do. Can't you do an app

Re: arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Tue, 09 Sep 2014 19:47:19 GMT

Thanks James and Robert.

Re: arcpy.da.SearchCursor - sqlprefix

XanderBakker — Wed, 10 Sep 2014 02:17:42 GMT

Hi Manny,

My explanation was a little short, sorry for that... and creating these type of "one-liners" doesn't allow for easy interpretation.

Let's break it down into pieces:

# let's say we have a list with duplicate values
list1 = [1, 3, 5, 7, 3, 2, 5, 6, 8, 7, 5, 4, 3, 2, 6, 2]

# the set() is used to convert it to a set of unique values
set1 = set(list1)
print set1
# returns: set([1, 2, 3, 4, 5, 6, 7, 8])

# if you want to convert it to a list again (to loop over it)
list2 = list(set1)
print list2
# returns [1, 2, 3, 4, 5, 6, 7, 8]

A set is very powerful to get a unique list of values and combining sets allows you to obtain values that are in one list but not in the other and info like this. More on this in my post: Some Python Snippets (scroll down to comparing lists)

To create the list some list comprehensions are used (check out this document I posted: Using Python List Comprehensions ).

Let's break down the code:

unique_list = list(set("{0}_{1}".format(r[0], r[1]) for r in arcpy.da.SearchCursor(FC_or_TBL, (fld_name1, fld_name2, fld_search), where)))

The set() and list() at the beginning of the line have been explained above.

Within the brackets of the set() command a lot is going on. Let's not start from left to right, but with the SearchCursor. It takes a table or featureclass, a tuple (or list) of field names and in this case a where clause. This is pretty standard and I don't think needs much explanation. Normally when you use a search cursor you obtain a cursor object which you use to loop through each row (or feature) in the cursor. Next you probably access the fields in each row and do something with it like this:

with arcpy.da.SearchCursor(FC_or_TBL, (fld_name1, fld_name2, fld_search), where) as curs:
    for row in curs:
        val_fld1 = row[0]
        # etc

In case of the list comprehension this is done on one line. It retrieves each row "r" in the SeachCursor and returns this:

"{0}_{1}".format(r[0], r[1])

string.format() allows you to create a string and pass in variables. It is a pretty need way to combine values and string in a certain format

r[0] refers to the value of fld_name1

r[1] refers to the value of fld_name2

{0} refers to the first parameter specified in the format command

{1} refers to the second parameter specified in the format command

... so it the value for fld_name1 is "abc" and the value in fld_name2 would be 1, this would result in the string "abc_1".

I used something similar for creating the where clause:

where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(FC_or_TBL, fld_search), search_value)

It starts with a string "{0} = '{1}'". The {0} is replaced with the first argument of the format function:

arcpy.AddFieldDelimiters(FC_or_TBL, fld_search)

This will put quotes or brackets or nothing around the field name based on the type of workspace (that can be useful if you don't know if your input is a shapefiles, personal, file or enterprise geodatabase...

The {1} is replace with the value in the variable search_value. Please note that this where clause will fail if your field is not of type TEXT (string). It is possible to extend this functionality and detect the columns type, but for simplicity reasons (read I was to lazy) I did not do that.

If you have any other doubt, please let me know...

Kind regards,

Xander

Re: arcpy.da.SearchCursor - sqlprefix

CarstenBøcker1 — Thu, 11 Sep 2014 09:45:49 GMT

I have used arcpy.ArcSDESQLExecute with great succes when it comes to complex queries involving DISTINCT and GROUP BY. It also seems to perform pretty well as an alternative to using a cursor and doing the grouping in the loop.

Carsten

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Thu, 11 Sep 2014 12:54:21 GMT

Can you post an example? I remember not being able to correctly apply a GROUP BY with multiple fields present in the SQL statement but it has been a while since then and I am not sure if I can even locate the thread in this new place.

Re: arcpy.da.SearchCursor - sqlprefix

curtvprice — Thu, 11 Sep 2014 18:31:07 GMT

As as an application and database devloper, it just makes sense to only return the rows needed rather than return everything and filter on the app side.

Isn't the main way this is supported with ArcSDE databases using the Make Query Table tool? I think that's the only way to access fully native SQL queries off the geodatabase.

arcpy.da.SearchCursor is a huge improvement. I've heard pandas is really really fast (especially for joins) so I'm hoping Esri is considering including it in their default python distro.

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Thu, 11 Sep 2014 18:47:52 GMT

I can't seem to locate the exact thread I participated in but it was about attempting to setup a Make Query Table with multiple fields and include a GROUP BY statement in the SQL. It wouldn't work from what I can remember, or it would execute but it doesn't do the grouping as expected.

Perhaps I am misunderstanding the QueryTable!

Re: arcpy.da.SearchCursor - sqlprefix

ManikMohandas — Thu, 11 Sep 2014 22:19:10 GMT

Xander Bakker‌. Thank you so very much. That was a good primer on data access, lists, set and string functions. Yes, I was able to work around the limitations of SQL prefix like DISTINCT not working on multiple fields through arcpy.da.SearchCursor. Wish ESRI would further improve "DA".

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Fri, 12 Sep 2014 13:01:58 GMT

So... are you confirming that what I mentioned is not possible with Make Query Table?

There's some confusion that this is possible with multiple fields returned. However, I just don't see how so because a correct SQL statement that returns DISTINCT values on multiple fields simply must have a GROUP BY clause in it.

Re: arcpy.da.SearchCursor - sqlprefix

JamesCrandall — Fri, 12 Sep 2014 13:04:36 GMT

Curtis,

I am using Pandas quite extensively and I can confirm it offers a lot of value for table/list operations! However, in the context of this thread, it just doesn't make sense because the idea of issuing SQL against the database is to return only those rose that meet the query. That is, I wouldn't want to return 1 million rows just to process them with Pandas functions.