topic Re: For Loop not looping correctly, ArcPy in Python Questions

For Loop not looping correctly, ArcPy

MattAzemar — Sun, 12 Dec 2021 08:02:54 GMT

I have four markets 1-4, but I only want to do markets 2 and 3. 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" but that also doesn't work. Could someone help me with this?

fc = 'Centers'
fields = ['market_id','Center_ID','Center_Name','Opening_Date']
fieldname = 'market_id'

#Define WHERE clause statement
#whereclause = """{} = '%000' """.format(arcpy.AddFieldDelimiters(fc, fieldname))

sqlclause = (None, 'Order By market_id, Center_ID')

# Set Markets to loop through
markets = [2,3]
# 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:
    sqlclause = (None, 'Order By market_id, Center_ID')
    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 market_id, Center_ID')) as cursor:‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: For Loop not looping correctly, ArcPy

XanderBakker — Sun, 12 Dec 2021 08:02:57 GMT

Although I'm not sure what you will do inside the SearchCursor, I assume that you want to query those records that correspond to the defined market_id, year and month and therefor you have the function to get the last day of the month (to define the date range to be included in the query).

The query on dates may depend on the data source you are using. What type of workspace is the data stored in?

I also think that using the calendar might be a better way of obtaining the last day (or moment) of a given month in a given year. This could look something like this:

from calendar import monthrange, datetime

def main():
    years = [2016, 2017]
    months = [1, 2]
    for year in years:
        for month in months:
            print year, month, last_day_of_month(year, month)

def last_day_of_month(year, month):
    last_day = monthrange(year, month)[1]
    return datetime.datetime(year, month, last_day, 23, 59, 59)

if __name__ == '__main__':
    main()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Inside your loop, you will have to include the market_id, and date range. Optionally you can use a sql clause to sort the result. No need to sort on market id, since each iteration will only include 1.

Re: For Loop not looping correctly, ArcPy

XanderBakker — Sun, 12 Dec 2021 08:02:59 GMT

Example of a query for a specified month if the data is stored in a File Geodatabase:

from calendar import monthrange, datetime

def main():
    print queryDefDateBetweenForGivenMonth(2017, 9, "Opening_Date")

def queryDefDateBetweenForGivenMonth(year, month, date_field):
    start_date = str(datetime.datetime(year, month, 1, 0, 0, 0))
    end_date = str(datetime.datetime(year, month, monthrange(year, month)[1], 23, 59, 59))
    return "{0} BETWEEN date '{1}' AND date '{2}'".format(date_field, start_date, end_date)

if __name__ == '__main__':
    main()‍‍‍‍‍‍‍‍‍‍‍‍

This will yield:

Opening_Date BETWEEN date '2017-09-01 00:00:00' AND date '2017-09-30 23:59:59'‍

You should include the market_id too to limit the results to what you want.:

from calendar import monthrange, datetime

def main():

    fld_date = "Opening_Date"
    fld_market_id = "market_id"

    markets = [2, 3]
    years = [2016, 2017]
    months = [1, 2]

    for market in markets:
        for year in years:
            for month in months:
                date_query = queryDefDateBetweenForGivenMonth(year, month, fld_date)
                whereclause = "{0} = {1} AND ({2})".format(fld_market_id, market, date_query)
                print whereclause


def queryDefDateBetweenForGivenMonth(year, month, date_field):
    start_date = str(datetime.datetime(year, month, 1, 0, 0, 0))
    end_date = str(datetime.datetime(year, month, monthrange(year, month)[1], 23, 59, 59))
    return "{0} BETWEEN date '{1}' AND date '{2}'".format(date_field, start_date, end_date)

if __name__ == '__main__':
    main()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Which will yield:

market_id = 2 AND (Opening_Date BETWEEN date '2016-01-01 00:00:00' AND date '2016-01-31 23:59:59')
market_id = 2 AND (Opening_Date BETWEEN date '2016-02-01 00:00:00' AND date '2016-02-29 23:59:59')
market_id = 2 AND (Opening_Date BETWEEN date '2017-01-01 00:00:00' AND date '2017-01-31 23:59:59')
market_id = 2 AND (Opening_Date BETWEEN date '2017-02-01 00:00:00' AND date '2017-02-28 23:59:59')
market_id = 3 AND (Opening_Date BETWEEN date '2016-01-01 00:00:00' AND date '2016-01-31 23:59:59')
market_id = 3 AND (Opening_Date BETWEEN date '2016-02-01 00:00:00' AND date '2016-02-29 23:59:59')
market_id = 3 AND (Opening_Date BETWEEN date '2017-01-01 00:00:00' AND date '2017-01-31 23:59:59')
market_id = 3 AND (Opening_Date BETWEEN date '2017-02-01 00:00:00' AND date '2017-02-28 23:59:59')‍‍‍‍‍‍‍‍

Re: For Loop not looping correctly, ArcPy

MattAzemar — Thu, 01 Feb 2018 18:46:19 GMT

Hey Xander thanks for the response and advice. Your assumptions were correct and my data is stored in a geodatabase. I'll give your suggestions a try and let you know how it goes!

Re: For Loop not looping correctly, ArcPy

MattAzemar — Fri, 02 Feb 2018 19:34:14 GMT

The quick and easy way to fix the issue I posted earlier was to fix my where clause. It was an invalid SQL statement so by altering it to what is below, I was able to fix the issue.

whereclause = """{} IN (2, 3) """.format(arcpy.AddFieldDelimiters(fc, fieldname))‍

I was also able to further optimize my code and make it more streamline by using some of xander_bakker‌'s suggestions. Thanks for your help Xander.