Search Cursor (where clause), Insert Cursor - based on unique values

BrianDudek — Sat, 11 Dec 2021 15:12:36 GMT

Seeking some guidance related to search cursor's where clause. Currently, I am grabbing unique field values from a single column using the unique_values function on the field Name. I am attempting to export tables by unique Name values, passing the fields I want to it along with the rows associated with that unique name. So if there is three exported tables, there will be three unique Name values. I can get the unique Name tables exported (with my fields), but the problem is the insert cursor is adding the last Name value (alphabetically) and its rows into each of these tables. So I end up with the same rows in each table, but the right names of tables.

#not all the code, but the relevant stuff.
def unique_values(table, field):
    with arcpy.da.SearchCursor(table,[field]) as scursor:
        return sorted({row[0] for row in scursor})

uName = unique_values(workspace+outname, "Name")

for uNameSplit in uName:
     uNameSplit.split(",")
     query = "Name = '{0}'".format(uNameSplit)
     for uNames in uName:
         uNames = dbas.replace(" ","")
         uNames = dbas.replace(",","")
         uNames = dbas.replace(".","")

     with arcpy.da.SearchCursor(workspace+outname, fields, query) as scursor:
           arcpy.CreateTable_management(workspace, outname+"_Name_"+uNames)
           with arcpy.da.InsertCursor(outname+"_Name_"+uNames, fields) as icursor:
               for row in scursor:
                  icursor.insertRow(row)

I thought the for loop would work to pass one query at a time, as it does for the table filename variable, but it does not for the search and inset cursor portion.

I have tried passing in a list of values, but the where clause takes a string. May be missing something obvious. Thanks for any help!

Re: Search Cursor (where clause), Insert Cursor - based on unique values

BlakeTerhune — Sat, 11 Dec 2021 15:12:39 GMT

You can actually go one step further and use the DISTINCT SQL prefix so you don't have to query everything, just do do your own distinct with a set in that generator expression. See the code sample 5b and 6 for reference on using SQL prefix and postfix.

My approach would be something like this

import arcpy
import os

def main():
    source_table = os.path.join(workspace, outname)
    gis_fields_in = ["ADDR_NUM", "FULL_ST_NAME"]
    distinct_names = [
        row[0] for row in
        arcpy.da.SearchCursor(
            source_table,  ## in_table
            "Name",  ## field_names
                sql_clause=(
                'DISTINCT',  ## SQL prefix
                None  ## SQL postfix
            )
        )
    ]

    for name in distinct_names:
        out_table = os.path.join(workspace, "{}_Name_{}".format(outname, name))
        where_clause = "Name = '{}'".format(name)
        try:
            arcpy.MakeTableView_management(source_table, "tbl_view", where_clause)
            arcpy.CopyRows_management("tbl_view", out_table)
        finally:
            arcpy.Delete_management("tbl_view")



if __name__ == '__main__':
    main()

topic Re: Search Cursor (where clause), Insert Cursor - based on unique values in Python Questions

Search Cursor (where clause), Insert Cursor - based on unique values

Re: Search Cursor (where clause), Insert Cursor - based on unique values