Comparing a list from an arcpy search cursor to a list from a CSV file

Question

I'm attempting to compare entries in a CSV file to entries in a geodatabase using a search cursor through arcpy. I'm not sure if this is the best way to go about a comparison against these two datasets but I do not want to have to write to a new CSV. I have attached an extract from the code I am writing and am getting stuck comparing my two lists to each other. Is there a better way to compare these two datasets?

The data is in rows and I want to find entries where 'ID' is common in both list 1 and list 2 when the 'Status' field in list 2 is 'Complete'. When the common element is not found in both lists I want to print the 'ID' field from list 1.

Haven't made it up to that step yet but am confident to work that section out.

Can't seem to compare a list from an arcpy search cursor to a list from a csvreader and am getting stuck here. Have tried every combination of indenting, calling upon the lists specifically, using the list indexes and other methods in IDLE but have removed these for clarity. Haven't used Python for a number of years but relearning everything. Data snapshots added.

Code below:

import arcpy
arcpy.env.workspace = r'Database Connections\PROD edit Water.sde\Water.WATER.Network'
Water = 'Database Connections\PROD edit Water.sde\Water.WATER.Network\Water.WATER.Main'
B = ''
expression =  "\"ID\" != '" + B + "'"
f = 'ID'
Gday = 'DAY'
Gmonth = 'MONTH'
Gyear = 'YEAR'
Gpage = 'PAGE'
y = []
with arcpy.da.SearchCursor(Water, [f, Gday, Gyear], where_clause = expression ) as cursor:
for row in cursor:
    IDs = row[0]
    DAYs = row[1]
    YEARs = row[2]
    y.append([IDs,DAYs,YEARs])
    for num in [IDs]:
        print DAYs

    with open (r'CSV_of_Job_List.csv', 'r') as f:
        csvReader = csv.reader(f, delimiter=',', quotechar='"')
        header = csvReader.next()
        JobDateIndex = header.index("Entered")
        StatusIndex = header.index("status")
        CaptureDateIndex = header.index("Captured")
        EditIDIndex = header.index("ID")
        dates2 =[]
        for row in csvReader:
            JobDate = row[JobDateIndex]
            Status = row[StatusIndex]
            capturedate = row[CaptureDateIndex]
            JobID = row[EditIDIndex]
            for x in [Status]:
                if x == 'Completed':
                    Status = Status

                else:
                    Status = 0
                    dates2.append([JobID,Status,JobDate,capturedate])
            for num in [JobID]:
                print JobDate

Answer 1

Not sure what you want to do but try creating a Dictionary from the csv and then use this to compare each row from the feature class:

import arcpy, csv

csvdict = {}
with open(r"C:\Test\joblist.csv") as csvfile:
    r = csv.reader(csvfile)
    header = r.__next__() #or .next()
    for row in r:
        csvdict[row[0]]=row[1:]

with arcpy.da.SearchCursor(r'C:\database.gdb\featureclass', ['ID','DAY','YEAR']) as cursor:
    for ID, day, year in cursor:
        if ID in csvdict:
            if csvdict[ID][1] == 'Completed':
                #do something
        else:
            #do something else

The Dictionary has ID as key and a list of all other attributes as value:

{'21879': ['31/07/2018', 'Completed', '30/08/2018'], '21878': ['31/07/2018', 'Completed', '30/08/2018'],...}