Loop through List find Records with no entries in last 30 days

718
13
05-04-2017 10:40 AM
jaykapalczynski
Frequent Contributor

I have  list that contains all the Boat Ramps I want to validate.

I want a query that loops through each boat ramp name and then determines which ones do not have a record in the last 30 days.  I need that result to be written to a CSV file.

I think I have the beginning and the end but not the part that loops and queries for Boat Ramps that do NOT have a record in the last 30 days

I need to query the FC variable (my dataset that has all the records)

"Ramp_1" is the field in the FC that has the Boat Ramp Name

Using the List for Boat Ramp names (List) 

Loop through the List and find which records in the FC do not have a record in the last 30 days.

import arcpy
import os, string
import smtplib, shutil
import email, time
import sys, csv
from email.MIMEMultipart import MIMEMultipart
from email.mime.text import MIMEText


#Variables==============================================

arcpy.env.workspace = "C:\\Users\\xxxx\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xxxx.sde"
fc = "xxxxx"

var_additionalcomments = 'additionalcomments'
var_CreationDate,var_Creator = 'CreationDate','Creator'

headers = ['comments','Creation Date','Creator']
        
QueryFields = [var_additionalcomments,var_CreationDate,var_Creator


# Get Date 30 days ago
import datetime as DT
today = DT.date.today()
month_ago = today - DT.timedelta(days=30)
print month_ago

# Get list of all Boat Ramps
myLayer = "BoatRampsWGS"
myField = "SITENAME"
myBoatRampList = [row[0] for row in arcpy.da.SearchCursor(myLayer, myField)]
print myBoatRampList

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days

# I AM LOST HERE .... THIS IS WHERE THE LOOP AND QUERY SHOULD BE BUT DONT KNOW WHERE TO STATR




# Write the results to CSV file.
outFileRegionDate = open(r"E:\ArcGISProjects\xxxx\PythonScripts\PythonSync\PythonScripts\z_outFileRegionDate.csv", "w") 

mylist=[]
with arcpy.da.SearchCursor(fc, QueryFields, where_clause=expressionDate) as cursorDate:
    outFileRegionDate.write(','.join(headers) + '\n')
    flagDate = False          
    for row in cursorDate:
        flagDate = True
        zvalDate = str('{0},{1},{2}'.format(row[0],row[1],row[2]))
        outFileRegionDate.write(zvalDate + "%s\n")
        mylist.append(zvalDate)
    if not flagDate:
        print "No rows found"
outFileRegionDate.close()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
13 Replies
jaykapalczynski
Frequent Contributor

I know I am missing the ] at the end of this line....copy and paste issue

