arcpy SQL expression for PostGIS

StefanKaps1

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

bixb0012

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.