topic Re: SQL in Python where statement using LIKE in Python Questions

SQL in Python where statement using LIKE

LaurenYee — Tue, 16 Jul 2013 13:47:35 GMT

Hi all, I'd like to construct a SQL statement for a search cursor using a LIKE with a wildcard instead of an =...but I'm having some difficulty as I find SQL in python with all it's backslashes, apostrophes, double quotes and escapes to be rather confusing. I don't know where to put the wildcard "%" and how to escape it in the code...

Here is what I've tried (couldn't get the script to work with "LIKE" but since I'm trying to find road names "LIKE" is necessary.

import arcpy import os import re  basedir = r"C:\Test" fc = r"G:\GIS\ParcelsPublic.shp" field1 = "ADD1" field2 = "STREETNAME"  try:     for fn in os.listdir(basedir):         add = re.sub(r"\D"," ", fn)         add = str.lstrip(add)         add = str.upper(add)         street = re.sub(r"[^a-zA-Z]"," ",fn)         street = str.lstrip(street)         street = str.upper(street)         print (add + " " + street)         expression = '"' + field1 + '"= ' + "'%s'" %add         expression2 = '"' + field2 +'"= ' + "'%s'" %street         expression3 = expression + " "+ "AND" +" " + expression2         rows = arcpy.SearchCursor(fc, fields="STREETNAME; ARN; ADD1", where_clause=expression3)

And this was provided to me on stackexchange but I haven't been able to implement a wildcard:

expression3 = '"{field1}" = \'{add}\' AND "{field2}" LIKE \'{street}\''.format(field1=field1, add=add, field2=field2, street=street)

Re: SQL in Python where statement using LIKE

MathewCoyle — Tue, 16 Jul 2013 14:03:56 GMT

I'm not sure I follow what you are trying to do. Something like this?

"{0} LIKE '%{1}'".format(field, value_suffix)

Re: SQL in Python where statement using LIKE

LaurenYee — Tue, 16 Jul 2013 14:30:42 GMT

This code is splitting an address in a file name at a hyphen, and getting variables for the number and street. This works fine.
Then it is passing these variables into the Parcel layer and using a search cursor to find the matching address to retrieve the roll number for the parcel (not shown in code), and replace the filename with the roll number.

Example:

Filename : Main St - 444
add = 444
street = Main St

The fieldname for the address is ADD1 and STREETNAME for street. And ARN for Roll #.

I basically want a SQL query similar to "ADD1 = 444 AND STREETNAME LIKE %MAIN ST%"
but to pass the variables in place.
I have tested a different SQL query using = and no LIKE or wildcard and this works if the streetname is exactly matching the one found in the parcels shapefile, but there are many streets such as "Main St S", "Main St N" which wouldn't match. Therefore I'd like to use the wildcard and LIKE to match to these addresses.

After matching the parcel the filename would then be changed to the matching ARN so filename = "34493850294023948"

I hope this cleared up and confusion.

Re: SQL in Python where statement using LIKE

MathewCoyle — Tue, 16 Jul 2013 14:41:44 GMT

Would this work?

expression3 = '"{field1}" = \'{add}\' AND "{field2}" LIKE \'%{street}%\''.format(field1=field1, add=add, field2=field2, street=street)

Re: SQL in Python where statement using LIKE

RichardFairhurst — Sat, 11 Dec 2021 07:36:12 GMT

The lines that built expression and expression2 were messed up (missing escape character to make single quote around your address and street literal) and expression3 badly written. They are corrected below.

import arcpy
import os
import re

basedir = r"C:\Test"
fc = r"G:\GIS\ParcelsPublic.shp"
field1 = "ADD1"
field2 = "STREETNAME"

try:
    for fn in os.listdir(basedir):
        add = re.sub(r"\D"," ", fn)
        add = str.lstrip(add)
        add = str.upper(add)
        street = re.sub(r"[^a-zA-Z]"," ",fn)
        street = str.lstrip(street)
        street = str.upper(street)
        print (add + " " + street)
        expression = '"' + field1 + '" = ' + add
        expression2 = '"' + field2 + '" LIKE \'%' + street + '%\''
        expression3 = expression + " AND " + expression2
        rows = arcpy.SearchCursor(fc, fields="STREETNAME; ARN; ADD1", where_clause=expression3)

I assumed ADD1 is a numeric field? If it is a string field rewrite the line that builds expression as:

expression = '"' + field1 + '" = \'' + add + '\''

And if you want the LIKE syntax applied to the address string use:

expression = '"' + field1 + '" = \'' + add + '%\''

Re: SQL in Python where statement using LIKE

LaurenYee — Tue, 16 Jul 2013 14:56:20 GMT

Thank you! That seems to be working fine.
Do you have any idea how one might use error checking in this code? For example if I can't split a string, is there a way I can just skip it? It's giving me a few errors with certain folders which don't follow the format with the hyphen!

Re: SQL in Python where statement using LIKE

RhettZufelt — Sat, 11 Dec 2021 07:36:15 GMT

Something like this might work:


if "-" in Filename:
  then do your split

So it would only do the split if the hyphen is in the string.

R_

Re: SQL in Python where statement using LIKE

JamesGirvan — Wed, 31 Jul 2013 14:06:30 GMT

I am a nonprogrammer and very new to Python. I would like to build a script tool for parcel searching. The tool will search a countywide parcel FC named Parcels in a FDS named Cadastre residing in a SQL enterprise GDB named EnterpriseGIS located on a network server named somcty06. The tool will require the user to select a municipality from a drop down, enter block number as text, lot as text, then zoom to the parcel. If no match it should return a No Parcel Found Matching Criteria message.