topic Re: SQL Query in Python Questions

SQL Query

chriss_ — Sun, 12 Dec 2021 16:12:57 GMT

Hi!

I ve problems with a sql expression. I want to select one row of a 1st lyr file attribute table in order to only select those features from a2nd lyr file intersecting with the 1lyr selected feature.
I tried all kind of expressions: "" ,"/", etc. but it keeps telling me either the expression is wrong or it doesn't make any selection. Any ideas?
Thi is the part of code keeping troubleing me

rows = arcpy.SearchCursor(MU)
for row in rows:
    x=row.OBJECTID
    whereClause="[OBJECTID] = "+str(x)
    arcpy.SelectLayerByAttribute_management (lyrFile, "NEW_SELECTION", whereClause)
    arcpy.SelectLayerByLocation_management(lyrFile2, "intersect", selectFeatures)

Re: SQL Query

BruceNielsen — Thu, 02 Jun 2011 11:43:32 GMT

Here's a couple of thoughts:
1. Using [OBJECTID] indicates that your data is stored in a personal geodatabase (.mdb). If it isn't, you should be using "OBJECTID" instead.

2. Assuming #1 is correct, it might be easier to use string substitution to build your query:
[INDENT]whereClause = "[OBJECTID] = %d" % row.OBJECTID[/INDENT]
and omit the line 'x=row.OBJECTID'.

3. If you're not querying a personal GDB, the query statement would be:
[INDENT]whereClause = '"OBJECTID" = %d' % row.OBJECTID[/INDENT]

Hope this helps.

Re: SQL Query

KimOllivier — Fri, 03 Jun 2011 01:22:08 GMT

To debug a tool, try this:

Run a tool interactively by filling in the dialog by hand.
After it runs successfully, open the results and right-click to get "Copy as a Python Snippet"
Paste this into an empty Python script and examine the syntax, particularly the SQL query.

I find that provided you are not using a personal geodatabase (*.mdb) AND you are using valid field names (No spaces or silly chars, starts with a letter, less than max chars, not a reserved word) you can also dispense with the double quotes around the field names. In spite of the help saying otherwise. This makes assembling SQL queries much simpler. Also try printing out your expression to see if you really got the expected valid expression.

Re: SQL Query

chriss_ — Fri, 03 Jun 2011 03:32:12 GMT

Hi!

Thanks to both of you.
Finally I discovered that it was not my SQL Query that didnt work. The problem lies within the "arcpy.SelectLayerByLocation_management" i couldnt get it to work so i changed to "Intersect_analysis" what is better anyhow.

@kimo
I really appreciate debugging advice. Normally for coding, I use the standard python window that comes with python. But compared to typing directly in the arcgis python window i get less "advice". By "advice" i mean arcgis python automatically pops up a list with functions or wahtever i can enter at this point. e.g. i type "arcpy." and then it pops up all functions arcpy has. in the standard python window it doesnt. Where do you write your code? Any tipps are highly appreciated.

Thanks

Re: SQL Query

DarrenWiens2 — Sat, 11 Dec 2021 11:13:51 GMT

I might be mistaken, but I believe SQL requires strings to be enclosed in escaped single quotes (\'). So, your query would be:

whereClause="[OBJECTID] = \'" + str(x) + "\'"

Re: SQL Query

KimOllivier — Mon, 06 Jun 2011 06:50:49 GMT

Intellisense works just fine in PythonWin while coding if you first import arcpy in the Interactive Window. No need to go to some other IDE.

import arcpy will check out a licence but it will make Pythonwin aware of the module.
If you only have an arcview licence, then import arcview

You can also then browse the arcpy module from Menu>Tools>Browser> arcpy
instead of the default __builtins__

However I still find the commentary in the ArcGIS help more useful.