topic Re: Problems with da SearchCursor results from LIKE query in Python Questions

Problems with da SearchCursor results from LIKE query

ChrisStanley — Sat, 11 Dec 2021 20:57:40 GMT

I have built an app with WxPython to run in Arcmap to search some tables that are already existing in ArcMap as a Python Addin. I have a few fields in this table that will get results just fine and some fields that will get correct results and then no results from the same field with just different search values. My search is a LIKE type query, Ex, if I enter 55 as my search value, my WxPython app will not finish and open the PDF that has the results. If I enter 61 my search will finish and open the PDF with the appropriate data in the report. I do know there are several rows in my table that should be in the 355 search results.

If I run the code in the ArcMap Python window against the table with the 55 value I get the expected results with no problem from the cursor. The only difference that I can see are the variables I am using in the query. If I run it in the ArcMap Python window I am setting the variables manually to make sure the query is good. In the WxPython App I am getting one variable from a selection made from a dropdown for the field "ESN" and the search value is from a text box where the user enters the value, "55".

The examples below are from a script that I am testing in the ArcMap window and then the search that my WxPython App is using. The syntax is all correct so the indentations may be different between the two. My main issue is why can't I get results when I use "55" as my value and I can get results when I search "61" when there are values in the table that should get results when I search LIKE '%55%'. Also this same thing is happening in the Community field when I search LIKE values in there as well.

I have tested several variations and other fields and most everything will return results but I can't see any reason these will not.

Any help would be GREATLY appreciated!!!

Thanks

Chris

Here is the snippet from my manual search that gives me the results I want:

test = 'ESN'

#Query to retreive data from gisMSAG for GeoSearch...
story = []
results=[]
fields = ['LOW','HIGH','DIR','STREET NAME','EOB','COMMUNITY', 'ZIP','ESN','SRNUM']
story.append(fields)
with arcpy.da.SearchCursor(fc3,[dir,sname,low,high,com,eob,esn,sr,zip,desc,rem],test+" LIKE UPPER('%55%')",sql_clause=(None,'ORDER BY '+sname+' ASC, '+low+' ')) 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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Here is the snipped of the same search from my WxPython App:

story = []
results=[]
fields = ['LOW','HIGH','DIR','STREET NAME','EOB','COMMUNITY', 'ZIP','ESN','SRNUM']
story.append(fields)
self.SBar.SetStatusText(str(selectedfield+" "+searchvalue))
with arcpy.da.SearchCursor(fc3,[dir,sname,low,high,com,eob,esn,sr,zip,desc,rem],selectedfield+" LIKE '%"+str(searchvalue)+"%'",sql_clause=(None,'ORDER BY '+sname+' ASC, '+low+' ')) 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‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Mon, 23 Jan 2017 21:47:31 GMT

Chris /blogs/dan_patterson/2016/08/14/script-formatting

Currently it is kind of hard to read

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Mon, 23 Jan 2017 21:53:43 GMT

OK, see if that's better, I didn't realize it would put it in there that way.....thanks

Chris

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Mon, 23 Jan 2017 21:56:57 GMT

Also, if I need to post the whole thing I can, I was just mainly wanting to make sure I wasn't missing something and couldn't figure out why 61 will work and 55 will not. It's very weird.

Thanks

Chris

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Mon, 23 Jan 2017 22:41:48 GMT

At this stage, I would throw a print statement, capturing the query to make sure it is properly formatted in both cases

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Mon, 23 Jan 2017 23:44:57 GMT

I set the query to print in my status bar and both look the same:

I have a status bar entry set before the script runs since something is causing it to fail and then one at the end just before the script finishes and opens a PDF.

This one comes up when I put in a value of 55 and the script stops just after this during the cursor.

This one works and comes up at the end of the script and then opens the PDF with the search results. The - END Print part is just extra string I put in to show when it printed.

They both look the same to me except for the numbers being searched.

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Mon, 23 Jan 2017 23:52:56 GMT

Well, the field names look the same, the LIKE query has single quotes around them, you have the %s around the numbers.... and assuming that 55 does exist, I have no clue. Try %5% does that bring up 55? are any other numbers experience this bizarre behaviour?

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Tue, 24 Jan 2017 00:25:39 GMT

The %5% doesn't work and even though I get results with %62%, %6% doesn't work either. If I do a search with any value where there aren't going to be any results the script always finishes and opens a PDF with 0 records so I know something is breaking the query but since it's in a form it's hard to troubleshoot and haven't been able to figure out anything where I can capture any errors or anything.

