Problems with da SearchCursor results from LIKE query
2861
18
01-23-2017 01:40 PM
by
by
by
Thanks to all of the replies......I did try removing the sql_clause and I still get the same results and just to make sure, I did change the zip and dir variables that I had set but it didn't change anything.
I have posted the complete code here to see if some can see anything.
I have tried multiple things and cannot understand why some numbers will return results and some will not. If I select ESN from my dropdown on the form that becomes the selectedfield variable and whatever I enter into the text box on the form becomes the searchvalue variable.
Everything I am searching for is in the table and I should get results. If I search something that I know is not in the dataset I will still get the PDF to open but with 0 results. If I enter 55 the PDF never opens so I know something in the query is not working. If I enter 62 I get results with several records and the PDF opens with the results. Everything I've tried in the 50's doesn't work but most of the 60's will work. Below is the table so you can see there are values with 55.
If I do this same search in Select By Attributes it will select fine.
I have another dataset that I have built this on and everything in that works fine, no issues like this, I can get results on anything I search. I did switch out this table with the same table from my test dataset just to make sure they are the same but still have the same results.
I really do not know where things are breaking or why.
Chris
import arcpy
import pythonaddins
import wx
import os
import subprocess
import datetime
import sys
import traceback
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import letter, landscape, portrait, A4
from reportlab.platypus import Image, Paragraph, Table,SimpleDocTemplate,Frame, BaseDocTemplate, PageTemplate, TableStyle
from reportlab.lib.colors import red, black
from reportlab.lib.styles import ParagraphStyle, getSampleStyleSheet
from reportlab.lib.units import inch
from reportlab.lib.enums import TA_JUSTIFY, TA_LEFT, TA_CENTER
from reportlab.lib import colors
selectedfield = str(self.TableFields.GetValue())
searchvalue = str(self.SearchValue.GetValue())
msagradio = str(self.radiobtnMSAG.GetValue())
intradio = str(self.radiobtnINT.GetValue())
maddrradio = str(self.radiobtnMAddr.GetValue())
if msagradio == "True":
#Get Date/Time needed
self.SBar.SetStatusText('Building MSAG Geo Search Report.....')
now = datetime.datetime.now()
filedate = now.strftime("%m%d%Y")
filemonth = now.strftime("%m%Y")
#Get Agency Demographics from GBO Report Settings Table
fc1 = "gboReportSettings"
with arcpy.da.SearchCursor(fc1,["PROPERTY","VALUE"]) as cursor:
for ad in cursor:
if ad[0] == "AgencyName":
agencyname = ad[1]
elif ad[0] == "AgencyState":
agencystate = ad[1]
elif ad[0] == "AgencyDeptName":
agencydept = ad[1]
elif ad[0] == "REPORTDIRECTORY":
reportpath = ad[1]
elif ad[0] == "AgencyMail":
mail = ad[1]
elif ad[0] == "AgencyMailCity":
mailcity = ad[1]
elif ad[0] == "AgencyPhone":
phone = ad[1]
elif ad[0] == "AgencyFax":
fax = ad[1]
elif ad[0] == "AgencyEmail":
email = ad[1]
def trim(fieldvalue):
fieldvalue=' '.join(fieldvalue.strip().split())
if not fieldvalue: fieldvalue=' '
return fieldvalue
#Get fields from gisMSAG table in GBO and set fields to display on report
fc3 = "gisMSAG"
msagf = [f.name for f in arcpy.ListFields(fc3)]
print msagf
dirs = str(msagf[1])
sname = str(msagf[2])
low = str(msagf[5])
high = str(msagf[6])
com = str(msagf[7])
eob = str(msagf[9])
esn = str(msagf[10])
sr = str(msagf[21])
zips = str(msagf[22])
mapgrid = str(msagf[23])
desc = str(msagf[24])
rem = str(msagf[25])
msagfields = dirs,sname,low,high,com,eob,esn,sr,zips,desc,rem
#Query to retreive data from Address Points for edited in last # of days
story = []
results=[]
fields = ['LOW','HIGH','DIR','STREET NAME','EOB','COMMUNITY', 'ZIP','ESN','SRNUM']
story.append(fields)
query = str(selectedfield)+" LIKE UPPER('%"+str(searchvalue)+"%')"
self.SBar.SetStatusText(str(query))
with arcpy.da.SearchCursor(fc3,msagfields,query) as cursor5:
for mg in cursor5:
msagdir = mg[0]
msagsname = mg[1]
msaglow = mg[2]
msaghigh = mg[3]
msagcom = mg[4]
msageob = mg[5]
msagesn = mg[6]
msagsr = mg[7]
msagzip = mg[8]
display = msaglow,msaghigh,msagdir,msagsname,msageob,msagcom,msagzip,msagesn,msagsr
story.append(display)
print story
self.SBar.SetStatusText('Gets Past query.....')
#Count Records in Cursor5
rowCount = 0
for row in cursor5:
rowCount = rowCount + 1
print rowCount
count = ["Total Records from Selection: "+str(rowCount)]
story.append(count)
styles = getSampleStyleSheet()
styleN = styles['Normal']
styleH = styles['Heading1']
# bodytext style used for wrapping data on flowables
styles = getSampleStyleSheet()
styleN = styles["BodyText"]
#used alignment if required
styleN.alignment = TA_LEFT
styleBH = styles["Normal"]
styleBH.alignment = TA_CENTER
styleBH.fontSize = 24
sdName = Paragraph("<b>"+agencyname+"</b>", styleBH)
styleBH2 = styles["Normal"]
styleBH2.alignment = TA_CENTER
styleBH2.fontSize = 12
self.SBar.SetStatusText('Gets to Field Labels.....')
#Replace field label names.....
if selectedfield == str.upper(dirs):
fieldlabel = "Prefix Direction"
elif selectedfield == str.upper(com):
fieldlabel = "Community"
elif selectedfield == str.upper(sname):
fieldlabel = "Street Name"
elif selectedfield == str.upper(low):
fieldlabel = "Low Number"
elif selectedfield == str.upper(high):
fieldlabel = "High Number"
elif selectedfield == str.upper(com):
fieldlabel = "Community"
elif selectedfield == str.upper(eob):
fieldlabel = "Even/Odd/Both"
elif selectedfield == str.upper(esn):
fieldlabel = "ESN"
elif selectedfield == str.upper(sr):
fieldlabel = "State Road #"
elif selectedfield == str.upper(zips):
fieldlabel = "Zip Code"
elif selectedfield == str.upper(mapgrid):
fieldlabel = "Map Grid"
elif selectedfield == str.upper(desc):
fieldlabel = "Description"
elif selectedfield == str.upper(rem):
fieldlabel = "Remarks"
self.SBar.SetStatusText('Gets to Title.....')
componentA = Paragraph('<i>MSAG Geo Search Report - '+fieldlabel+' Containing "'+searchvalue+'"</i>', styleBH2)
#<font color=red>
headerdata= [[sdName],
[],
[componentA]]
table = Table(headerdata,colWidths = 745)
table.setStyle(TableStyle([
('LINEBELOW',(-1,-1),(-1,2),1,colors.black),
#('BOX', (0,0), (-1,2), 0.25, colors.black),
#('BOX', (0,1), (-1,2), 0.25, colors.black),
('ALIGNMENT', (0,0), (-1,2), 'CENTER'),
#('GRID', (0,0), (-1,-1), 0.25, colors.black),
]))
footerdata= [[agencyname +"-"+agencydept,"","Phone: "+phone],
[mail,"","Fax: "+fax],
[mailcity,"",email]]
footertable = Table(footerdata,colWidths = 249)
footertable.setStyle(TableStyle([
('LINEABOVE',(0,0),(-1,0),1,colors.black),
#('BOX', (0,0), (-1,2), 0.25, colors.black),
#('BOX', (0,1), (-1,2), 0.25, colors.black),
('ALIGNMENT', (0,0), (-1,-1), 'LEFT'),
('ALIGNMENT', (2,-3), (2,2), 'RIGHT'),
('BOTTOMPADDING', (0,0), (-1,-2), -1),
#('GRID', (0,0), (-1,-1), 0.25, colors.black),
]))
def header(canvas, doc):
canvas.saveState()
P = Paragraph("This is a multi-line footer. It goes on every page. " * 5, styleN)
w, h = P.wrap(doc.width, doc.topMargin)
table.wrapOn(canvas, 650, 500)
table.drawOn(canvas, 25, 550)
canvas.restoreState()
def footer(canvas, doc):
canvas.saveState()
P = Paragraph("This is a multi-line footer. It goes on every page. " * 5, styleN)
w, h = P.wrap(doc.width, doc.topMargin)
footertable.wrapOn(canvas, 650, 500)
footertable.drawOn(canvas, 25, 10)
for i in range(5):
pagenum = canvas.getPageNumber()
text = "Page %s" % pagenum
canvas.drawString(380,10 ,text)
canvas.restoreState()
if not os.path.exists(reportpath+filemonth):
os.makedirs(reportpath+filemonth)
doc = BaseDocTemplate(reportpath + filemonth + "\\"+"MSAGGeoSearchReport.pdf", pagesize = landscape(letter))
fileopen = reportpath + filemonth + "\\" + "MSAGGeoSearchReport.pdf"
frame = Frame(-1, 65, 790, doc.height, id='normal')
template = PageTemplate(id='test', frames=frame, onPage=header,onPageEnd=footer)
doc.addPageTemplates([template])
elements=[]
#columns = [45,45,45,270,40,150,45,45,55]
t = Table(story, rowHeights=20, repeatRows=1, hAlign = 'CENTER')
t.setStyle(TableStyle([
('ALIGN', (0, 0), (-1, -1), 'LEFT'),
('GRID', (0, 0), (-1, -1), 0.25, colors.black),
('BOX', (0, 0), (-1, -1), 1, colors.black),
('TEXTCOLOR', (0, 0), (8, 0), colors.red),
('FONTSIZE', (0, 0), (8, 0), 12),
('RIGHTPADDING', (0, 0), (-1,-1), 15),
('SPAN', (0, -1), (-1,-1)),
('TEXTCOLOR', (0, -1), (-1, -1), colors.red),
('INNERGRID', (0, 0), (-1, -1), 0.25, colors.black),
]))
data_len = len(story)
for each in range(data_len):
if each % 2 == 0:
bg_color = colors.white
else:
bg_color = colors.lavender
t.setStyle(TableStyle([('BACKGROUND', (0, each), (-1, each), bg_color)]))
elements.append(t)
doc.build(elements)
self.SBar.SetStatusText('Geo Search Report Finished.....')
self.SBar.SetStatusText(str(query + " - END Print"))
subprocess.Popen(fileopen, shell=True)
by
by
by
by
