AnsweredAssumed Answered

sql statement in python with gdb

Question asked by n.ueberschaeresri-rw-esridist Employee on Sep 30, 2016
Latest reply on Oct 3, 2016 by bixb0012

This is what I'm trying to do:

From the targetLayer I pick the value for the PSS_ID attribute, look for the same value in my in_table and retrieve the values for the fields 2 and 3 (row2[1] and row2[2]).

I managed to do it with shp-files (and the ID was a string):

expression='\"UPI\"=\''+row[0]+'\''

but now I have to apply it on a gdb and I struggle to find the right way to put the sql statement ("expression").

#walk through targetLayer, retrieve the PSS_ID
cursor = arcpy.da.SearchCursor(targetLayer, join1)
for row in cursor:
    print(row[0]) #prints the PSS_ID

#walk through joinTable, look for rows that have the same UPI as the targetLayer and retrieve UPI [0], Taxonomy [1] and FAO_CLASSI [2]
    Tax_con=""                         #Variable for adding up the values
    in_table=outputJoin                #Variable in which table to look for the values
    field_name1=join1                  #First field name
    field_name2=join2                  #Second field name
    field_name3=join3                  #Third field name
    expression= '\"'+join1+'='+str(row[0])+'\"'       #SQL expression to look for the right row
    print expression                   #prints the expression
    #SearchCursor looking for the values corresponding to the requested UPI
    cursor2 = arcpy.da.SearchCursor(in_table, [field_name1, field_name2, field_name3],expression)              
    for row2 in cursor2:
        row_text= row2[1]+ row2[2]

 

I looked for example here Specifying a query in Python—ArcPy Get Started | ArcGIS for Desktop   and here ArcGIS Desktop and here How To: Use Python to determine the SQL syntax for a WHERE clause depending on the workspace type but I always get the same response: Invalid SQL statement  

 

I also tried to put the expression straight to the where_clause position but it didn't seem to make any difference.

Here are the version that I tried (output from "print expression"):

"PSS_ID=13.0"

PSS_ID=13.0

[PSS_ID]=13.0

'"PSS_ID"=13.0'

"'PSS_ID'=13.0"

'PSS_ID'=13.0

 

I tried also the triple quotation marks inside da.SearchCursor like suggested here Specifying a query in Python—ArcPy Get Started | ArcGIS for Desktop (although I don't see why triple quotation marks should be easier to read and to understand ):

arcpy.da.SearchCursor(in_table, [field_name1, field_name2, field_name3],""""PSS_ID"=13.0""")

 

It would be great if someone could assist me on this - I've already spent so much time on looking for the right answer 

Outcomes