topic Re: sql statement in python with gdb in Python Questions

sql statement in python with gdb

Nicole_Ueberschär — Fri, 10 Dec 2021 20:24:46 GMT

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

Re: sql statement in python with gdb

JoshuaBixby — Fri, 30 Sep 2016 12:51:42 GMT

If PSS_ID is a number, try:

expression = "{} = {}".format(join1, row[0])‍

If PSS_ID is a string, try:

expression = "{} = '{}'".format(join1, row[0])‍

Re: sql statement in python with gdb

Nicole_Ueberschär — Fri, 30 Sep 2016 13:30:43 GMT

Joshua, you saved my weekend!

Would you mind giving a short explanation what this is doing? What are the brackets for?

Re: sql statement in python with gdb

WesMiller — Fri, 30 Sep 2016 13:31:34 GMT

You may also be interested in AddFieldDelimiters—Help | ArcGIS for Desktop

Re: sql statement in python with gdb

JoshuaBixby — Fri, 30 Sep 2016 13:43:40 GMT

The brackets are part of Python's Format String Syntax, which I encourage you to read about. In summary, the brackets are place holders that get substituted by what is passed into the format method. In this particular case, the brackets are simple place holders, but there is a lot of formatting that can be done within the brackets.

The equivalent of what I suggested, but not using string formatting, is:

expression = join1 + " = " + str(row[0])‍‍

and

expression = join1 + " = " + "'" + row[0] + "'"‍

Re: sql statement in python with gdb

Nicole_Ueberschär — Fri, 10 Dec 2021 20:24:49 GMT

Thank you very much for the link! I will definitely have a look at it.

I thought I had tried also the first expression but maybe not.

And for the second: Is there a difference between

join1 + " = " + "'" + row[0] + "'" and 
join1 + " = '" + row[0] + "'"

Re: sql statement in python with gdb

JoshuaBixby — Mon, 03 Oct 2016 18:28:32 GMT

No difference. I can't recall why I would have structured it one way instead of the other, must not have been paying attention. Regardless, both generate the same expression.