0

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?

Code:

# 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
2

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 = """{} IN (2000, 3000) """.format(arcpy.AddFieldDelimiters(fc, fieldname))
whereclause = """{0} = 2000 OR {0} = 3000 """.format(arcpy.AddFieldDelimiters(fc, fieldname))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.