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\\xyz\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xyz.sde" fc = "xyz" 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\xyz\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 ( )

OK this is where I am and think I have what I need , albeit very messy...maybe someone has a more streamlined way to accomplish this. Background: * I have a FC that has ALL boat Ramps in our state* It will have records/boat rampsin the state * I have a FC of inspections of these boat ramps* this FC will not have all the boat ramps in it as not all have been inspected * this FC will have duplicates boat ramp entries as its an inspection FC over time * I am writing ALL the Boat Ramps to a list * I am writing ALL the Inspections in the last 30 days to a list * I then need to find the Unmatched between the Boat Ramps and the Unique Inspections * This will give me a result of the ALL Boat Ramps that were not Inspected in the last 30 days. where the MATCHED will be the Boat Ramps that were inspected in the last 30 days where the UNMATCHED are the Boat Ramps that were NOT inspected in the last 30 days. I think this works in it current form but am sure this can be done more efficiently!!!!! Anyone have any input or thoughts on how to streamline this? ALL Boat Ramps: I create a list of ALL the Boat Ramps from the FC # Get list of all Boat Ramps myLayer = "BoatRampsWGS" myField = "SITENAME" myBoatRampList = [ row [ 0 ] for row in arcpy . da . SearchCursor ( myLayer , myField ) ] Inspections: Query Inspection FC to get all the inspections in the last 30 days # Loop through List of Boat Ramps and return those that dont have inspection within 30 days Inspected = [ ] dateQry = """CreationDate > '{}'""" . format ( month_ago ) 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])) CreatevalDate = ( str ( row [ 0 ] ) ) Inspected . append ( CreatevalDate ) del cursor # List of boat ramps inspected in last 30 days print Inspected Convert to SET and get unmatched and Matched Boat Ramps #Convert ALL Boat Ramps to a set to get the unique values mysetRampList = set ( myBoatRampList ) #Convert Inspections from last 30 days to a set to get the unique values mysetInspected = set ( Inspected ) # Get the Matched and Unmatched values using a SET matched = mysetRampList . intersection ( mysetInspected ) unmatched = mysetRampList . symmetric_difference ( mysetInspected ) # MATCHED # convert back to list sort and print mynewMatchedlist = list ( matched ) mynewMatchedlist . sort ( ) ; print mynewMatchedlist # UNMATCHED # convert back to list sort and print mynewUnmatchedlist = list ( unmatched ) mynewUnmatchedlist . sort ( ) ; print mynewUnmatchedlist

Jay, I'm just getting more and more confused and I don't think I completely understand the original question. E-mail me if necessary mitchh300@gmail.com

Line 18 is commented out right now. I don't know why I am having a hard time with this. I don't see where in the query it compares the last 30 day Boat Ramp Inspections against ALL the available Boat Ramps to determine which ones were not visited.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

jaykapalczynski

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.

<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="keyword token">import</SPAN> os<SPAN class="punctuation token">,</SPAN> string
<SPAN class="keyword token">import</SPAN> smtplib<SPAN class="punctuation token">,</SPAN> shutil
<SPAN class="keyword token">import</SPAN> email<SPAN class="punctuation token">,</SPAN> time
<SPAN class="keyword token">import</SPAN> sys<SPAN class="punctuation token">,</SPAN> csv
<SPAN class="keyword token">from</SPAN> email<SPAN class="punctuation token">.</SPAN>MIMEMultipart <SPAN class="keyword token">import</SPAN> MIMEMultipart
<SPAN class="keyword token">from</SPAN> email<SPAN class="punctuation token">.</SPAN>mime<SPAN class="punctuation token">.</SPAN>text <SPAN class="keyword token">import</SPAN> MIMEText


<SPAN class="comment token">#Variables==============================================</SPAN>

arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\Users\\xyz\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xyz.sde"</SPAN>
fc <SPAN class="operator token">=</SPAN> <SPAN class="string token">"xyz"</SPAN>

var_additionalcomments <SPAN class="operator token">=</SPAN> <SPAN class="string token">'additionalcomments'</SPAN>
var_CreationDate<SPAN class="punctuation token">,</SPAN>var_Creator <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CreationDate'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creator'</SPAN>

headers <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'comments'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creation Date'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creator'</SPAN><SPAN class="punctuation token">]</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
QueryFields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>var_additionalcomments<SPAN class="punctuation token">,</SPAN>var_CreationDate<SPAN class="punctuation token">,</SPAN>var_Creator


