Use Items in List as SQL Where Clause in "for Loop"; write identified rows to CSV

[RaenaBallantyne_DeMaris]

Greetings, I'm trying to create a custom geoprocessing tool. The background:

• I have a polygon dataset "FarmLocations" with a "LocCode" attribute field and an attribute field "Fruits" grown there. Examples of the text entries include "Apples, Cherries", "Apples", "Apples, Walnuts, Peaches", etc.:
• 
• I have a List of Fruits: ['Apples', 'Walnuts', 'Hazelnuts', 'Cherries', 'Apricots', 'Pecans', 'Pears', 'Peaches', 'Plums']. (This is a list of all of the unique single items that appear in the "Fruits" field of the FarmLocations polygons)
• I'm provided with a Project List as .xlsx that contains a code for location "LocCode" of projects that will be done at FarmLocations. A single farm location may appear more than once in the Project List:

• LocCode	ProjName
  A	Project 1
  B	Project 2
  C	Project 3
  E	Project 4
  G	Project 5
  A	Project 6
  D	Project 7
  C	Project 8
  C	Project 9
  A	Project 10

The desired output: A csv for each fruit in the List of Fruits containing the list of projects that will affect that fruit crop. 

My approach:

# imports and environments
import arcpy
from arcpy import da
import csv
arcpy.env.workspace = r'C:\Default.gdb'
workspace = arcpy.env.workspace
arcpy.env.overwriteOutput = True
Locations = workspace + '\FarmLocations'
Field = 'Fruits'
List = ['Apples', 'Walnuts', 'Hazelnuts', 'Cherries', 'Apricots', 'Pecans', 'Pears', 'Peaches', 'Plums']

# join the spatial data to the list of projects
# project table as excel workbook
ProjectTable = r'C:\ProjectList.xlsx\ProjectList$'
JoinField = 'LocCode'                
arcpy.management.JoinField(ProjectTable, JoinField, Locations, 'LocCode', ['Fruits'])
# export the joined list as a dbase table
arcpy.conversion.TableToDBASE(ProjectTable, workspace)
JoinedProjList = r'C:\Default.gdb\ProjectList_'

# Loop through the joined project list and identify those projects that contain values from the Fruit List

for item in List: 
    if row in JoinedProjList LIKE item:      
# query = "'Fruits' LIKE 'Apples'"
# arcpy.management.SelectLayerByAttribute(JoinedProjList, 'NEW_SELECTION', query)
        with open(item, "w", encoding='utf-8') as csvfile:
        csvwriter = csv.writer(csvfile, delimiter = ',', lineterminator = '\n')
        # write header
        fields = ['LocCode', 'ProjName']
        csvwriter.writerow(fields),
        # write selected data to rows
        with arcpy.da.SearchCursor(item, fields) as s_cursor:
            for row in s_cursor:
                csvwriter.writerow()
                
print("csvs written")

What I've tried: At first I tried using arcpy.management.SelectLayerByAttribute, but then I realized that's probably an extra step and I can just query the "Fruits" field in the joined table for the items in the List; when the search terms are present, I then write that row to a csv. However... I can't figure out the syntax of the SQL query in the for loop to loop over the items in the list as the search terms. I've tried it a bunch of ways, but the following block is where I'm at. (For now, I commented out the use of the SelectLayerByAttribute, but in my tests, outside of the For Loop, I had marginal success with the SelectLayerByAttribute using "'Fruits' LIKE '%Apple%'" as the SQL Where clause.) 

Where I'm stuck is right around here:

for item in List: 
    if row in JoineProjList LIKE item:
        
# query = "'Fruits' LIKE 'Apples'"
# arcpy.management.SelectLayerByAttribute(JoinedProjList, 'NEW_SELECTION', query)

So, how can I loop through the items in my List (['Apples', 'Walnuts', 'Hazelnuts', 'Cherries', 'Apricots', 'Pecans', 'Pears', 'Peaches', 'Plums']), searching the "Fruits" field of the joined table for each of the terms in the List, and writing the identified rows to respective csv files with using the "item in List" (e.g. the specific fruit) as the name of the CSV?

I also think I can simply the code by using f string literals, and perhaps I'm already including unnecessary steps (e.g., if I write code to return a feature set, eliminating the need to join the tables...). I'll get there, but first things first. Ideas and feedback welcome. 

-r

[JakeSkinner · Accepted solution]

Hi @RaenaBallantyne_DeMaris ,

Try the following:

import arcpy

# Variables
farmLocations = r"c:\projects\GeoNET\GeoNET.gdb\FarmLocations"
projects = r"c:\projects\GeoNET\GeoNET.gdb\Project"
csvDirectory = r"c:\projects\GeoNET"

# List of frutis
fruitList = ['Apples', 'Walnuts', 'Hazelnuts', 'Cherries', 'Apricots', 'Pecans', 'Pears', 'Peaches', 'Plums']

# Create Projects Dictionary
projectsDict = {}
with arcpy.da.SearchCursor(projects, ["LocCode", "ProjName"]) as cursor:
    for row in cursor:
        projectsDict.setdefault(row[0], [])
        projectsDict[row[0]].append(row[1])
del cursor

# Iterate through fruit list
for fruit in fruitList:
    with arcpy.da.SearchCursor(farmLocations, ["LocCode"], f"Fruits LIKE '%{fruit}%'") as cursor:
        for row in cursor:
            # Create CSV file for each fruit
            csvOutput = fr'{csvDirectory}\{fruit}.csv'
            output_file = open(csvOutput, 'w')
            header = 'LocCode, ProjName\n'
            output_file.write(header)
            for project in projectsDict[row[0]]:
                output = f'{row[0]}, {project}\n'
                output_file.write(output)
    del cursor

output_file.close()

[BlakeTerhune]

Unrelated question for you, @JakeSkinner: Do you recommend using del on a cursor even when it's used in a with statement?

[JakeSkinner]

@BlakeTerhune I believe the documentation states that it's not required when using it within a with statement, but I've found there can still be locks when I do not explicitly remove the cursor using del cursor.

[RaenaBallantyne_DeMaris]

Hi @JakeSkinner Thanks so much for simplifying my code. I have added code to import the excel sheet into the geodatabase, and it all goes smoothly until line 28 of your code, when it a KeyError exception is thrown. I'm guessing this is because not all projects in the list are associated with each fruit location and there are some that are missing. Would this be handled by an if else statement to skip over the dictionary keys that are not found during the loop?

[JakeSkinner · Accepted solution]

@RaenaBallantyne_DeMaris that may be the case.  You can add a try/except to bypass this error.  Ex:

# Iterate through fruit list
for fruit in fruitList:
    with arcpy.da.SearchCursor(farmLocations, ["LocCode"], f"Fruits LIKE '%{fruit}%'") as cursor:
        for row in cursor:
            # Create CSV file for each fruit
            csvOutput = fr'{csvDirectory}\{fruit}.csv'
            output_file = open(csvOutput, 'w')
            header = 'LocCode, ProjName\n'
            output_file.write(header)
            try:
                for project in projectsDict[row[0]]:
                    output = f'{row[0]}, {project}\n'
                    output_file.write(output)
            except KeyError:
                pass
    del cursor

[RaenaBallantyne_DeMaris]

@JakeSkinner Thanks so much, again! I have a long way to go in developing my abilities in Python, but this gives me a template to use this code in my real project, which is more complex and contains hundreds of locations, dozens of list items that are not fruit, and evolving lists of projects. I'm grateful that you took the time to show me this dictionary solution!

-r