I have another dataset that works just fine and even just put in a record with a 355 to be able to search and it works fine as well. I just wanted to make sure I wasn't missing anything or wasn't aware of some kind of limitation with LIKE or something. I'm going to check the table to make sure there's nothing weird going on there since everything works in my control dataset.

Thanks

Chris

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Tue, 24 Jan 2017 01:10:39 GMT

To state the obvious and to just ensure you are querying strings... 6 isn't the same as "6" ... first is a number, second is a string and LIKE doesn't work with numbers

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Tue, 24 Jan 2017 01:13:24 GMT

Yes, I am using strings for my queries.

Re: Problems with da SearchCursor results from LIKE query

NathanHeick — Tue, 24 Jan 2017 02:37:20 GMT

I would try removing the sql_clause and retesting it.

Nathan

Re: Problems with da SearchCursor results from LIKE query

XanderBakker — Tue, 24 Jan 2017 03:05:27 GMT

The thing I notice is the strange way the list of field is defined. Since you did not post the complete code, it is difficult to see if this is really wrong. You are not specifying strings, but variables and including two reserved words (dir and zip). I don't think that will work, unless you defined those variable somewhere before referring to names of the fields and thereby overwritten the dir and zip objects (which remains strange, since just before the SearchCursor statement, you define a list of field names).

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Wed, 25 Jan 2017 23:17:18 GMT

your EDN field contains just 3 numbers in text format? #1, there is no need for upper, #2 why not get rid of the % stuff all together for testing... I am sure that someone can enter 3 values. I might suggest that you ... 'str' ... any values entered to rule out numbers not being converted to text

Re: Problems with da SearchCursor results from LIKE query

NathanHeick — Thu, 26 Jan 2017 02:00:57 GMT

You need to find a debugging method to isolate the problem to a specific line. Can you launch your application from a command line and print to stdout? At the least, you could always write to a file. You should be able to wrap the cursor in a try-except block and write the rows and any error messages to stdout or a file. I suspect the cursor may be fine and the report is choking on the records you pass into it for certain searches.

Nathan

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Thu, 26 Jan 2017 22:15:19 GMT

Today I setup the Try-except and initially it didn't yield any results on just the cursor but I put it on the whole script so it would catch anything and I did get a result but I've researched it and cannot find anything similar to what doing.

The error I am getting when the issue happens with ESN LIKE '%55%' is Errno 9 Bad file descriptor

I tested with a value that I know works in my form and the error was not in the file from the Except and I tested with the 55 and it is in the file so I'm pretty sure that this is what I've been looking for but I haven't been able to find anything yet.

I'm thinking it's something with WXPython somehow because I can take this same script and fill in the values I'm getting from the form right in the script and just run it only in python and I get results every time I search anything.

Thanks again for the replies so far....

Chris

Re: Problems with da SearchCursor results from LIKE query

DanPatterson_Retired — Thu, 26 Jan 2017 22:31:50 GMT

The word 'form' rings a bell... I know Arc* doesn't play well with Tk so I am guessing it could be an issue. If the can be run as a script via providing the parameters outside a dialog interface and it works... then you have your solution. If you are running this inside of arcmap anyway, why not use a conventional script tool or a python tool and toolbox?

Re: Problems with da SearchCursor results from LIKE query

NathanHeick — Thu, 26 Jan 2017 23:19:21 GMT

I think your error is related to line 255 or a combination of 252 and 255. I think Popen is trying to open a file that has already been closed.

Try using os.system(fileopen) instead or do more research on that error, reportlab, WxPython, and Popen. I think there may be multiple threads trying to access the same file at the same time.

Nathan

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Wed, 15 Feb 2017 14:19:17 GMT

All,

I wanted to follow up with an update on this issue in case someone else runs into this in the future. I was able to get everything resolved. The issue was with line 98 where I had put in "print story". I had this in there when I was creating everything so I could see results during testing and never really thought anything about it. I could tell that with some queries that would work that the amount of records being returned were less than the queries that wouldn't work. For some queries like the "55" that was failing, once I took out 20 records from the table that one would work. I was just cleaning up some of the code and commenting out unnecessary things and once I commented out the "print story" every query ran with no issues. The only thing I could think of was that something with building the story for ReportLab wasn't liking that because the "print story" worked fine when I ran queries outside of my application. Either way it's working now and I appreciate everyone's input and assistance to work through the issue.

Chris

Re: Problems with da SearchCursor results from LIKE query

ChrisStanley — Sat, 11 Dec 2021 20:57:44 GMT

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)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