<SPAN class="comment token"># Get Date 30 days ago</SPAN>
<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT
today <SPAN class="operator token">=</SPAN> DT<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
month_ago <SPAN class="operator token">=</SPAN> today <SPAN class="operator token">-</SPAN> DT<SPAN class="punctuation token">.</SPAN>timedelta<SPAN class="punctuation token">(</SPAN>days<SPAN class="operator token">=</SPAN><SPAN class="number token">30</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> month_ago

<SPAN class="comment token"># Get list of all Boat Ramps</SPAN>
myLayer <SPAN class="operator token">=</SPAN> <SPAN class="string token">"BoatRampsWGS"</SPAN>
myField <SPAN class="operator token">=</SPAN> <SPAN class="string token">"SITENAME"</SPAN>
myBoatRampList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myLayer<SPAN class="punctuation token">,</SPAN> myField<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">print</SPAN> myBoatRampList

<SPAN class="comment token"># Loop through List of Boat Ramps and return those that dont have inspection within 30 days</SPAN>

<SPAN class="comment token"># I AM LOST HERE .... THIS IS WHERE THE LOOP AND QUERY SHOULD BE BUT DONT KNOW WHERE TO STATR</SPAN>




<SPAN class="comment token"># Write the results to CSV file.</SPAN>
outFileRegionDate <SPAN class="operator token">=</SPAN> open<SPAN class="punctuation token">(</SPAN>r<SPAN class="string token">"E:\ArcGISProjects\xyz\PythonScripts\PythonSync\PythonScripts\z_outFileRegionDate.csv"</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">"w"</SPAN><SPAN class="punctuation token">)</SPAN>&nbsp; 

mylist<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> QueryFields<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN>expressionDate<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursorDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; outFileRegionDate<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN><SPAN class="string token">','</SPAN><SPAN class="punctuation token">.</SPAN>join<SPAN class="punctuation token">(</SPAN>headers<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">'\n'</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; flagDate <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">False</SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursorDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; flagDate <SPAN class="operator token">=</SPAN> <SPAN class="token boolean">True</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; zvalDate <SPAN class="operator token">=</SPAN> str<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{0},{1},{2}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; outFileRegionDate<SPAN class="punctuation token">.</SPAN>write<SPAN class="punctuation token">(</SPAN>zvalDate <SPAN class="operator token">+</SPAN> <SPAN class="string token">"%s\n"</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; mylist<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>zvalDate<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">if</SPAN> <SPAN class="operator token">not</SPAN> flagDate<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN> <SPAN class="string token">"No rows found"</SPAN>
outFileRegionDate<SPAN class="punctuation token">.</SPAN>close<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

Find more posts tagged with

Accepted answers

All comments

jaykapalczynski

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<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> <SPAN class="string token">"C:\\Users\\xyz\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xyz@xyz.sde"</SPAN>
fc <SPAN class="operator token">=</SPAN> <SPAN class="string token">"xyz"</SPAN>

var_additionalcomments <SPAN class="operator token">=</SPAN> <SPAN class="string token">'additionalcomments'</SPAN>
var_CreationDate <SPAN class="operator token">=</SPAN> <SPAN class="string token">'CreationDate'</SPAN>
var_Creator <SPAN class="operator token">=</SPAN> <SPAN class="string token">'Creator'</SPAN>

headers <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'comments'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creation Date'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'Creator'</SPAN><SPAN class="punctuation token">]</SPAN>

QueryFields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>var_additionalcomments<SPAN class="punctuation token">,</SPAN>var_CreationDate<SPAN class="punctuation token">,</SPAN>var_Creator<SPAN class="punctuation token">]</SPAN>

<SPAN class="comment token"># Get Date 30 days ago</SPAN>
<SPAN class="keyword token">import</SPAN> datetime <SPAN class="keyword token">as</SPAN> DT
today <SPAN class="operator token">=</SPAN> DT<SPAN class="punctuation token">.</SPAN>date<SPAN class="punctuation token">.</SPAN>today<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
month_ago <SPAN class="operator token">=</SPAN> today <SPAN class="operator token">-</SPAN> DT<SPAN class="punctuation token">.</SPAN>timedelta<SPAN class="punctuation token">(</SPAN>days<SPAN class="operator token">=</SPAN><SPAN class="number token">30</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> month_ago

<SPAN class="comment token"># Get list of all Boat Ramps</SPAN>
myLayer <SPAN class="operator token">=</SPAN> <SPAN class="string token">"BoatRampsWGS"</SPAN>
myField <SPAN class="operator token">=</SPAN> <SPAN class="string token">"SITENAME"</SPAN>
myBoatRampList <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN> <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myLayer<SPAN class="punctuation token">,</SPAN> myField<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">print</SPAN> myBoatRampList

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

<SPAN class="comment token">#with arcpy.da.SearchCursor(myLayer, ['CreationDate', 'additionalcomments'], dateQry) as cursor:</SPAN>
<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> myField1<SPAN class="punctuation token">,</SPAN> dateQry<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CreatevalDate <SPAN class="operator token">=</SPAN> str<SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{0},{1},{2}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; notInspected<SPAN class="punctuation token">.</SPAN>append<SPAN class="punctuation token">(</SPAN>CreatevalDate<SPAN class="punctuation token">)</SPAN>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

MitchHolley1

Jay,

My apologies, try this:

dateQry<SPAN class="operator token">=</SPAN><SPAN class="string token">"""CreationDate < date{}"""</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>month_ago<SPAN class="punctuation token">)</SPAN>‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN></SPAN>

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

jaykapalczynski

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 <SPAN class="operator token">=</SPAN> <SPAN class="string token">"BoatRampsWGS"</SPAN>
myField <SPAN class="operator token">=</SPAN> <SPAN class="string token">"SITENAME"</SPAN>

<SPAN class="comment token"># Loop through List of Boat Ramps and return those that dont have inspection within 30 days</SPAN>
notInspected <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

dateQry<SPAN class="operator token">=</SPAN><SPAN class="string token">"""CreationDate > date%s AND CreationDate < date%s"""</SPAN><SPAN class="operator token">%</SPAN>month_ago <SPAN class="operator token">%</SPAN>today

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myLayer<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'CreationDate'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'additionalcomments'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> dateQry<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="comment token">#print(row)</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="keyword token">print</SPAN><SPAN class="punctuation token">(</SPAN><SPAN class="string token">'{} is in default of {}'</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">0</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> row<SPAN class="punctuation token">[</SPAN><SPAN class="number token">1</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

and get this error:

Traceback <SPAN class="punctuation token">(</SPAN>most recent call last<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
File <SPAN class="string token">"E:\ArcGISProjects\xyz\PythonScripts\PythonSync\PythonScripts\xyz.py"</SPAN><SPAN class="punctuation token">,</SPAN> line <SPAN class="number token">76</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="keyword token">in</SPAN> <SPAN class="operator token"><</SPAN>module<SPAN class="operator token">></SPAN>
dateQry<SPAN class="operator token">=</SPAN><SPAN class="string token">"""CreationDate > date%s AND CreationDate < date%s"""</SPAN><SPAN class="operator token">%</SPAN>month_ago <SPAN class="operator token">%</SPAN>today
TypeError<SPAN class="punctuation token">:</SPAN> <SPAN class="operator token">not</SPAN> enough arguments <SPAN class="keyword token">for</SPAN> format string‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

jaykapalczynski

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

dateQry<SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.5); border: 0px; font-weight: inherit;">=</SPAN><SPAN class="" style="color: #669900; border: 0px; font-weight: inherit;">"""InspectionDate > date%s AND InspectionDate < date%s"""</SPAN><SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.5); border: 0px; font-weight: inherit;">%</SPAN>month_ago <SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.5); border: 0px; font-weight: inherit;">%</SPAN>today

MitchHolley1

Jay,

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

Example:

notInspected <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="punctuation token">]</SPAN>

dateQry<SPAN class="operator token">=</SPAN><SPAN class="string token">"""InspectionDate > date%s AND InspectionDate < date%s"""</SPAN><SPAN class="operator token">%</SPAN>month_ago <SPAN class="operator token">%</SPAN>today

<SPAN class="keyword token">with</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>da<SPAN class="punctuation token">.</SPAN>SearchCursor<SPAN class="punctuation token">(</SPAN>myLayer<SPAN class="punctuation token">,</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'myField'</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> dateQry<SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>do something<SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN><SPAN class="punctuation token">.</SPAN>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

jaykapalczynski

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

QueryFields <SPAN class="" style="color: #a67f59; background: rgba(255, 255, 255, 0.498039); border: 0px; font-weight: inherit;">=</SPAN> <SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">[</SPAN>var_additionalcomments<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">,</SPAN>var_CreationDate<SPAN class="" style="color: #999999; border: 0px; font-weight: inherit;">,</SPAN>var_Creator

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.