Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 2 down vote favorite

I am trying to convert a psql query string into arcpy formatting for an UpdateCursor where expression parameter: 

'''SELECT \"NAME_0\",\"NAME_1\",\"NAME_2\",\"NAME_3\",\"NAME_4\",\"NAME_5\",\"WIKI_URL\", \"LEVEL_DEPT\" FROM 
AdminBoundaries WHERE \"WIKI_URL\" IN(SELECT \"WIKI_URL\" FROM AdminBoundaries GROUP BY \"WIKI_URL\" HAVING 
(COUNT (\"WIKI_URL\") > 1)) ORDER BY \"WIKI_URL\";
'''
share|improve this question
1  
Select statements in ArcGIS are very basic. ArcGIS is automatically set up to "Select * from [Table]". You only have the ability to add the WHERE clause. You also will not have the ability to use your GROUP BY, HAVING, and ORDER BY statements, – RyanDalton Dec 4 '12 at 19:47
okay, thanks I pretty much figured that was the case, but I wanted to make sure because I think the arcpy documentation should have at least made that kind of disclaimer. – Carl Carlson Dec 4 '12 at 19:55

1 Answer

up vote 1 down vote accepted

The UpdateCursor function allows you to filter fields that are returned/updated and allows simple sorting of A-Z/Z-A. I'm assuming you don't want the column/field filters for purely display reasons, but because you only want to update those specific fields?

A rough example:

rows = arcpy.UpdateCursor (r"C:/data/dataset.shp", WIKI_URL > 1,"", [NAME_0,NAME_1,NAME_2,...], WIKI_URL A)

Mind you, that example doesn't have exact syntax, or the full expression you gave above. It will change based on your database backend and needs. If your data is coming from ArcSDE or personal geodatabases, it should support full SQL queries including subqueries. You will have to experiment whether it allows more complex queries in this particular function.

You can use Copy Features to convert data to file, personal, or SDE geodatabases in order to take advantage of enhanced SQL functionality.

You can also use AddFieldDelimiters to take the guesswork out of whether your query should use quotes ("") or brackets ([]) or nothing around fields.

All of the above tools can be accessed through Python scripting.

share|improve this answer
oh, well the data is coming from within arcmap itself. I sort of figured that the sql would be limited and based on the limits of the select by attribute option. – Carl Carlson Dec 4 '12 at 22:17
yeah I sort of figured the sql would be limited based on the select by attributes option because my data is not from any db, but rather through adding a layer in arcmap. So it doesn't support full queries which I needed. – Carl Carlson Dec 4 '12 at 22:20
When you say the data comes from ArcMap itself through a layer file, do you mean an intermediate/temporary dataset, or something else? What format is the data in? Python can do a lot, but it is dependent on data format. – Baltok Dec 4 '12 at 22:25
1  
In that case, you can convert your shapefile to a personal geodatabase and may be able to use the enhanced SQL abilities to perform your query that way. Then convert the personal geodatabase back to a shapefile after you have updated rows. Just a thought. Anyway, sorry I couldn't help more. – Baltok Dec 4 '12 at 22:53
1  
You can use the Copy Features tool to convert your shapefile to a file, personal, or SDE geodatabase and vice versa. The tool can be accessed manually, through Model Builder, or Python. – Baltok Dec 4 '12 at 23:18
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.