topic Re: ArcPy Feature Class to Feature Class blank results - ArcGIS Pro in Python Questions

ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

Business_IntelligenceSoftware — Sun, 12 Dec 2021 03:02:26 GMT

I have this Feature Class to Feature Class script that creates a feature class of patient visits for each store for every month for every year it has been open. However, when I run the script it will create feature classes by month and year with the correct labels, for example (Jan2015_NWPatientVisits2000Gresham), but when I look at the attribute table it contains no data.

I believe the problem is in my "expression" because when I alter it to run on only "USER_DOSYear" or "USER_DOSMonth" it will populate, but when I try and string them together that is when the problem occurs.

I'm sure I am missing something small, but I just can't seem to figure out what it is.

Here is my script:

import arcpy

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

#Declare variables
fc = 'Open_Store_Centers'
fields = ['USER_market_id','USER_Store_ID','USER_Store_Center_Name']
fieldname = 'USER_market_id'

#Define WHERE clause statement
whereclause = """{} = 2000""".format(arcpy.AddFieldDelimiters(fc, fieldname))
sqlclause = (None, 'Order By USER_market_id, USER_Store_ID')

# loop through months
years = [2015, 2016, 2017]
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

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

#         Execute FeatureClassToFeatureClass
                arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)

        #Print Results
                print(row[2])
                count = arcpy.GetMessageCount()
                print (arcpy.GetMessage(count-1))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Any help/advice would be much appreciated. Thanks.

Re: ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

JoshuaBixby — Tue, 23 Jan 2018 20:38:47 GMT

To get the SQL syntax sorted out, what about trying a couple or few expressions manually in ArcMap GUI. Without have a bit more information about the data itself, it is hard for me to provide any specific suggestions.

Re: ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

deleted-user-qzyyiYme1rz9 — Fri, 26 Jan 2018 18:36:18 GMT

My suggestion would be similar to Joshua Bixby, but first I'd put a print statement to print your expression clause so you can see what it looks like. When the expression is printed it may be obvious where the error is, if not, try to copy/paste that manually in ArcMap and make changes in ArcMap until you have the correct syntax for the clause, then work backwards and adjust your Python script to match.

Re: ArcPy Feature Class to Feature Class blank results - ArcGIS Pro

Business_IntelligenceSoftware — Sun, 12 Dec 2021 03:02:28 GMT

I honestly don't really even now what I did to fix this, but it works.

Here is my final code:

import arcpy
import datetime 
from datetime import timedelta
import time

#Document Start Time in-order to calculate Run Time
time1 = time.clock()

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

#Declare variables
fc = 'Open_Store_Centers'
fields = ['USER_market_id','USER_Store_ID','USER_Store_Center_Name', 'USER_Opening_Date']
fieldname = 'USER_market_id'

#Define WHERE clause statement
whereclause = """{} = 2000""".format(arcpy.AddFieldDelimiters(fc, fieldname))
sqlclause = (None, 'Order By USER_market_id, USER_Store_ID')

# loop through months
years = [2014, 2015, 2016, 2017]
months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']

for year in years:
    for month in months:
        with arcpy.da.SearchCursor(in_table = fc, field_names = fields, where_clause=whereclause, sql_clause=(None, 'ORDER BY USER_market_id, USER_Store_ID')) as cursor:
            #Loop through each row established in cursor
            for row in (cursor):
                # Set local variables for FeatureClasstoFeatureClass
                inFeatures = "PatientVisitsGeocoded"
                outLocation = r"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"
                outFeatureClass = "{2}{3}_NWPatientVisits{0}{1}".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], month, year)


#               Execute FeatureClassToFeatureClass
                arcpy.FeatureClassToFeatureClass_conversion(inFeatures, outLocation, outFeatureClass, expression)

                #Print Results
                print(row[2])
                count = arcpy.GetMessageCount()
                print (arcpy.GetMessage(count-1)) 

#Document End Time
time2 = time.clock()

#Run Time in seconds
runtime = (time2-time1)

print (str(timedelta(seconds=runtime))) ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