I have four markets 1000-4000, but I only want to do markets 2000 and 3000. I created a markets list only containing those two markets so that I could use the list in a "for" loop. However, when I try and run it, it still runs through all the markets. I think this has to do with my "whereclause" or "sqlclause" or both, but I can't figure out how to fix it. I've tried using a wildcard in the "whereclause" but that doesn't work. I've also tried getting rid of the "whereclause", as well as an "AND" but that also doesn't work. What is wrong?


# Import system modules
import arcpy
import datetime 
from datetime import timedelta
import time
import calendar

# Set environment settings
arcpy.env.workspace = r"C:\arcGIS_Shared\Python\CenterHeatMaps2.gdb"

#Declare variables
fc = 'Open_GoHealth_Centers'
fields = ['USER_market_id','USER_GoHealth_ID','USER_GoHealth_Center_Name','USER_Opening_Date']
fieldname = 'USER_market_id'

#Define WHERE clause statement
whereclause = """{} = 2000 AND 3000 """.format(arcpy.AddFieldDelimiters(fc, fieldname))

sqlclause = (None, 'Order By USER_market_id, USER_GoHealth_ID')

# Set Markets to loop through
markets = [2000,3000]
# Set Years to loop through
years = [2016,2017]
# Set Months to loop through
months = [1, 2]

#returns last day of each month
def last_day_of_month(any_day):
    next_month = any_day.replace(day=28) + datetime.timedelta(days=4)
    return next_month - datetime.timedelta(days=next_month.day)

for market in markets:
    print (market)
    for year in years:
        print (year)
        for month in months:
            print (month)
            with arcpy.da.SearchCursor(in_table = fc, field_names = fields, where_clause=whereclause, sql_clause=(None, 'ORDER BY USER_market_id, USER_GoHealth_ID')) as cursor:
                #Loop through each row established in cursor
                for row in (cursor):
                    # Set local variables for the FeatureClasstoFeatureClass tool
                    inFeatures = "PatientVisitsGeocoded"
                    outLocation = r"C:\arcGIS_Shared\Python\CenterHeatMaps2.gdb"
                    outFeatureClass = "PatientVisits{0}_{1}_{3}_{2}".format(row[0], row[2], month, year)
                    delimitedfield = arcpy.AddFieldDelimiters(arcpy.env.workspace,"USER_CenterID")
                    expression = """{0} = {1} AND USER_DOSMonth = '{2}' AND USER_DOSYear = {3}""".format(delimitedfield, row[1], calendar.month_abbr[month], year)

    #               Execute FeatureClassToFeatureClass tool
    #               This If statement determines if the Opening_Date of the respective center is before the last day of the month being run.
    #               If it is, the feature class is copied, if not, the feature class is skipped.
                    if last_day_of_month(datetime.date(year, month, 1)) >= row[3].date():
                        arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)
  • 3
    Your whereclause won't work as written. Try whereclause = """{} IN (2000, 3000) """.format(arcpy.AddFieldDelimiters(fc, fieldname)) – Bjorn Feb 1 '18 at 20:30
  • I will write up a more complete response as an answer for you to acknowledge – Bjorn Feb 2 '18 at 13:13

whereclause won't work as written. When writing a compound SQL statement, all of the parts must be valid SQL statements by themselves. Also, your fieldname can't equal 2 distinct values at the same time. To match either of the values, use the OR operator.

Two possible solutions are:

whereclause = """{0} = 2000 OR {0} = 3000 """.format(arcpy.AddFieldDelimiters(fc, fieldname))

Your Answer

