topic Use list as input parameter to query FC in Python Questions

Use list as input parameter to query FC

jaykapalczynski — Sun, 12 Dec 2021 07:32:54 GMT

I have a LIST containing SiteNames (this is a LIST i created named "mynewlist")
I have a FC that has a SiteName field

I want to create a LIST / ARRAY of the data that is in the FC, where the List (mynewlist) and FC (Field "SiteName") are the same.

For instance...

I have 5 Sitenames in the LIST (# 1 above)

I have 200 Sitenames in the FC (#2 above)

I want a resulting LIST that is comprised of fields 1-9 from the FC where the LIST values are the same as the FC SiteName field.

Think I have everything in order just need help with the actual query???

I don't even know if I can create a Query from a LIST and a FC....do I need to convert the list to something else first?

arcpy.env.workspace = "C:\\Users\\xxxx\\AppData\\Roaming\ESRI\\Desktop10.4\\ArcCatalog\\xx@xx.sde"
myFCLayer = "BoatRampsWGS"

#Set variables for the FC above
var_SITENAME,var_WATERBODY,var_ACCESSAREA,var_BODYOFWATE = 'SITENAME' ,'WATERBODY','ACCESSAREA','BODYOFWATE'
var_TYPE,var_NO_OFRAMPS,var_COUNTY,var_REGION = 'TYPE','NO_OFRAMPS','COUNTY','REGION'
var_Lat,var_Long = 'Lat','Long'

QueryBoatRamps = [var_SITENAME,var_COUNTY,var_REGION,var_WATERBODY,var_BODYOFWATE,var_ACCESSAREA,var_TYPE,var_NO_OFRAMPS,var_Lat,var_Long]


# Build a query that will grab the records from the FC where the two SiteName fields are the same from the FC (BoatRampsWGS) and the LIST (myBoatRampList)

RampQry=  #Grab all the records from FC (BoatRampsWGS) where th4e Sitename is the same as the sitename in the LIST (myBoatRampList)



# write these records to a new list
BoatRampList=[]
with arcpy.da.SearchCursor(myFCLayer, QueryBoatRamps, RampQry) as cursor:
    for row in cursor:
        varBoatRampsList = str('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
        BoatRampList.append(varBoatRampsList)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Use list as input parameter to query FC

jaykapalczynski — Sun, 12 Dec 2021 07:32:57 GMT

I can hard code a sitename and get a return...just don't know how to get it to read the LIST and give me a record for each value in the LIST.

How do I replace my LIST with the variable "varQuery"

varQuery = "Airfield Pond"
RampQry="""SITENAME = '{}'""".format(varQuery)
print RampQry

# write these records to a new list
BoatRampList=[]
with arcpy.da.SearchCursor(myLayer, QueryBoatRamps, RampQry) as cursor:
    for row in cursor:
        varBoatRampsList = str('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
        BoatRampList.append(varBoatRampsList)
del cursor
print BoatRampList‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Result:

['Airfield Pond,Sussex,1,Airfield Pond,AIRFIELD POND,AIRFIELD POND,R,1.0,32.90782387,-80.02724715']

Re: Use list as input parameter to query FC

jaykapalczynski — Sun, 12 Dec 2021 07:32:59 GMT

I am trying this but getting an error...

ERROR:

Traceback (most recent call last):
File "E:\ArcGISProjects\BoatRampFacilities\PythonScripts\PythonSync\PythonScripts\~6_30days ago.py", line 170, in <module>
for row in cursor:
RuntimeError: Underlying DBMS error [[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 's'.] [DGIF_TEST.DBO.BoatRampsWGS]

LIST: CALLED "matched"

['Airfield Pond', 'American Legion', 'Anthony Ford #4', 'Aylett', 'Blackwater', 'Blackwater Bridge', "Carey's", "Carter's Wharf", 'Coopers', 'Deep Bottom']

I think I am getting the error because the value has an apostrophe????

Carey's

#Set variables for the FC above
var_SITENAME,var_WATERBODY,var_ACCESSAREA,var_BODYOFWATE = 'SITENAME' ,'WATERBODY','ACCESSAREA','BODYOFWATE'
var_TYPE,var_NO_OFRAMPS,var_COUNTY,var_REGION = 'TYPE','NO_OFRAMPS','COUNTY','REGION'
var_Lat,var_Long = 'Lat','Long'

QueryBoatRamps = [var_SITENAME,var_COUNTY,var_REGION,var_WATERBODY,var_BODYOFWATE,var_ACCESSAREA,var_TYPE,var_NO_OFRAMPS,var_Lat,var_Long]

BoatRampList=[]

# Build a query that will grab the records from the FC where the two SiteName fields are the same from the FC (BoatRampsWGS) and the LIST (myBoatRampList)

for i, val in enumerate(matched):
    varQuery = val
    print varQuery
    RampQry="""SITENAME = '{}'""".format(varQuery)
    #print RampQry

    with arcpy.da.SearchCursor(myLayer, QueryBoatRamps, RampQry) as cursor:
        for row in cursor:
            varBoatRampsList = str('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
            BoatRampList.append(varBoatRampsList)
    del cursor
print BoatRampList‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Use list as input parameter to query FC

DanPatterson_Retired — Sun, 12 Dec 2021 07:33:02 GMT

side tangent...

A tip

row = [1, 'a', 3, 'b', 5, 6, 7, 'c', 'd']

frmt = ("{}, "*len(row)).format(*row)  # to account for unknown 

print(frmt[:-2])  # snip off the extra comma and space
1, a, 3, b, 5, 6, 7, c, d

# alternative 2
", ".join(["{}".format(i) for i in row])  # just saves the snip
'1, a, 3, b, 5, 6, 7, c, d'

‍‍‍‍‍‍‍‍‍‍‍

Re: Use list as input parameter to query FC

jaykapalczynski — Sun, 12 Dec 2021 07:33:05 GMT

THINK I GOT IT....one minute for testing

I had to fix the apostrophe

if string.find(varQuery, "'") <> -1: 
print 'fixing apostrophe in', varQuery, 'to be queryable.' 
fn_name_qry = string.replace(varQuery, "'", "''") 
print fn_name_qry 
else: 
fn_name_qry = varQuery‍‍‍‍‍‍

#Set variables for the FC above
var_SITENAME,var_WATERBODY,var_ACCESSAREA,var_BODYOFWATE = 'SITENAME' ,'WATERBODY','ACCESSAREA','BODYOFWATE'
var_TYPE,var_NO_OFRAMPS,var_COUNTY,var_REGION = 'TYPE','NO_OFRAMPS','COUNTY','REGION'
var_Lat,var_Long = 'Lat','Long'

QueryBoatRamps = [var_SITENAME,var_COUNTY,var_REGION,var_WATERBODY,var_BODYOFWATE,var_ACCESSAREA,var_TYPE,var_NO_OFRAMPS,var_Lat,var_Long]

BoatRampList=[]

# Build a query that will grab the records from the FC where the two SiteName fields are the same from the FC (BoatRampsWGS) and the LIST (myBoatRampList)
print ""
for i, val in enumerate(mynewMatchedlist):
    varQuery = val
    print varQuery

    if string.find(varQuery, "'") <> -1:  
        print 'fixing apostrophe in', varQuery, 'to be queryable.'  
        fn_name_qry = string.replace(varQuery, "'", "''")  
        print fn_name_qry  
    else:   
        fn_name_qry = varQuery
        
    #RampQry="""SITENAME = '{}'""".format(varQuery)
    RampQry="""SITENAME = '{}'""".format(fn_name_qry)
    #print RampQry

    with arcpy.da.SearchCursor(myLayer, QueryBoatRamps, RampQry) as cursor:
        for row in cursor:
            varBoatRampsList = str('{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}'.format(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
            BoatRampList.append(varBoatRampsList)
    del cursor
print BoatRampList‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