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)
Solved! Go to Solution.
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
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']
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
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'
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