Latest Contributions by JoshuaBixby

Garret Duffy, thanks for uploading sample data, it helps to troubleshoot and explain what is fouled up. As I suspected, the problem is with the elevation values being returned by the subquery and how the IN clause is structured.  The elevation values are fine, in and of themselves, and the subquery and query are returning correct results based on the SQL as written.  Let's take a closer look at the example in your attached screenshot: The Sample_Dataset table is in the background with the results of your subquery in the foreground.  Although the subquery is calculating the maximum elevation value by the FID_T_25_projected column, it is only returning a result set of elevation values.  With the query as written, two rows with FID_T_25_projected equal to 663 are being returned instead of one because there are two 663 records that have an elevation matching one of the elevation values in the subquery. Even if you were to include FID_T_25_projected in the SELECT list of the subquery, the query as written still wouldn't return the results you want.  If you were working with one of the database platforms that support multiple column IN clauses, like Oracle, a fairly small change to your SQL would get things workings.  Unfortunately, neither file nor personal geodatabases support such functionality. There are numerous ways to solve this riddle using SQL natively within a DBMS, but you are working within the confines of ArcGIS and the limitations that imposes on structuring SQL.  Fortunately, you are working with personal geodatabases so there is a fairly straightforward solution: objectid IN (     SELECT objectid       FROM sample_dataset t1          ,(SELECT fid_t_25_projected, MAX(elevation) as maxe              FROM sample_dataset          GROUP BY fid_t_25_projected) t2      WHERE t1.fid_t_25_projected = t2.fid_t_25_projected        AND t1.elevation = t2.maxe) The above SQL works with personal geodatabases and desktop geodatabases/personal SDE, but it doesn't work with file geodatabases or shapefiles.  I suspect the above SQL would work with all enterprise geodatabases, but I haven't verified it. ... View more

Cursor.description will provide you the information you need, but you will have to create a mapping between cx_Oracle types and NumPy types.  It might also be worth checking out oracle_util.py on GitHub. ... View more

Performance wise, I wouldn't be surprised if Append and Copy Rows were comparable, but there is the functional difference where Append requires an existing table/dataset/etc... while Copy Rows has to create a new one.  Since the OP's original workflow includes truncating a table instead of deleting it, there may be a requirement that prevents the use of Copy Rows. In terms of the performance of ArcPy Data Access cursors versus ArcPy original/older cursors versus ArcObjects in .NET or C++, there is a post over at GIS StackExchange that discusses the topic:  How is the data access cursor performance so enhanced compared to previous versions?  Jason Scheirer​, one of the arcpy.da developers, explains how they improved the performance of the new cursors, but he also states that ArcObjects in .NET or C++ is still over twice as fast as arcpy.da cursors.  Since the Append tool is native ArcObjects, the OP's runtimes seem reasonable, or at least not surprising, when talking about bulk transferring of data. ... View more

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. ... View more