SQL in python

[forestknutsen1]

The situation:

I have a table that is in a oracle database that I need to query and then spatialize. I need to select the most recant updated record for each group. I have a last update column to base the max date on. Then I have a name and a sand type columns that should make up the groups. So after some looking I think this is the sql that I need:

SELECT NAME, SAND, MAX(LAST_UPDATE) AS "DATE" from MY_TABLE

GROUP BY NAME, SAND

I have run it in Toad and it produced the desired result.

After the query I will need to do a join to a table that has the lat and long data and then make a xy events layer and then export the thing to a feature class in our SDE.

The question:

What it the best way to do this query in python in such a way that I can pass the output to arcpy for the join etc.

Thanks for the help,

Forest

[JoshuaBixby]

One can use SDE connection files, and ArcSDESQLExecute, against databases that are not registered SDE databases, at least I do it with SQL Server regularly and assume it would work with Oracle as well.

[JamesCrandall]

Nice!  Do you have an example?  It'd be great to not have to rely on .sde files for making connections.

[JoshuaBixby]

Typically, I use an SDE connection file to a regular/nonSDE database for simplicity's sake, but the connection can be created on the fly.  Here is a generalized example for SQL Server Express:

sde_conn = arcpy.ArcSDESQLExecute(instance="sde:sqlserver:host\MSSQLExpress", database="DB")

The above will use Operating System Authentication since I am connecting to SQL Server and not specifying a username/password.

The syntax for creating on-the-fly connections using ArcSDESQLExecute is the same as the direct connect syntax prior to ArcGIS 10.1 when they started changing connections to databases.

[BlakeTerhune]

I didn't think about doing this either. Interesting idea, I'll have to give it a try.

[forestknutsen1]

No I have not read that. Thanks you for the input. I have already made up the python for this current project based on James and Blake's input. But next time....

[JamesCrandall]

I just noticed this ArcGIS Help (10.2, 10.2.1, and 10.2.2)

Looks like you can now just join the NumPy array to your gdb Feature Class.

[JoshuaBixby]

Interesting, I guess I never noticed.  Now the lack of datetime support with ArcGIS and NumPy hurts even more.