topic Re: arcpy SQL expression for PostGIS in Python Questions

arcpy SQL expression for PostGIS

StefanKaps1 — Wed, 29 Apr 2015 10:00:24 GMT

I am developing a toolbox where the user can select an attribute (dropdown list) which comes from a PostGIS database and based on this attribute a select statement (selectbyAttribute) will be executed... Sounds easy...

Here is a code snippet:

input_AZ = str(arcpy.GetParameterAsText(0))

sql_exp = 'aktenzahl = ' + input_AZ ("aktenzahl" is the column (character) of the PostGIS db)

arcpy.SelectLayerByAttribute_management (lyr_postgis, "NEW_SELECTION", sql_exp)

sql_exp looks like this:

"aktenzahl = VIIa-20150429a"

but I get the following error:

ERROR 000358

[The SQL statement was not a select statement]

Does somebody know, how a SQL statement in arcpy has to look like (e.g. inverted comma) when executing on PostGIS?

Thank you in advance!

Stefan

Re: arcpy SQL expression for PostGIS

JamesCrandall — Wed, 29 Apr 2015 12:15:17 GMT

Since you are querying a string value it will likely need to have single quotes (although I'm not entirely sure about PostGIS db requirements). You could try to update your sql with the following:

sql_exp =  """aktenzahl = '"""  + input_AZ + "'"

Which should produce:

aktenzahl = 'VIIa-20150429a'

Re: arcpy SQL expression for PostGIS

JoshuaBixby — Sun, 12 Dec 2021 03:33:39 GMT

The issue you are running into is less about database or Python requirements and more about Esri requirements. For databases, single quotes are the predominant string delimiter although some database platforms do support using double quotes that way as well. I have read in various places over time that the ANSI SQL standard itself defines double quotes for database object names delimiters and single quotes for string literals. That said, I have never paid to get a copy of the ANSI SQL standard to verify that statement.

Looking at the Python documentation for string literals:

2.4.1. String literals
....
In plain English: String literals can be enclosed in matching single quotes (') or double quotes ("). They can also be enclosed in matching groups of three single or double quotes (these are generally referred to as triple-quoted strings). The backslash (\) character is used to escape characters that otherwise have a special meaning, such as newline, backslash itself, or the quote character.
....
In triple-quoted strings, unescaped newlines and quotes are allowed (and are retained), except that three unescaped quotes in a row terminate the string.

Outside of raw strings, the backslash is the primary way of escaping special characters like a single quote. Triple quotes can be used, and are even preferred in some specific situations, but backslashes are most commonly used. One interesting behavior with Python is that using one type of quotes to define a string literal means the other type of quotes are interpreted literally within the string:

>>> #using backslash to escape single quotes
>>> print 'Let\'s hear it for \'air quotes\'.'
Let's hear it for 'air quotes'.
>>> #using triple quotes to escape single quotes
>>> print '''Let's hear it for 'air quotes'.'''
Let's hear it for 'air quotes'.
>>> #using double quote string literal to allow single quotes
>>> print "Let's hear it for 'air quotes'."
Let's hear it for 'air quotes'.
>>> #using single quote string literal with backslash to allow
>>> #double quotes
>>> print 'Let\'s hear it for "air quotes".'
Let's hear it for "air quotes".

(The Jive syntax highlighting is creating an artifact on line 05)

Looking at the Esri documentation for query expressions in ArcGIS:

Building a query expression
....
Searching strings
Strings must always be enclosed within single quotes. For example:
STATE_NAME = 'California'
Strings in expressions are case sensitive except when you're querying personal geodatabase feature classes and tables.
....
If the string contains a single quote you will first need to use another single quote as an escape character. For example:
NAME = 'Alfie''s Trough'

When it comes to building string expressions in Python, for SQL or otherwise, I am a big advocate for using the Python string format method (str.format()). The Python Format Specification Mini-Language is very robust at building expressions, and I find it much more readable than string concatenation in most cases. As long as the strings you are searching on don't have single or double quotes in them, the following code should work to generate a valid SQL expression for you:

input_AZ = str(arcpy.GetParameterAsText(0))
sql_exp =  "aktenzahl = '{}'".format(input_AZ)
arcpy.SelectLayerByAttribute_management (lyr_postgis, "NEW_SELECTION", sql_exp)

In the code above, I am using double quotes for the Python string literal so that I can use single quotes un-escaped to structure the query the way ArcGIS wants.

Re: arcpy SQL expression for PostGIS

StefanKaps1 — Thu, 30 Apr 2015 06:28:51 GMT

Joshua, I am impressed...That was exactly the problem and your explaination was outstanding!

Thank you, you saved my day...

Stefan

Re: arcpy SQL expression for PostGIS

JoshuaBixby — Thu, 30 Apr 2015 20:26:46 GMT

I am glad you found the explanation helpful. Building query expressions in ArcGIS using Python comes up from time to time in the forums, although the specific question usually varies. I have been meaning to write a blog post about it, and your question gave me the chance to do a dry run. Cheers.