Formatting SQL queries for use in Arcpy?

Question

I have to make an SQL query on a column having not adequat name "type_borne_id", I couldn't make SQL queries using arcpy.Select_analysis neither selectbyattribute, but when I use select_analysis toolbox in arcmap it works with the same column.

I wonder if I should add some characters, or what to do, to query this column normally? I remarked that this pattern of names "name_column_id" is vulnerable to this error.

Example of the queries that didn't work:

arcpy.Select_analysis("table" , "table_copy" , " type_borne_id = 23" ) arcpy.Select_analysis("table" , "table_copy" , ' "type_borne_id" = 23' )

I have tested with other fields like "id" and it works normally.

I'm using Arcgis 10.1 & ArcSDE 10.1 on PostgreSQL.

• Michalis Avraam: Please include your query so we can have a look. In general, remember that you need to put field names around double quotation marks, which means the query needs to be enclosed in single quotation marks (for example '"FIELD" > 10')
• Roy: I find it helpful to build the query step by step; this is especially useful when you're dealing with values stored as variables. It looks confusing at first, but you can always use a print statement to see how it turns out: whereClause = '"' + fieldName + '" = ' + "'" + fieldValue + "'"
• geogeek: Thanks all but i don't have problem in quotes, i have tested with other fields like "id" it works like a charm
• blah238: This is a frequently asked question. Example: How do I include a variable in the where clause of arcpy.Select_analysis?

Answer 1

All selections through arcpy needs to be quoted similarly to how you see them in ArcMap itself.

In ArcMap you would see something like:

"type_borne_id" = 23

You can recreate that in Python as well, in many different ways.

'"type_borne_id" = 23' # Note the single quote outside the query
'''"type_borne_id" = 23''' # Triple single quote (docstring)

The above two examples just show two ways. You can use escape characters and other ways as well. For more information, look into the Select help page on ESRI.

Answer 2

I realize this is an old post, but for anyone else that comes across it. If you don't know how your input file is (shapefile, geodatabase, or file geodatabase) there is a function that will append the field delimiters ([ or ") depending on the input featureclass.

See http://resources.arcgis.com/en/help/main/10.1/index.html#/AddFieldDelimiters/018v0000006p000000/

For further details.