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:
<SPAN class="keyword token">import</SPAN> arcpy
<SPAN class="comment token"># Set environment settings</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"</SPAN>
<SPAN class="comment token">#Declare variables</SPAN>
fc <SPAN class="operator token">=</SPAN> <SPAN class="string token">'Open_Store_Centers'</SPAN>
fields <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'USER_market_id'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'USER_Store_ID'</SPAN><SPAN class="punctuation token">,</SPAN><SPAN class="string token">'USER_Store_Center_Name'</SPAN><SPAN class="punctuation token">]</SPAN>
fieldname <SPAN class="operator token">=</SPAN> <SPAN class="string token">'USER_market_id'</SPAN>
<SPAN class="comment token">#Define WHERE clause statement</SPAN>
whereclause <SPAN class="operator token">=</SPAN> <SPAN class="string token">"""{} = 2000"""</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>AddFieldDelimiters<SPAN class="punctuation token">(</SPAN>fc<SPAN class="punctuation token">,</SPAN> fieldname<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN>
sqlclause <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Order By USER_market_id, USER_Store_ID'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># loop through months</SPAN>
years <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="number token">2015</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2016</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="number token">2017</SPAN><SPAN class="punctuation token">]</SPAN>
months <SPAN class="operator token">=</SPAN> <SPAN class="punctuation token">[</SPAN><SPAN class="string token">'Jan'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Feb'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Mar'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Apr'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'May'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Jun'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Jul'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Aug'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Sep'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Oct'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Nov'</SPAN><SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'Dec'</SPAN><SPAN class="punctuation token">]</SPAN>
<SPAN class="keyword token">for</SPAN> year <SPAN class="keyword token">in</SPAN> years<SPAN class="punctuation token">:</SPAN>
<SPAN class="keyword token">for</SPAN> month <SPAN class="keyword token">in</SPAN> months<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>in_table <SPAN class="operator token">=</SPAN> fc<SPAN class="punctuation token">,</SPAN> field_names <SPAN class="operator token">=</SPAN> fields<SPAN class="punctuation token">,</SPAN> where_clause<SPAN class="operator token">=</SPAN>whereclause<SPAN class="punctuation token">,</SPAN> sql_clause<SPAN class="operator token">=</SPAN><SPAN class="punctuation token">(</SPAN>None<SPAN class="punctuation token">,</SPAN> <SPAN class="string token">'ORDER BY USER_market_id, USER_Store_ID'</SPAN><SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">)</SPAN> <SPAN class="keyword token">as</SPAN> cursor<SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token">#Loop through each row established in cursor</SPAN>
<SPAN class="keyword token">for</SPAN> row <SPAN class="keyword token">in</SPAN> <SPAN class="punctuation token">(</SPAN>cursor<SPAN class="punctuation token">)</SPAN><SPAN class="punctuation token">:</SPAN>
<SPAN class="comment token"># Set local variables for FeatureClasstoFeatureClass</SPAN>
inFeatures <SPAN class="operator token">=</SPAN> <SPAN class="string token">"PatientVisitsGeocoded"</SPAN>
outLocation <SPAN class="operator token">=</SPAN> r<SPAN class="string token">"C:\arcGIS_Shared\Python\CenterHeatMaps.gdb"</SPAN>
outFeatureClass <SPAN class="operator token">=</SPAN> <SPAN class="string token">"{2}{3}_NWPatientVisits{0}{1}"</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">2</SPAN><SPAN class="punctuation token">]</SPAN><SPAN class="punctuation token">,</SPAN> month<SPAN class="punctuation token">,</SPAN> year<SPAN class="punctuation token">)</SPAN>
delimitedfield <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>AddFieldDelimiters<SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>env<SPAN class="punctuation token">.</SPAN>workspace<SPAN class="punctuation token">,</SPAN><SPAN class="string token">"USER_CenterID"</SPAN><SPAN class="punctuation token">)</SPAN>
expression <SPAN class="operator token">=</SPAN> delimitedfield <SPAN class="operator token">+</SPAN> <SPAN class="string token">"= {0}"</SPAN><SPAN class="punctuation token">.</SPAN>format<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="operator token">+</SPAN> <SPAN class="string token">" AND USER_DOSYear = {0}"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>year<SPAN class="punctuation token">)</SPAN> <SPAN class="operator token">+</SPAN> <SPAN class="string token">" AND USER_DOSMonth = '{0}'"</SPAN><SPAN class="punctuation token">.</SPAN>format<SPAN class="punctuation token">(</SPAN>month<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token"># Execute FeatureClassToFeatureClass</SPAN>
arcpy<SPAN class="punctuation token">.</SPAN>FeatureClassToFeatureClass_conversion<SPAN class="punctuation token">(</SPAN>inFeatures<SPAN class="punctuation token">,</SPAN> outLocation<SPAN class="punctuation token">,</SPAN> outFeatureClass<SPAN class="punctuation token">,</SPAN> expression<SPAN class="punctuation token">)</SPAN>
<SPAN class="comment token">#Print Results</SPAN>
<SPAN class="keyword token">print</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>
count <SPAN class="operator token">=</SPAN> arcpy<SPAN class="punctuation token">.</SPAN>GetMessageCount<SPAN class="punctuation token">(</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="keyword token">print</SPAN> <SPAN class="punctuation token">(</SPAN>arcpy<SPAN class="punctuation token">.</SPAN>GetMessage<SPAN class="punctuation token">(</SPAN>count<SPAN class="number token">-1</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></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>Any help/advice would be much appreciated. Thanks.
