topic Re: Python or Model Builder in Python Questions

Python or Model Builder

RickWarner — Thu, 13 Nov 2014 15:51:24 GMT

I have developed a model that the first step is to select records for a table with field name Sales, I manually now change sql expression from Sale 1 to Sale 2 etc, there are about 50 groups of sales. Once the group is selected, it then is joined with a polygon feature Parcels, records copied, join removed and then onto the next group of sales (There are upto 50 sales for each parcel, so a simple join doesn't work,)

I would like to automate the sql expression that initially selects the records, so that once the first group of sales is copied, join removed, the table then selects the next group of sales. Also I need a way to change the name of the copied features to match the sales group, so it doesn't overwrite.

The model works fine now, except that I must manually change the sql expression after each sale group and change the copied features name so it doesn't overwrite the previous sales group.

How can I automate the sql expression, if that is the appropriate approach?

Thank you.

Re: Python or Model Builder

JakeSkinner — Thu, 13 Nov 2014 17:03:03 GMT

Hi Rick,

Here is an example of one way to do this using Python. You can iterate through the Sales table and append the sales to a list. You can then remove duplicates from list, and iterate through the list. Ex:

import arcpy
from arcpy import env
env.workspace = r"C:\temp\python\test.gdb"
env.overwriteOutput = 1

table = "Sales"

#Create empty list
list = []

with arcpy.da.SearchCursor(table, ["Sales"]) as cursor:
    for row in cursor:
        #Append sales to list
        list.append(row[0])

del row, cursor

#Remove duplicates from list
list = dict.fromkeys(list)
list = list.keys()

#Iterate through list
for sale in list:
    arcpy.MakeFeatureLayer_management(table, "tableView", "Sales = '" + sale + "'")
    ...
    ...

Re: Python or Model Builder

RickWarner — Thu, 13 Nov 2014 18:34:47 GMT

Thank you, does this then allow me to group by sales grouping, ie Sales 1, say 100 records, then join them to a parcel map, copy features, then go on to the next Sales Group 2, say 200 records and repeat join copy records?

Re: Python or Model Builder

RickWarner — Thu, 13 Nov 2014 22:09:38 GMT

Thank you for your help,

I'm getting this error, what do you Think I'm doing wrong??

Runtime error Traceback (most recent call last): File "<string>", line 24, in <module> File "c:\program files\arcgis\desktop10.1\arcpy\arcpy\management.py", line 5748, in MakeFeatureLayer raise e ExecuteError: ERROR 000840: The value is not a Feature Layer.

Re: Python or Model Builder

JoshuaBixby — Fri, 14 Nov 2014 02:16:39 GMT

What is the backend storage format? File geodatabase? ArcSDE geodatabase? I ask because there may be other approaches that work more efficiently if the data is all stored in a DBMS.

Re: Python or Model Builder

JoshuaBixby — Sat, 11 Dec 2021 17:50:33 GMT

Although I think the approach is sound, it is bad practice to use a built-in function name as a variable identifier, i.e., naming a list 'list'. The code as written will work, but shadowing a built-in name could cause confusion further down in the script.

Another option for finding unique values is to use the set data type, which inherently doesn't allow duplicates.


#Create empty set  
salesSet = set()  





with arcpy.da.SearchCursor(table, ["Sales"]) as cursor:  
    for sale, in cursor:  
        #Add sales to set  
        salesSet.add(sale)





del sale, cursor





#Iterate through set  
for sale in salesSet:  
    arcpy.MakeFeatureLayer_management(table, "tableView", "Sales = '" + sale + "'")  
    ...  
    ...

A slightly different tack is to let the database engine identify unique values either through a SQL prefix


with arcpy.da.SearchCursor(table, ["Sales"], sql_clause=('DISTINCT', None)) as cursor:

or SQL postfix.


with arcpy.da.SearchCursor(table, ["Sales"], sql_clause=(None, 'GROUP BY Sales')) as cursor:

Performance wise, I am not sure where each approach falls out, but I can't imagine the differences being noticeable. My personal preference is using DISTINCT in a SQL prefix, but I know that ArcGIS 10.1 had issues with SQL clauses in cursors, so using a set data type might work with more versions.

Re: Python or Model Builder

JoshuaBixby — Fri, 14 Nov 2014 04:07:34 GMT

A cursory performance check shows SQL postfix being the quickest against a file geodatabase and SQL prefix being the slowest. Runtimes relative to SQL postfix: SQL postfix (+0%), List (+3%), Set (+4%), SQL prefix (+14%). Interestingly enough, having the data in a personal geodatabase nearly doubled the runtimes of every approach across the board.

Re: Python or Model Builder

RickWarner — Mon, 17 Nov 2014 16:54:36 GMT

I'm now getting a "Parsing error SyntaxError: invalid syntax (line 1)", which is the import arcpy statement. what could I now be doing wrong? I'm typing import arcpy??

Re: Python or Model Builder

JoshuaBixby — Mon, 17 Nov 2014 18:08:46 GMT

Can you include more of the error message?

Re: Python or Model Builder

RickWarner — Mon, 17 Nov 2014 18:21:24 GMT

Thanks Josh, I appreciate your help, I got that fixed, but now I'm hung up on table view, the way it is running now, each tableview is being overwritten by the next tableview, there are 20 or so groups of sales. I would like to join each tableview to a parcel map, copy those feature to a new file, then go on to the next sale group/table view. How would you suggest. MakeFature_management didn't work because I'm starting with a table.

Re: Python or Model Builder

JoshuaBixby — Mon, 17 Nov 2014 20:54:00 GMT

It is easier to look at code and provide feedback than statements describing the code. What code are you using to build the table views?

Re: Python or Model Builder

XanderBakker — Mon, 17 Nov 2014 22:45:04 GMT

small suggestion...

replace:

#Create empty set   
salesSet = set()   

with arcpy.da.SearchCursor(table, ["Sales"]) as cursor:   
    for sale, in cursor:   
        #Add sales to set   
        salesSet.add(sale) 

del sale, cursor

for this:

salesSet = set([r[0] for r in arcpy.da.SearchCursor(table, ["Sales"])])

Re: Python or Model Builder

JoshuaBixby — Mon, 17 Nov 2014 23:28:03 GMT

I agree a list comprehension can shave several lines off the code block, but we also lose the with statement. I don't know whether it is formally documented, but I have read and heard several Esri staffers say using with statements is a best practice when working with cursors.

Re: Python or Model Builder

XanderBakker — Tue, 18 Nov 2014 00:26:16 GMT

Yep, I have heard that, but then at least delete the line:

del sale, cursor

Since that is not necessary when using a with statement... At least that's what they say as well.

Re: Python or Model Builder

RichardFairhurst — Tue, 18 Nov 2014 00:30:04 GMT

I am using list comprehensions all over my code now and have experienced no problems. Until one crops up, I will continue to use them.

Re: Python or Model Builder

XanderBakker — Tue, 18 Nov 2014 00:33:40 GMT

I haven't had any negative experience with them either and truly believe that a day without list comprehensions is a day not lived (or coded actually)...

Re: Python or Model Builder

DanPatterson_Retired — Tue, 18 Nov 2014 00:36:30 GMT

Xander is that in Python Snippets?

Re: Python or Model Builder

XanderBakker — Tue, 18 Nov 2014 00:40:35 GMT

Hi Dan,

I have a separate post on that... Using Python List Comprehensions

May have to rethink the python snippets and move things over to the python snippets place..

Re: Python or Model Builder

JoshuaBixby — Tue, 18 Nov 2014 19:52:48 GMT

The documentation does give the impression that database locks will be released, but the documentation doesn't get into specifics of which locks. Currently, using a with statement for da-based search cursor releases the read lock on the layer but not the schema lock. My guess is that the schema lock can't be removed because the with statement resets the cursor iteration to streamline using the cursor again. Explicitly deleting the cursor object will remove the final schema lock. Is this poor documentation or a bug? I haven't submitted a Support case to get an answer, but I am leaning toward Esri saying the former.

Re: Python or Model Builder

JoshuaBixby — Tue, 18 Nov 2014 19:56:13 GMT

I think insert and update cursors were more the target for enabling with statements than search cursors, but people have a tendency to want to keep best practices simple. Instead of having one best practice for insert and update cursors and a different one for search cursors, we have ended up with one for all three.

I like list comprehensions, and I think they can work well with search cursors. I was just pointing out the trade-offs of the two approaches, not trying to evangelize one over the other.