QueryFields = [var_additionalcomments,var_CreationDate,var_Creator
0 Kudos
MitchHolley1
MVP Regular Contributor

Jay, 

You can add a SQL Expression inside a da.cursor loop. 

Example:

notInspected = []

dateQry="""InspectionDate > date%s AND InspectionDate < date%s"""%month_ago %today

with arcpy.da.SearchCursor(myLayer, ['myField'], dateQry) as cursor:
      ...do something...
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Depending on what type of database you're using and what field type the date is stored in, the SQL syntax will change.  See here: SQL reference | ArcGIS Desktop 

0 Kudos
jaykapalczynski
Frequent Contributor

Is this saying :  Greater than the month_ago variable and less than todays date?

dateQry="""InspectionDate > date%s AND InspectionDate < date%s"""%month_ago %today
0 Kudos
jaykapalczynski
Frequent Contributor

While writing the below I had a thought...where in that example is it taking the unique Boat Ramp Names from the Array and looping through each one to determine if that specific boat ramp has an inspection date in the last 30 days....  I then need a list of each boat ramp that does not have an inspection in the last 30 days....mind you each boat ramps has hundreds of existing inspections.  Im really confused.

It looks like that query is giving me the boat ramps that were inspected in the last 30 days...not the ones that have not been inspected...

I try this:

myLayer = "BoatRampsWGS"
myField = "SITENAME"

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days
notInspected = []

dateQry="""CreationDate > date%s AND CreationDate < date%s"""%month_ago %today

with arcpy.da.SearchCursor(myLayer, ['CreationDate', 'additionalcomments'], dateQry) as cursor:
    for row in cursor:
        #print(row)
        print('{} is in default of {}'.format(row[0], row[1]))
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

and get this error:

Traceback (most recent call last):
File "E:\ArcGISProjects\xxxx\PythonScripts\PythonSync\PythonScripts\xxx.py", line 76, in <module>
dateQry="""CreationDate > date%s AND CreationDate < date%s"""%month_ago %today
TypeError: not enough arguments for format string‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
MitchHolley1
MVP Regular Contributor

Jay, 

My apologies, try this:

dateQry="""CreationDate < date{}""".format(month_ago)‍‍‍

This will return values that are less than a month ago. 

0 Kudos
jaykapalczynski
Frequent Contributor

Yea I picked that up and made the change in my testing yesterday....thanks

So this is what I have right now...I get this error...thoughts?

I assume a date format issue?

Questions:

I dont see how this query is looping through each Boat Ramp Name, The fc variable the query is running against might not even have the Boat Ramp in it ( in fact I know it does not have all the unique boat ramps in it).  

Thats why I created the LIST called "myBoatRampList".  This contains all the unique Boat Ramp Names.

  • I assumed I would loop through the LIST (myBoatRampList) reading each unique Boat Ramp name.
  • On each pass through the loop verify if there has been a record created for that specific boat ramp in the last 30 days.
  • If not then write that to a second LIST (notInspected).  This will be the list of Boat Ramps that do not have inspection in the last 30 days.

Traceback (most recent call last):
File "E:\ArcGISProjects\BoatRampFacilities\PythonScripts\PythonSync\PythonScripts\~6_30days ago.py", line 44, in <module>
for row in cursor:
RuntimeError: Underlying DBMS error [[Microsoft][SQL Server Native Client 11.0][SQL Server]Operand type clash: datetime2 is incompatible with int] [DGIF_TEST.DBO.BRs_Boat_Ramp_Inspection_2]


arcpy.env.workspace = "C:\\Users\\xxx\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xx@xx.sde"
fc = "xx"

var_additionalcomments = 'additionalcomments'
var_CreationDate = 'CreationDate'
var_Creator = 'Creator'

headers = ['comments','Creation Date','Creator']

QueryFields = [var_additionalcomments,var_CreationDate,var_Creator]

# Get Date 30 days ago
import datetime as DT
today = DT.date.today()
month_ago = today - DT.timedelta(days=30)
print month_ago

# Get list of all Boat Ramps
myLayer = "BoatRampsWGS"
myField = "SITENAME"
myBoatRampList = [row[0] for row in arcpy.da.SearchCursor(myLayer, myField)]
print myBoatRampList

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days
notInspected = []
dateQry="""CreationDate > date{} AND CreationDate < date{}""".format(month_ago, today)
myField1 = "CreationDate"

#with arcpy.da.SearchCursor(myLayer, ['CreationDate', 'additionalcomments'], dateQry) as cursor:
with arcpy.da.SearchCursor(fc, myField1, dateQry) as cursor:
    for row in cursor:
        print(row)
        CreatevalDate = str('{0},{1},{2}'.format(row[0],row[1],row[2]))
        notInspected.append(CreatevalDate)

        ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos
MitchHolley1
MVP Regular Contributor

Try this on lines 30 - 35:

#with arcpy.da.SearchCursor(myLayer, ['CreationDate', 'additionalcomments'], dateQry) as cursor:
with arcpy.da.SearchCursor(fc, myField1, dateQry) as cursor:
    for row in cursor:
        print(row)
        CreatevalDate = (str(row[0]),str(row[1]),str(row[2]))
        notInspected.append(CreatevalDate)‍‍‍‍‍‍‍‍‍‍‍‍
del cursor

That will make each item in the list a tuple.  

If you want each item in the list to be a string, do that following (ugly way). 

#with arcpy.da.SearchCursor(myLayer, ['CreationDate', 'additionalcomments'], dateQry) as cursor:
with arcpy.da.SearchCursor(fc, myField1, dateQry) as cursor:
    for row in cursor:
        print(row)
        notInspected.append(str(row[0])+','+str(row[1])+','+str(row[2]))
del cursor

*Note: don't forget to delete the cursors

MitchHolley1
MVP Regular Contributor

ohhhhhh wait wait wait... 

You're calling 3 rows in the search cursor.. when you're only asking arcpy to search one row 'myField'.  So, that'd be row[0] only... no row[1] or row[2]. 

0 Kudos
jaykapalczynski
Frequent Contributor

OK...I am now getting a return....I added a count to the FOR loop and am getting 64 records.  

  • These are the 64 records that have been added in the last 30 days.
  • There are duplicate Boat Ramp names as some have been visited multiple times.  
  • So maybe 50 or so unique Boat Ramps were entered in the last 30 days.

What I need now is the opposite.  I need the Ramps that were NOT visited in the last 30 days.

  1. I have the ramps that were visited in the last 30 days (this query we just got working)
  2. And I have a list of ALL the ramps (LIST "myBoatRampList" in the code above)

Can I do this in one query? 


Or: Do I need anew query to give me a list of the Ramps that differ from the Query results LIST (number 1 above) and the ALL ramps LIST (number 2 above)

QueryFields2 = [var_ramp_1,var_CreationDate]

# Get Date 30 days ago
import datetime as DT
today = DT.date.today()
month_ago = today - DT.timedelta(days=30)
print month_ago

# Get list of all Boat Ramps
myLayer = "BoatRampsWGS"
myField = "SITENAME"
myBoatRampList = [row[0] for row in arcpy.da.SearchCursor(myLayer, myField)]
print myBoatRampList

# Loop through List of Boat Ramps and return those that dont have inspection within 30 days
Inspected = []

#dateQry="""CreationDate > date{} AND CreationDate < date{}""".format(month_ago, today)
dateQry="""CreationDate > '{}'""".format(month_ago)
print dateQry

count = 0

with arcpy.da.SearchCursor(fc, QueryFields2, dateQry) as cursor:
    for row in cursor:
        print(row)
        count = count + 1
        CreatevalDate = (str(row[0]),str(row[1]))
        Inspected.append(CreatevalDate)
del cursor

print count
print Inspected‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
0 Kudos